CSC 545, Instructor: Zhen Jiang Project 1 ACCESS Queries.
Use the tables
ClientClient Number / Client Name / Street / City / State / Postal Code / Amount Paid / Current Due / Recruiter Number
AC34 / Alys Clinic / 134 Central / Berridge / CO / 80330 / $0.00 / $17,500.00 / 21
BH72 / Berls Hospital / 415 Main / Berls / CO / 80349 / $29,200.00 / $0.00 / 24
BL12 / Benton Labs / 12 Mountain / Denton / CO / 80412 / $16,500.00 / $38,225.00 / 24
EA45 / ENT Assoc. / 867 Ridge / Fort Stewart / CO / 80336 / $12,750.00 / $15,000.00 / 27
FD89 / Ferb Dentistry / 34 Crestview / Berridge / CO / 80330 / $21,000.00 / $12,500.00 / 21
FH22 / Family Health / 123 Second / Tarleton / CO / 80409 / $0.00 / $0.00 / 24
MH56 / Maun Hospital / 76 Dixon / Mason / CO / 80356 / $0.00 / $43,025.00 / 24
PR11 / Peel Radiology / 151 Valleyview / Fort Stewart / CO / 80336 / $31,750.00 / $0.00 / 21
TC37 / Tarleton Clinic / 451 Hull / Tarleton / CO / 80409 / $18,750.00 / $31,500.00 / 27
WL56 / West Labs / 785 Main / Berls / CO / 80349 / $14,000.00 / $0.00 / 24
Recruiter
Recruiter Number / Last Name / First Name / Street / City / State / Postal Code / Rate / Commission
21 / Kerry / Alyssa / 261 Pointer / Tourin / CO / 80416 / 0.10 / $17,600.00
24 / Reeves / Camden / 3135 Brill / Denton / CO / 80412 / 0.10 / $19,900.00
27 / Fernandez / Jaime / 265 Maxwell / Charleston / CO / 80380 / 0.09 / $9,450.00
34 / Lee / Jan / 1827 Oak / Denton / CO / 80413 / 0.08 / $0.00
and build the following queries with each specified query name.
Evaluation: Please submit your accdb file via D2L. Late work is not acceptable.
Simple queries
1) For all clients, list client number, client name, amount paid and current due (save as Q1).
2) List client number, state, zip code, current due for all clients of recruiter “24” (save as Q2).
3) List client number, client name, street, for all clients that live in zip code “80336” (save as Q3).
4) List all the fields for any client who has a current due of more than $20,000 (save as Q4).
Queries involving * and ? (see page AC87)
5) List all clients (name, number and zip) whose zip code starts with “804” (save as Q5).
6) List all client numbers that start with “F” and ends with 9 (save as Q6).
7) List all clients (client number only) located in a city whose name starts with “M” and has 5 letters (save as Q7).
8) List all clients (client number and name) whose name contains the word “Hospital” (save as Q8).
9) List all clients (client number, name, street) that are located on Main street (save as Q9).
Queries involving compound criteria AND & OR (see pages AC95-96)
10) List client numbers, client names and current due, for all clients of recruiter “24” who have more than $5,000 current due (save as Q10).
11) List client numbers, client names, amount paid, and current due, of all clients who have paid more than $5,000 (amount paid) and have less than $20,000 current due (save as Q11).
12) List all clients (client number, client name, and amount paid) whose amount paid is between $10K and $20K (save as Q12).
13) List all clients (client number, client name) whose amount paid is NOT between $10K and $20K and live in “Berls” (save as Q13).
Sorting Data in a query, (see pages AC97-102)
Note-Information in every table isNOT in any particular order. However, when we create a query or report we can request that the information shows up in a particular (sorted) order. This does not change the actual way the data is stored in the tables.
14) List all client numbers, names, recruiter numbers, and amount paid, in an ascending order by recruiter number (save as Q14).
15) List client number, name, and amount of current due of all the clients of recruiter “24” with a current due > 0, sorted by the amount of (current) due (save as Q15).
Top value queries (AC102-103)
16) List the top 5 clients with the highest current due, in the descending order by current due. The display includes current due, client number, and client name (save as Q16).
17) For recruiter “24”, list the top 3 clients (client number, client name, amount paid) who have the highest amount paid, in the descending order by amount paid (save as Q17).
Parameter Queries (AC90)
18) List all clients (client number and name) who live in a city entered by the user (Save as Q18).
19) List the client numbers, client name, and current due for all clients that have a current due more than a value entered by the user (save as Q19).
Calculated values (AC113-115)
20) Show all names of clients, along with a new column labeled “Total Amount” which is the sum of amount paid and current due (save as Q20)
Calculating statistics (AC 118-119)
21) Show the max, min and average amount paid for all clients (save as Q21).
Multiple Table (AC 105-109)
22)List name and number of each client located in “Fort Stewart,” along with the recruiter number and the city where its Recruiter lives in. Sort the display by client number in an ascending order. Save as Q22.
23)For each recruiter who has a commission greater than 10,000, list Recruiter number, Recruiter’s Last and first names, and each of their clients (by client number, client name). Sort the display by Recruiter number (in descending order). Save as Q23.
Aggregations (refer to lab 2)
24) For all recruiters living in “Denton”, show a count of their clients, along with their (clients’) average “amount paid”, and maximum “current due” (save as Q24).
25) For eachrecruiter, show a count of their clients, along with average “amount paid”, and maximum “current due” of his/her clients (save as Q25).