query with date

[nounou]

hello all. im new here and i need a bit of help

i have the table

ID DATE AMOUNT

i have many records with same DATE BUT DIFFERENT AMOUNT.
I need a query tha ask me the start date and the end date and when i give the dates i need to give me back the sum of those amounts.

please help me



thnx in advance

[pbaldy]

Two things to look up, "parameter query" and "totals query". You want both. Most of us would use a form to gather the user input, but you can use the brackets method.

[June7]

I agree, form for criteria input is better. Can't validate user input to query parameter prompts.

Could build a report using Grouping & Sorting with aggregate calcs. This will allow display of record details and summary calcs.

[nounou]

thank you both for reply but... im new in this and if you can help me by giving me the commands how i can writte it in sql.

the table name is INPUTS
and the fields are

ID DATE AMOUNT

i want to give a start date and a end date and the query give me back the sum amount of all those days.
e.g

ID DATE AMOUNT
1 1/2/2012 20
2 2/2/2012 30
3 3/2/2012 40
4 5/2/2012 10

i give start date 2/2/2012 and end date 5/2/2012
i want the query to give me the result 80.

plz help me

[June7]

This would be an aggregate (Totals) query. If summarizing by group would also include GROUP BY clause. With the query in design view, click the Totals button (looks like Sigma). This will change the query grid to show a Total row. Select appropriate parameter under each column. Access Help has guidelines on building queries with designer and wizard.

Your sample shows a summary for all records between given dates, does not summarize by date so GROUP BY clause not needed, just the sum of amount.
Final SQL should look like:
SELECT Sum(Amount) As SumOfAmount FROM tablename WHERE [Date] Between [enter start date] And [enter end date];

If you don't want to have summary by any grouping, you can use the Totals row of a table or query. View the table or query normally (Datasheet view) and click Totals (again, looks like sigma). This will open a Totals row at bottom of table or query. Select an aggregate function from dropdown under each column you want to calculate.

[pbaldy]

You would not want to group on date (which by the way is a bad field name, conflicting with the Date() function).

[nounou]

THANK I DID THAT AND THE RESULT I TOOK FROM THE QUERY WAS

DATE AMOUNT
2/2/2012 30
3/2/2012 40
5/2/2012 10

Instead of the number 80 which is the sum between those dates.
is there anything else i can do?

[June7]

Read my post again. I edited it while you posted yours.

[nounou]

Perfection!!!!

Thank you!!!