Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / March 2006

Tip: Looking for answers? Try searching our database.

count from multiple tables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kay - 20 Mar 2006 17:27 GMT
Hi
Im creating a query for my chart to lookup a count of records from 3
different tables using the Driver ID field. I have got the following
code but it comes up with the following error 'You tried to execute a
query that does not include the specified expression 'Driver ID' as
part of an aggregate funtion'. My code  is a follows:

SELECT Count(*) AS Expr1
FROM (SELECT Count(*) AS Expr1, [Driver ID]
FROM [Current Jobs]

UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Bookings]

UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Contract Jobs]) AS query1

WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]
ORDER BY [Driver ID];

Any help will be much appreciated
Darren - 20 Mar 2006 17:53 GMT
First, Driver id is not selected in your top query to group by.
Second Driver Id I believe needs to have a group by in all of your sub
queries
Third, I think you actually want to sum the top level, not count

SELECT Sum(Expr1) AS Expr1, [Driver ID]
FROM
(
SELECT Count(*) AS Expr1, [Driver ID]
FROM [Current Jobs]
WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]

UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Bookings]
WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]

UNION ALL
SELECT COUNT(*) AS Expr1, [Driver ID]
FROM [Contract Jobs]
WHERE ((([Date]) Between [From Date] And [To Date]))
GROUP BY [Driver ID]
)
GROUP BY [Driver ID]
ORDER BY [Driver ID];

> Hi
> Im creating a query for my chart to lookup a count of records from 3
[quoted text clipped - 20 lines]
>
> Any help will be much appreciated
Kay - 20 Mar 2006 18:00 GMT
Hi
Doesnt the sum option give you a total. The driver ID refers to many
drivers. So I need to display each driver ID and a number next to it
showing the no of records booked under that ID

Any Suggestions
Darren - 20 Mar 2006 18:10 GMT
The sub queries have already done the counting, the top query should just
add these up.

> Hi
> Doesnt the sum option give you a total. The driver ID refers to many
> drivers. So I need to display each driver ID and a number next to it
> showing the no of records booked under that ID
>
> Any Suggestions
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.