Query from differnt tables

I have tables 5 seperate tables with the same fields in all of them. I have
a seperate table with employees id and their name.
I need to set up a query for a report that will have the information to pull
into 1.
Ex. The report needs to show how many batch_id in each platform with their
name showing instead of their ID.
Fields : ID , Batch_ID, Platform
The employee table has ID and name.
Outcome would be ex: Smith 62 commerical, Smith 62 Medical than a total.

5 seperate tables with the same fields in all of them.
You should have only one. Use a union query to pull them together --
SELECT ID , Batch_ID, Platform, "Tb1" AS [TBL]
FROM Table1
UNION ALL SELECT ID , Batch_ID, Platform, "Tb2" AS [TBL]
FROM Table
UNION ALL SELECT ID , Batch_ID, Platform, "Tb3" AS [TBL]
FROM Table3
UNION ALL SELECT ID , Batch_ID, Platform, "Tb4" AS [TBL]
FROM Table4
UNION ALL SELECT ID , Batch_ID, Platform, "Tb5" AS [TBL]
FROM Table5

The output field [TBL] above is if you really need to know where the data
came from.

Then use a totals query --
SELECT Name, Batch_ID, Count(Batch_ID) AS Batch_Total
FROM Employee LEFT JOIN MyUnionQuery ON Employee.ID = MyUnionQuery
GROUP BY Name, Batch_ID;

"LG" wrote:

I have tables 5 seperate tables with the same fields in all of them. I have
a seperate table with employees id and their name.
I need to set up a query for a report that will have the information to pull
into 1.
Ex. The report needs to show how many batch_id in each platform with their
name showing instead of their ID.
Fields : ID , Batch_ID, Platform
The employee table has ID and name.
Outcome would be ex: Smith 62 commerical, Smith 62 Medical than a total.

On Tue, 30 Jun 2009 13:22:01 -0700, LG wrote:

I have tables 5 seperate tables with the same fields in all of them.

Then you have a misdesigned database. It would be much better to have ONE
table with an additional field identifying which batch of information this
record belongs to.

I have
a seperate table with employees id and their name.
I need to set up a query for a report that will have the information to pull
into 1.
Ex. The report needs to show how many batch_id in each platform with their
name showing instead of their ID.
Fields : ID , Batch_ID, Platform
The employee table has ID and name.
Outcome would be ex: Smith 62 commerical, Smith 62 Medical than a total.

You'll need to create a query with the employee table, and join *all five* of
the other tables to it by ID. Use a "Left Join" - select the join line in the
query window and select option 2 "Show all records in Employees and matching
records in other table".
--

John W. Vinson [MVP]

How do I get it to have date parameters? Where the supervisor or processor
can pull up their completed work by date(s)
"KARL DEWEY" wrote:

5 seperate tables with the same fields in all of them.
You should have only one. Use a union query to pull them together --
SELECT ID , Batch_ID, Platform, "Tb1" AS [TBL]
FROM Table1
UNION ALL SELECT ID , Batch_ID, Platform, "Tb2" AS [TBL]
FROM Table
UNION ALL SELECT ID , Batch_ID, Platform, "Tb3" AS [TBL]
FROM Table3
UNION ALL SELECT ID , Batch_ID, Platform, "Tb4" AS [TBL]
FROM Table4
UNION ALL SELECT ID , Batch_ID, Platform, "Tb5" AS [TBL]
FROM Table5

The output field [TBL] above is if you really need to know where the data
came from.

Then use a totals query --
SELECT Name, Batch_ID, Count(Batch_ID) AS Batch_Total
FROM Employee LEFT JOIN MyUnionQuery ON Employee.ID = MyUnionQuery
GROUP BY Name, Batch_ID;

"LG" wrote:

I have tables 5 seperate tables with the same fields in all of them. I have
a seperate table with employees id and their name.
I need to set up a query for a report that will have the information to pull
into 1.
Ex. The report needs to show how many batch_id in each platform with their
name showing instead of their ID.
Fields : ID , Batch_ID, Platform
The employee table has ID and name.
Outcome would be ex: Smith 62 commerical, Smith 62 Medical than a total.

Add you date field into the union and totals queries.

"LG" wrote:

How do I get it to have date parameters? Where the supervisor or processor
can pull up their completed work by date(s)
"KARL DEWEY" wrote:

5 seperate tables with the same fields in all of them.
You should have only one. Use a union query to pull them together --
SELECT ID , Batch_ID, Platform, "Tb1" AS [TBL]
FROM Table1
UNION ALL SELECT ID , Batch_ID, Platform, "Tb2" AS [TBL]
FROM Table
UNION ALL SELECT ID , Batch_ID, Platform, "Tb3" AS [TBL]
FROM Table3
UNION ALL SELECT ID , Batch_ID, Platform, "Tb4" AS [TBL]
FROM Table4
UNION ALL SELECT ID , Batch_ID, Platform, "Tb5" AS [TBL]
FROM Table5

The output field [TBL] above is if you really need to know where the data
came from.

Then use a totals query --
SELECT Name, Batch_ID, Count(Batch_ID) AS Batch_Total
FROM Employee LEFT JOIN MyUnionQuery ON Employee.ID = MyUnionQuery
GROUP BY Name, Batch_ID;

"LG" wrote:

I have tables 5 seperate tables with the same fields in all of them. I have
a seperate table with employees id and their name.
I need to set up a query for a report that will have the information to pull
into 1.
Ex. The report needs to show how many batch_id in each platform with their
name showing instead of their ID.
Fields : ID , Batch_ID, Platform
The employee table has ID and name.
Outcome would be ex: Smith 62 commerical, Smith 62 Medical than a total.