Create an Audit db with multiple linked tables 1

[murphy123]

I am creating an Audit db in Access 2000 to monitor several different "reports" that agents work, which in fact are(tables)linked into the Audit db. The purpose of the database is to track the type of work the agents have completed. A group of people will auditor the work done. What I am trying to do is get a total number of what has been audited among all the linked tables in the database.

Here are the common fields in all linked tables:

AuditorID (text)
AuditDate (date)
Correct (yes/no)
Incorrect (yes/no)
FYI (yes/no)
AuditorComments (text)

I need to get the output result showing how many records were audited by date from all linked tables including the tables that were not monitored.

Example:
AuditorID CountOfAuditorID
MARY123 25

It doesn't really matter what table (report name)that was audited, I need a total. But if there is a simple way to do it, please give me suggestions.

I am not sure if a Union query would give me the results I am looking for. I am new to SQL queries.
Any suggestions??

Thanks

 

[lotharious]

I think the union query will give you the desired result.

Select AuditorID, AuditDate from Table1
Union All Select AuditorID, AuditDate from Table2
.....

That will put all the AuditorID's and AuditDates into 1 Query. The easy, but not neccisarily best way is then to make another query that would be like,

Select AuditorID, AuditDate, Count(AuditorID) as CountofAuditorID from AboveQuery Group By AuditorID, AuditDate

Any conditions for the querys would have to be put in of course. You can add as many tables queried in that as you need.

 

[murphy123]

Hi lotharious, thanks for the information. So far I tested the db with 2 tables and the queries worked. I'll add more tables to the query and if I come across a problem I'll send the question out.

Thanks again.