Access Test 1: A Database for Books
Scenario
You need to keep track of your Book collection. You want to keep track of both your books and where they are.
The Table.
Please create the following table:
Books
Field Type Size Notes
ID Autonumber –
Title Text 80
Author Text 50
Topic Text 50
Year Number Integer Decimal Places: 0
Location Text 50
Notes Text 100 Larger than the default value.
The Primary Key for the Books table is ID Number.
Input Form
Create suitable form for the Books table.
The Data
Using the form you’ve just created, please enter the 6 following books as detailed below. Also, now enter a further 10 Books of your own choice, making a total of 16 Books.
The Books
ID Title Author Topic Year Location Notes
1 Excession Ian M. Banks Novel 1996 Lounge
2 The Oxford Dictionary of Opera J Warrick and E West Reference 1994 Study
3 The Politically Incorrect Guide to Darwinism and Intelligent Design Jonathan Wells Science 2006 Study
4 Sahara Michael Palin Travel 2002 Lounge
5 The Origins of Virtue Matt Ridley Science 1996 Study
6 Charles Darwin’s Letters Ed. F Burkhardt Biography 1996 Study
The Queries
Please create the following queries.
1. Query: Books by Author
List the ID Number, Title, Author, Topic, Year and Location of all the books. The list should be ordered by Author.
2. Query: Books by Year
Produce a list of all the books using the fields (in this order): Year, Topic, Title, Author and Location. The list should be ordered by Year, within each year order the books by Topic.
3. Query: Books in Study
List the ID Number, Title, Author, Topic, Year and Location of all the books located in the Study. The list should be ordered by Author.
4. Query: Books on Darwin
List the ID Number, Title, Author, Topic, Year and Location of all the books which have Darwin in their titles. The list should be ordered by Title (rather “academic” in this set of data as the list should contain only one book!).
Reports
Please create the following reports.
1. Books Report
You need a simple report listing all the details of the books. This should be in Title order and in “Columnar” layout.
2. Location Report
You need a simple report listing the details of all Books in each location. Within each of the locations the books should be in title order and in a “Stepped” layout.
Final Step
Save the database and submit it according to the submission instructions above.
Access Test 2: A Database for CDs
Scenario
You need to keep track of your CD collection. You seem to be lending a lot of CDs to friends. Therefore, you decide to write a database to help you keep track of both your CDs and who you lend them to.
The Tables.
Please create the following tables:
CDs
Field Type Size Notes
CDNumber Autonumber –
Title Text 80
Artist Text 50
Label Text 50
Borrower Text 3 Set an appropriate Input Mask to enforce this, e.g. >LL?
DateBorrowed Date/Time – Set an appropriate Date Format Input Mask
The Primary Key for the CDs table is CD Number.
Friends
Field Type Size Notes
Initials Text 3 Set an appropriate Input Mask (e.g. >LL?)
FirstName Text 50
LastName Text 50
Address Text 50
TelNumber Text 50
The Primary Key for the Friends table is Initials
The Data
Please enter the follow rows in the tables.
CD Rows.
CDNumber Title Artist Label Borrower DateBorrowed
1 To The Faithful Departed The Cranberries Island Records MJ 28/06/2016
2 The Very Best of Kim Wilde Kim Wilde Parlaphone DBK 31/12/2016
3 No Need To Argue The Cranberries Island Records
4 In The Mirror Yanni Private Music YZ 02/09/2016
5 Olympic National Park Mars Lasar Real Music
6 A Farewell to Kings Rush Mercury DBK 15/07/2016
7 Incantation Tim Weather Real Music MJ 02/03/2017
Friends Rows
Initial FirstName LastName Address Tel Number
MJ Mark Jung Pontypridd 01443 321852
PJ Peter Jam Cardiff 029 205235344
PL Paul Lingo Treforest 01443 1234812
DBK David Kidner Treforest 01443 482647
YZ Yeats Zhang Treforest 01443 2347239
Additional Data
Please add an additional 12 CDs of your own choice and an additional 5 Friends of your own choice. Assume that at least 8 of these additional CDs have been loaned to any of your existing 5 Friends or 5 New Friends.
Join the tables
Create a one-to-many relationship between the two tables. You should enforce referential integrity, this will stop the deletion of a friend’s details when they have a CD on loan.
The Queries
Please create the following queries.
1. Query: CDs by Artist
List the CD Number, Title, Artist, Label, Borrower’s initials and Data borrowed of all the CDs. The list should be ordered by Artist.
2. Query: Friends by First Name
List the Initials, Names, Address and Telephone numbers of all the friends. The list should be ordered by their First Names.
3. Query: CDs on Loan
List the CD Number, Title, Artist, Name of the friend and when they borrowed it. Only CDs which you have lent should be listed (3 CDs in all) and they should be listed in Date borrowed order, the one which has been on loan longest should be first in the list.
4. Query: CDs not on Loan
List the CD Number, Title, Artist and Label of those CDs which you have not loaned out. Order the list by Artist. (Hint. Use the special value “null” to filter for the CDs not on loan.)
5. Query: Number of CDs on loan
For those friends who have your CDs, list their Names and Telephone number and the number of CDs they have on loan. The list should be in First Name order.
6. Query: CDs loaned to friends in Treforest
List the CDs which have been loaned to friends who live in Treforest. The list should contain the CD Number, Title, Artist, Names of the borrower, their Telephone number and the Date they borrowed the CD.
7. Query: CDs on long term Loan
For CDs which were loaned over six months ago, list their Number, Title, Artist and who has them on loan together with the Date it was borrowed.
Input Forms
Create suitable forms for the CDs and Friends tables. The CDs form should include a drop-down selection box for the Friend’s Initials.
Reports
Please create the following reports.
1. CD Report
You need a simple report listing all the details of the CDs. This should be in Artist order.
2. Friends Report
You need a simple report listing the details of all your friends. The report should be in First Name order.
3. CDs on Loan Report
Produce a report of the CDs that are out on loan. The report should include the CD Number, Title, Artist, Label, Names of the person who has the CD and when they borrowed it. The report should be ordered by the Date of the loan, the oldest at the top of the list.
Final Step
Save the database and submit it according to the submission instructions above.
PLACE THIS ORDER OR A SIMILAR ORDER TODAY AND GET AN AMAZING DISCOUNT 🙂
