Parameter query using dates leading to subtotals

[Gooford]

Hi,



I have a list of coded transactions in column A, Amounts in B and Dates in C.


I want to show subtotal for all amounts with the same codes in D

I can do this simply without the date parameters, but I need to give timeframes.


WHen I add the [From] and [Until] questions it then shows the amounts next to each date even though the date column is hidden.

I'm aiming for the subtotal of amounts for same codes between given dates:

Example
ABB 100 1/6/11
ABB 100 6/6/11
ABB 150 1/8/11
ABC 50 1/7/11
ABR 50 2/8/11
ABJ 50 1/5/11

If I enter 1/6/11 and 30/6/11 I would expect ABB to show £200 total as 150 is after that date and so not counted

Hopefully I have explained that ok - let me know if not.

Thanks and regards

Matt

[boblarson]

In your query (in design view in the QBE Grid) you would have all three fields down in the fields area but you would uncheck the SHOW box on the date and then with the GROUPING enabled (using the button which looks like a Greek Sigma) you would then change the date one from GROUP BY to WHERE.

Then you can put your parameter prompts in. However, I would avoid using parameter prompts and have a form for input. Here's another take on why not to use parameter prompts:
http://www.tek-tips.com/faqs.cfm?fid=6763

[Gooford]

Thanks you thats great.

I will read up on your link also.

Regards