running or sub total using sql query in access

[learning_graccess]

I have a data similar to below
date >> items >> qty
23.05.2011 >> pen >> 3
23.05.2011 >> pencil >> 10
24.05.2011 >> paper >> 33
25.05.2011 >> paper >> 23
23.05.2011 >> paper >> 24

Now I want total for 23.05.2011 and want output like below
23.05.2011 >> pen >> 3
23.05.2011 >> pencil >> 10
23.05.2011 >> paper >> 24
Total >> >> 37

I heard that using report it's possible, in sql it's not possible in access. But i'm very new to access and I don't know except few sql query.
And also after a long search, I found that using Union All command this is possible in sql too but I don't know how.

[rpeare]

SELECT tblTest.SaleDate, tblTest.Item, Sum(tblTest.Qty) AS TotQty
FROM tblTest
GROUP BY tblTest.SaleDate, tblTest.Item
HAVING ((SaleDate) = [Enter the Date])
ORDER BY SaleDate, Item
UNION ALL
SELECT tblTest.SaleDate, 'Total' AS Item, Sum(tblTest.Qty) AS SumOfQty
FROM tblTest
GROUP BY tblTest.SaleDate, 'Total'
HAVING ((SaleDate) = [Enter the Sale Date])
ORDER BY SaleDate, Item

This will give you what you want, I changed your 'date' field to 'saledate' because DATE is a reserved word in Access and will only cause you problems if you try to use it as a field name.

Secondly this will prompt you for the [Enter the Sale Date] twice, because basically a union query is a series of queries whose results you are appending to a long list. To avoid the multiple prompts you can have your date input on a form and in the individual queries have a reference to that form/field like this:

HAVING ((SaleDate) = [forms]![MyForm]![MyDate])

Where myform and mydate would be the name of the form you have for the query setup and mydate would be the name of the text box that you're inputting your search date.

[learning_graccess]

thanks mate.
I have a question. Suppose I want to view all dates and subtotal in each date and grand total at the end like below, is it possible in access.
23.05.2011 >> pen >> 3
23.05.2011 >> pencil >> 10
23.05.2011 >> paper >> 24
Total >> >> 37
24.05.2011 >> paper >> 33
Total >> >> 33
25.05.2011 >> paper >> 23
Total >> >> 23
Grand Total >> 93

[rpeare]

yes, it's the same methodology as the previous example just a little more complex you'd have three layers of to the union query instead of two.

I'm not sure why you'd want to do this but it can be done. A far easier solution would be to build a with the subtotaling you want then export the report to an excel spreadsheet (which is what I suspect you're trying to do?)

[learning_graccess]

yes, it's the same methodology as the previous example just a little more complex you'd have three layers of to the union query instead of two.

I'm not sure why you'd want to do this but it can be done. A far easier solution would be to build a with the subtotaling you want then export the report to an excel spreadsheet (which is what I suspect you're trying to do?)

Thank you again.

The original data is more than 2,00,000 so it's not possible in excel. Access pivottable looks much easier but it takes ages to produce output maybe because of too much data. So the only way which possible right now is SQL query in Access which I can export as I want later in excel (only output). I don't know about any reports, forms, etc. in Access also.

I would be happy if you give me code for the grand total