Query Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kay - 20 Mar 2006 20:57 GMT

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

Reply to this Message

Robert_DubYa - 20 Mar 2006 21:36 GMT

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];

> Hi
>
[quoted text clipped - 27 lines]
>
> Any help is much appreciated

Reply to this Message

Kay - 20 Mar 2006 21:43 GMT

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

Reply to this Message

Robert_DubYa - 20 Mar 2006 22:15 GMT

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.

> 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

Reply to this Message