Homework answers / question archive / Access work Instructions:  For this project, it is best to do things in the following sequence: View the tutorial Import your data using each table (I’ll provide the data for each group)

Access work Instructions:  For this project, it is best to do things in the following sequence: View the tutorial Import your data using each table (I’ll provide the data for each group)

MS Access

Share With

---

Access work

Instructions: 

For this project, it is best to do things in the following sequence:

1. View the tutorial
2. Import your data using each table (I’ll provide the data for each group).  Don’t type in the data! 
3. Create a form for each table.
4. Create any necessary relationships between the tables.
5. After everything is well designed and well created you can run the queries and reports.

 

4.    For each of the following queries, use Design View to create your queries. Run and save (with the corresponding letter A,B C,D,E or F)

(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 greater than or equal to  $150.00   (10 points)

(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 with the amount they owe and a new field called New Amount Owed which is a discount of 10%.

The New Amount Owed should not be just the discount, but the actual new amount owed with the discount included

(d)  Display the first and last names (alphabetical order by last name) of the clients who live in (NY AND from zip code ‘58120’) OR live in (NJ AND from zip code ‘07052’).       

(e)  Display the count, total, average, minimum and maximum amounts due from all the clients.  Data presented for dollar amounts should be in currency format with 2 decimal places.  Use the original amount owed from the table.

Create a parameter query that will ask the user to enter a city AND state and will return the first and last name of the clients who live in that city AND state

5.    Use the Report Wizard to create the following reports.  Save each one with the report letter (A, B).  You may need to modify your report layout if it is not professional or columns are not aligned properly. (ie. Design View).  

(a)  Print a tabular report of the client table (show all the fields). This should appear clearly with all headings

(b)  Print a stepped report showing an alphabetical list of clients (sorted by last name) whose amount due is less than $200.  The photographer code should appear only once for each store with the clients belonging to that photographer listed below.

 

 

 

 

https://youtu.be/pbb4BBV4Cew