How can I count records from many tables

  • Thread starter Thread starter Kay
  • Start date Start date
K

Kay

HI

Im very stuck with a query. I am trying to count records from 3 tables
based on the DRIVER ID field. I want it displayed as
P01 6
P02 4
P03 7 Etc

I have got it working when trying it for one table, with following code
SELECT Count(*) AS Expr1, [table1].[Driver ID]
FROM [Current Jobs]
GROUP BY [table1].[Driver ID]
ORDER BY [table1].[Driver ID];

How do I get it working by searching through 3 tables

Your help is much appreciated
 
Dear Kay:

A COUNT(*) like you have made counts the rows in one table. Three such
queries would count the rows in 3 tables.

It is possible to do this putting the 3 queries together in a UNION to get
the results in one table:

SELECT [Driver ID], COUNT(*) AS Ct1, 0 AS Ct2, 0 AS Ct3
FROM table1
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS Ct1, COUNT(*) AS Ct2, 0 AS Ct3
FROM table2
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS Ct1, 0 AS Ct2, COUNT(*) AS Ct3
FROM table3
GROUP BY [Driver ID]

Save the above as Q1 (our choose your own name and substitute that name for
Q1 in the following):

SELECT [Driver ID],
SUM(Ct1) AS Ct1, SUM(Ct2) AS Ct2, SUM(Ct3) AS Ct3
FROM Q1
GROUP BY [Driver ID]
ORDER BY [Driver ID]

Tom Ellison
 
Hi Tom

The first piece of code works fine and I saved it as Q1
When running the second bit of code
SELECT [Driver ID],
SUM(Ct1) AS Ct1, SUM(Ct2) AS Ct2, SUM(Ct3) AS Ct3
FROM Q1
GROUP BY [Driver ID]
ORDER BY [Driver ID]

It displays an error message 'Circular reference caused by alias 'Ct1'
in query definitions SELECT list'

Any suggestions?
 
Dear Kay:

It's silly, but it does that. Change to:

SELECT [Driver ID],
SUM(Ct1) AS Cnt1, SUM(Ct2) AS Cnt2, SUM(Ct3) AS Cnt3
FROM Q1
GROUP BY [Driver ID]
ORDER BY [Driver ID]
Click to expand...

Tom Ellison


Kay said:
Hi Tom

The first piece of code works fine and I saved it as Q1
When running the second bit of code
SELECT [Driver ID],
SUM(Ct1) AS Ct1, SUM(Ct2) AS Ct2, SUM(Ct3) AS Ct3
FROM Q1
GROUP BY [Driver ID]
ORDER BY [Driver ID]

It displays an error message 'Circular reference caused by alias 'Ct1'
in query definitions SELECT list'

Any suggestions?
Click to expand...
 
Back
Top