Counting fields for Multiple Occurrences of the same information

[dbourdea]

I have a maintenance database that has several related tables (Serial Number, Model Number, Technician Name,etc)and a "data" table to store the maintenance data on equipment. Equipment is identified by its Serial via a lookup to the Serial number table. RMA number, sent and recieved information is stored in the "Data" table.

I am trying to write a query to return information from this "data" table that only includes equipment (based on serial number) that has been serviced more than 1 time. The "data" table has lookup's to the serial number table for serial number information.

How do I have access determine how many of each serial number there are then based on this information return only ones that have been serviced more than Once?

 

[RickSpr]

You'll need to use the Count(*) aggregate function in your query, with the GROUP BY and HAVING clauses. Depending on what you want, this will probably have to be a subquery of the main query, which retrieves the fields you want.

I'd like to have been more specific, but you didn't provide any specific information I could have used. If you need a better explanation, you'll have to describe:
1. What is represented in the rows of each table. For instance, does a row in the "Data" table represent a piece of equipment, or a single repair on a piece of equipment, or what?
2. The key fields for each table.
3. How the tables are related ("One row is A is related to one or more rows in B", etc.). Also, give the name of the column(s) used to relate them. You've already explained that the Data table has a Serial Number field that relates it to the Serial Number table, but there must be more relationships. How, for instance, are repairs related to pieces of equipment?
4. Which columns you need in the output of the query, and which table each comes from.

It would also help if you use your real table and column names. Rick Sprague