Query Help

[Kay]

Hi

I have created a query to count the amount of records stored under each
driver ID. I Created 2 queries, one that ounts all the records booked
under each driver(Q1). The second query uses the first query and sum up
the data using the following code:

SELECT [Q1].[Driver ID], Sum([Q1].Current) AS SumOfCurrent,
Sum([Q1].Bookings) AS SumOfBookings, Sum([Q1].Contracts) AS
SumOfContracts
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];

The results are as follows:
Driver ID SumOfCurrent SumOfBookings SumOfContracts
P01 1 0 0
P04 0 1 2
P05 1 0 0
P06 1 0 1
P07 0 1 0
P08 0 1 1
P10 2 1 0

The problem is I want to display these totals sumd up and under one
column
P01 1
P04 3
P05 1 ETC

Any help is much appreciated

 

[Guest]

Try this:

SELECT [Q1].[Driver ID] AS Expr1, Sum([Q1].[Current])+Sum([Q1].[Bookings])
AS Total
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];

 

[Kay]

It works

Thanks very much Robert. Have you any idea how to make it so that the
user can enter a start date and end date and the results returned fall
in between the two dates

 

[Guest]

Kay,

If you are creating a query in design view in the Criteria Row type the
following (under the date column):

=[Enter The From Date] and <=[Enter The Thru Date]

What you put in the brackets is just text to pop up in an input box.