HITEK Computer Services - Queries

The brief given to you by Pat Miller included being able to find out the following information:

1. print out up-to-date lists of customer names & addresses with company names in alphabetical order
2. make it easy to find computers which do not have a service contract and contact their owners 
3. find out which customers only have return to base or next business day (NBD) warranties and persuade them to upgrade
4. find out which engineers built which computers
5. contact customers who do not have addresses in Warrington AND who do have a warranty to offer them telephone support while waiting for an engineer to arrive
6. contact customers with a service contract who have not got much memory installed in their computers to offer them a cut-price upgrade
7. contact customers with a service contract whose PC is between 6 and 12 months old to offer them an inspection

Query 1  Lists of customer names & addresses in alphabetical order

You can make this query MUCH MORE USEFUL by including next to each customer how many computers each one owns

STEP 1 - Create Query and add tables

• Create a new query in design view
• add the customers table (so we can see their information)
• add the computers table (so we can count how many they have)
• Click on CLOSE

STEP 2-  SELECT FIELDS TO BE INCLUDED IN THE QUERY

• double-click on the fields listed in the computer and customers tables to insert them into the query design

STEP 3 - SELECT CRITERIA FOR QUERY

• you need to sort the customer names in ascending order
• you need to count the number of computers that each customer owns - click on VIEW, TOTALS and choose COUNT for SerialNo

Question:   Why was serial number chosen as the field to count?  Ask your teacher if you are unsure.

STEP 4 - SAVE AND RUN QUERY

• Save the query as Customer Info with  Computer Count
• Run the query by clicking on Query, Run or click the icon
• Your query results should be similar to those below - make sure that the CustomerName is in alphabetical order

Query 2  Find computers which do not have a service contract

• you need to ADD all 3 tables to the query design
• you should sort the customer names into order
• you should search for none
• save the query as "No Service Contract"
• run the query
• your query design should be similar to this

QUESTION:  

This query only uses information from the customers and warranty table but you must add all 3 queries to the query design.  Below is a picture of the relationships without the computer table.  

Why will your query not work with just the 2 tables as part of the design? Ask your teacher if you are not sure.

Query 3 - Customers that have return to base or next business day (NBD) warranties 

• add the appropriate tables to the query design
• save the query as "Target for Warranty Upgrade"
• Run the query to view the results

Query 4 - find out which engineers installed which computers

• add the appropriate tables to the query design
• save the query as "Who installed PC"
• Run the query to view the results

QUESTION - Can you think of better fields to sort?

Query 5 - Customers who do not have addresses in Warrington and do have a Warranty

• add the appropriate tables to the query design
• save the query as "Not in Warrington with contract"
• Run the query to test your results

Query 6 - customers with a service contract who have not got much memory installed in their computers

• add the appropriate tables to the query design
• save the query as "Memory Upgrade Targets"
• Not much memory means less than 128 Mb (hint - less than is <)

Query 7 customers with a service contract whose PC is between 6 and 12 months old

• add the appropriate tables to the query design
• save the query as "6 to 12 months old"

The example below was created on 3 December 2001 - yours will be 6 to 12 months from today's date