Counting test records from multiple tables in one report

[m0aje]

Hello,

I have been trying to create a report to count equipment tested between two dates:

I am using Access 2000 (old I know) and I have 21 different tables with the fields laid out the same. For the moment, I will list five of the tables: Servers, Laptops, Printers, Workstations, and Monitors. The criteria I draw from each table are the fields Model#, Part#, Serial#, Test Date, Retest Date, and Technician.
I can create a report from a query (say laptops). The criteria I is
BETWEEN[Enter Start Date]AND[Enter End Date] under the TEST DATE field. Works great! In my report I use =Count(*)&" "&"Unit(s) tested" & "Between "&[Enter Start Date] & " and" & [Enter End Date].

That works great too. I found that out from a post on this site.

I am trying to create ONE REPORT using ONE Date range and display how many units were tested:
Example:

Units Tested between March 1, 2014 and March 31, 2014

Servers 9
Laptops 23
Workstations 15
Printers 18
Monitors 31

If I can get these five tables, hopefully I can add additional tables to the same report as I need them. I am not savy with VB code, but will give it a go with some direction as to where to put it and how to add to it.

Any help would be most appreciated.

m0aje

 

[plog]

I am using Access 2000 (old I know) and I have 21 different tables with the fields laid out the same

You don't need to acknowledge and apologize for the old version of Access, its the 21 different tables with the same structure you should seek penance for.

Why do you have 21 similarly structured tables? Suppose all this data was in 1 table, you wouldn't be posting this because it wouldn't be an issue for you would it? That's the task you need to focus on--get all this data into one table, because your way is an improper structure.

 

[m0aje]

Hello Plog,

Unfortunately, the 21 different tables is what I am stuck with. The way it is written makes it do certain required functions.
What I am doing is linking the tables so that I can make my own reports and queries. Thereby not screwing up anything or the original setup. Such is the case with what I am trying to do now.
The program was written along time ago. I assumed that it may have started with fewer than 21 tables and has progressed since. I may have misspoke "a little" about the structure of the tables. They are for the most part structured the same way. There are certain fields I extract from them.
Anyway, I have a means to copy all the tables to a separate hard drive. However, I am a novice when it comes to Access and, for the most part, an idiot with VB code. I don't understand what I read from it. Most of the code I have used has been from Microsoft's help files and I have been lucky.

Thanks for your reply!

m0aje

 

[plog]

In that case I would create a query to merge the data in all of them into one datasource. For that you would use a UNION QUERY: http://www.techonthenet.com/sql/union.php

 

[m0aje]

Thanks plog. SQL and VBA are way over my head, but will give it a try. I appreciate your help.

Sorry for the long delay in my reply.

 

[m0aje]

Hello plog

After doing some more research on SQL and some examples, I believe I am on the right track and also the right train. I am not sure if it could be done better or more efficiently, but it appears to be working.
This is what I came up with. I did it this way with all the tables:

SELECT "HP Servers Tested" AS equipment, Count(*) AS Records
FROM [HP SERVER]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION ALL
SELECT "Laptops Tested" AS equipment, Count(*) AS Records
FROM [LAPTOP]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION ALL
SELECT "Monitors Tested" AS equipment, Count(*) AS Records
FROM [MONITOR]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION ALL
SELECT "Printers Tested" AS equipment, Count(*) AS Records
FROM [PRINTER]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));

I am prompted to enter a start date and then an end date:

Start Date - 4/1/14 End Date - 4/29/14

Results:

equipment Records

HP Servers Tested 22
Laptops Tested 38
Monitors Tested 41
Printers Tested 18

I then created a Report off of this Union Query and it seems to work fine.

Thank you very much for the links and replies you have provided. You guys are the greatest!

Best regards,

m0aje