I have a Access 2000 DB that lists items received in a warehouse. Its very simple - DateIn, Agency and Quantity are the main fields.

I am trying to build a query that prompts the user to enter the month and year between 2 dates and then show total items received between those 2 dates.

So far I have

Between [Type the beginning date:mmmm yyyy] And [Type the ending date:mmmm yyyy] In the criteria of my Date feild.

An Agency field "Grouped by"

and

Sum of Quantity: for the Quantity field

However when I run the query it returns the Agency totals by individual month. I just want a Grand total for each Agency between the dates input by the user. What am I doing wrong.

Thanks in advance.

Here's an example you could try based on the Orders and Orders Details tables in Northwind. You can adapt it to fit your situation.

The first query (Query2) provides totals of items purchased by CustomerID (read 'Agency') during the specified date range.

SELECT Orders.CustomerID, Orders.OrderDate, Sum([Order Details].Quantity) AS SumOfQuantity
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate
HAVING (((Orders.OrderDate) Between [enter start date] And [enter end date]));

The second query (Query3) calls Query2 and allows you to specify the specific CustomerID, and returns the total for period.

SELECT Query2.CustomerID, Sum(Query2.SumOfQuantity) AS SumOfSumOfQuantity
FROM Query2
GROUP BY Query2.CustomerID
HAVING (((Query2.CustomerID)=[enter customerID]));


To test, you just need to copy the querySQL to new queries in Northwind. Ensure that you name the first one Query2.

cheers!