Computer sciences and Information technology:
Instructions:
For this project, it is best to do things in the following sequence:
1) Input or import your data using each table.
2) Create a form for each table.
3) Create any necessary relationships between the tables.
4) After everything is well designed and well created you can run the queries and reports.
PROBLEM:
Photographers of America do wedding and party photography in both film and digital.
To improve customer service for their most important corporate clients, they need to create an Access database to keep track of their photographers and their clients.
The following tables present a partial list of corporate clients and their photographers.
Client Table:
Client Number First Name Last Name Address City State Zip Code Amount Due
AB53 Penny Lee 276 Oval Radish NY 58120 $155.00
AS62 Nickel Sweet 313 Round Radish NY 58216 $425.00
BB88 Dime Love 111 Square Apple NJ 07052 $100.00
BL26 Quarter Flower 476 Triangle Radish NY 58120 $140.00
DE76 Dollar George 193 Hexagon Plumb VT 52018 $300.00
GR56 Sam Adams 981 Octagon Corn NH 59130 $165.00
GU21 John Kennedy 111 Polygon Radish NY 58120 $964.00
JE77 Bill Clinton 157 Circle Plumb VT 52018 $110.00
MI26 George Bush 165 Flat Radish NY 56152 $223.50
XX99 Richard Nixon 116 Bumpy Peas PA 12345 $10.00
Photographer Table:
Photographer Code Last Name First Name Address City State Zip Code Hourly Rate Hired
AA Levin Harold 33 Maiden Lane Farmers CT 12353 $25.00 9/19/1999
BB Smith Ian 65 Hanes Court Railing FL 97165 $50.00 2/22/2003
CC Jones Dylan 91 Lake Road Revila AZ 84789 $75.00 4/25/2004
Each photographer can work for many clients, but a client can have only one assigned photographer. Photography assignments are as follows:
• Levin is assigned to Penny Lee, Dime Love, Dollar George and John Kennedy
• Smith is assigned to Nickel Sweet, Quarter Flower, Sam Adams and Bill Clinton
• Jones is assigned to George Bush and Richard Nixon
Requirements:
1. Create two tables in Access (a client table and a photographer table), and give the database the name “PHOTO”. Be sure to designate appropriate primary keys, to set appropriate field types and sizes and to pay attention to other important field properties in each table. Design your tables so a relationship can be created between the two tables.
2. Use the Form Wizard to create a form for each table. You can load the data specified (above) into your tables either using the datasheet view of each table or using the forms you created.
3. Create the relationship between the two tables. Be sure to enforce referential integrity, cascade updates and cascade delete. (10 points)
4. For each of the following queries, run and save (with the corresponding letter A,B C,D or E)
(a) Display the first and last names (alphabetical order by last name), the client number and billed amounts for those clients who have amounts due over $150.00
(b) Display the first and last names (alphabetical order by last name), and hire dates of the photographers hired after the date January 1, 2000.
(c) Display the first and last name (alphabetical order by last name) of all the clients located in either NY or PA.
(d) Display the first and last names (alphabetical order by last name) of the clients who live in NY and owe over $200
(e) Display the total, average, minimum and maximum amounts due from all the clients.
(f) Create a parameter query that will ask the user to enter a state and will return the first and last name of the clients who live in that state.
5. Use the Report Wizard to create the following reports. Save each one with the report letter (A, B)
(a) Print a tabular report of the client table (show all the fields).
(b) Print a tabular report showing an alphabetical list of clients (sorted by last name) whose amount due is less than $200