Sub Query Help

[AccessRookie]

I have a Query that lists Hours and Truck Number.

In a Report I calculate the Grand Total of Hours for All Truck Numbers.

How do I go about performing a Sub Query that calculates total Hours for ONLY Truck Number "Z000"?

Table Name is:
Activity Table

Here is the Code for the Query Now:
SELECT [Employee Table].[Employee Number], [Employee Table].[First Name], [Employee Table].[Last Name], [Activity Table].Date, [Activity Table].[Truck Number], [Activity Table].[Activity Code], [Activity Table].Hours
FROM [Employee Table] INNER JOIN [Activity Table] ON [Employee Table].[Employee Number] = [Activity Table].[Employee Number]
WHERE ((([Employee Table].[First Name])=[Enter Employee First Name]) AND (([Employee Table].[Last Name])=[Enter Employee Last Name]) AND (([Activity Table].Date) Between [Enter Beginning Date: mm/dd/yy] And [Enter Ending Date: mm/dd/yy]));


Thanks!

 

[rac2]

SELECT [Employee Table].[Employee Number], [Employee Table].[First Name], [Employee Table].[Last Name], [Activity Table].Date, [Activity Table].[Truck Number], [Activity Table].[Activity Code], [Activity Table].Hours

FROM [Employee Table]
INNER JOIN [Activity Table] ON [Employee Table].[Employee Number] = [Activity Table].[Employee Number]

WHERE ((([Employee Table].[First Name])=[Enter Employee First Name])
AND (([Employee Table].[Last Name])=[Enter Employee Last Name])
AND (([Activity Table].Date) Between [Enter Beginning Date: mm/dd/yy] And [Enter Ending Date: mm/dd/yy]))
AND (([Activity Table].[Truck Number]) = [Enter Truck Number: ] ;

 

[AccessRookie]

Thank You for the Help!

But....
I copied this code (added two closing parenthesis at the end) and placed it in the heading of a blank field and pressed Enter. It took it as EXPR1, which is okay so far.

Then I run my Query and it says the following error:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)"

I'm not sure what this means or what the EXISTS command means....

 

[rac2]

Sorry, I ignored your reference to a Sub Query.

It sounds like you are using the Design View to build a query. And that you pasted my query into the Field row of a blank column.

Do you want to create a column that calculates the total hours the truck in that row was used? Something like
George Burns on August 29th used truck 54 to transport people for 5 hours out of a total of 100 hours that truck 54 was used during August.

And you need to know how to get the total 100 hours for truck 54?

Make the following query and name it TruckTotals.

SELECT [Truck Number], SUM(Hours) AS "hours_total"
FROM [Activity Table]
WHERE Date Between [Enter Beginning Date: mm/dd/yy] And [Enter Ending Date: mm/dd/yy]
GROUP BY [Truck Number]

You can paste this into the SQL view, or drag-and-drop in design view. Drag Truck Number into the first column. The GROUP BY clause is created by clicking on the capital sigma icon, a line for Totals will be added to the design grid. Drag the column you want to total to the Field line. Click in the Group By line and select Sum.

Then you modify your existing query by adding to it the hours_total field from the TruckTotals query.

Select the existing Query. Click Design, click the Show Table icon. In the dialog for Show Table select the Queries tab. Select TruckTotals and click Add then Close.

Create a link between TruckTotals and your Query by dragging Truck Number from one to the other. Then drag hours_totals into a blank field. Close and save changes.

You can see SQL for the new query in Design->SQL View. It will contain an second JOIN clause something like this.

 . . .
INNER JOIN [TruckTotals] ON TruckTotals.[Truck Number] = [Activity Table].[Truck Number];

There will be some additional things to work out such as the date ranges for events in the Activity table and the totals in TruckTotals. And you need a LEFT JOIN instead of an INNER JOIN.

But at this point I am not at all confident that this is what you are looking for.

Hope some of this is helpful.

 

[AccessRookie]

This is Perfect!

Thank You soooooo much for your expert help!

I could never figure out how to Run a Query within a Query and this is perfect!