Create the following database:
Table work
- Create a table called "Bands" It should have the fields
"Name", "Contact", "Tel", and "desired fee per hour".
The first one should be the primary key. The last one should have "Currency" as data type,
all others have "Text" as data type. Put in at least 5 records.
- Create a table called "Clubs" It should have the fields
"Name", "Contact", "Tel", and "maximum fee per hour".
The first one should be the primary key. The last one should have "Currency" as data type,
all others have "Text" as data type. Put in at least 5 records
with club names "A Club", "Bio Club", "Club Club",
"Dee's Club", and "Falcon's Nest",
- Download the Excel file "bookings.xls",
that can also be found on IQWeb. Import it into the Access file, using
"File|Get external data|Import". Put in an additional field called
"Bookings ID" with data dtype "Autonumber" which should be the primary key.
Fix the data type for "day" and "start time" and "end time".
Then put in band names and hourly fees for all the records.
- Create a table called "Band Members". It should have two fields,
"Name" and "Instrument". The first one is the primary key.
Put in 10 records.
- Finally put in a table called "Members-to-Bands". It should have three
fields, "ID" with data type "Autonumner" which is the primary key,
and the fileds "Member Name" and "Band Name". Put in records.
Let every member play in one band, and at least one in two bands.
Relationships
Open the relationshipw window and put in the four relationships between the five tables.
Try to enforce referential integrity, if this is impossible then do it without.
Query Work
- Create a (select-) query that lists the names of all bands having somone playing a keyboard
(or any other instrument appearing in your particular database).
- Create a (select-) query that lists the names of all band members that are playing on May 29.
- Create a (select-) query displaying for all your bookings the club and band names, together with
the total fee for playing. This task requires so-called "calculated fields"
- Create a so-called "parameter query" that prompts you for a club name.
Then it will display, for all bookings at that club, the hourly fee the club was actually paying
and the maximum hourly fee the club is in general willing to pay.
- Independent Work Task: Create a so-called "parameter query" that prompts you for a band name.
Then it will display for all bookings of that band where the band got less than what they desired,
the club name, the date, and how much less per hour they got.
- Create a (sigma-) query that shows for all
clubs the total amount of money they paid during May.
- ...
- Independent Work Task: Create a (sigma-) query that shows for all band members the
total amount they got during May (assuming that every band will split the money evenly).
- A guitar has been found in the Bio Club on June 5th.
Create a select query to find all band members playing guitar that played in the A Club between
May 25 and June 5.
- Create a so-called "update query", and use it to change all start times 8:00 into 8:15.
- Create an update query that changes, for all bookings where the actual hourly fee is below the
one required by the band, the actual hourly fee to the one required by the band.
Forms
- Create a form for the Bands
- Change the design of the form slightly
- Create a form for the Bands with Bandmembers as subform
- Create a form based on query 3, showing all bookings, band names, club names, and total fee.
- Independent Work Task: Create a form for the band members,
with band information as subform
Reports
- Create a report showing all bookings, band names, club names, date, and fee pre hour.
It should be grouped by club name.
Remember that you should help each other, but that you shouldn't submit
the same file for several students.
