MS Access total the results of a query

[moobarrymore]

Hi there,

Very new to MS access and I am having trouble with a certain query.

I have a table that collates order information i.e
Order#, Date, Amount charged, Date paid etc.

Basically I want to create a query that I can input 2 dates and return the total amount paid for all of those dates. I have the date parameter part working so it will return all the records within those 2 dates. I have tried to SUM the amount charged column in there but I can only get it to sum each returned date record. i.e if I do the month of may and 2 orders are on the 12th those are combined.
What I would like to do is return 1 totalled amount for every order in May.

Thank you

[NeoPa]

You need to GROUP BY the [Order#] field as well as selecting the [Date] range.

[moobarrymore]

Thank you for the response.

I am only using 2 fields for this query.
Date and Amount
In May I have 4 orders, each for $1000, 2 of these happen to be on the 12th 9the other 2 are 14th and 16th). If I put in the dates 1/5/11 and 31/5/11 i get records back that look like:
(I'm only showing amount not Date)
$2000
$1000
$1000

I'm just looking for a way for that to return 1 record of $4000

Kind regards

[NeoPa]

In that case don't GROUP BY at all.

Look, you're making this much more difficult than it need be. If you post your SQL we have a much better chance of understanding what you're talking about. I'll include instructions below for how to get that in case you don't yet know.

To extract the SQL from a QueryDef (saved Access query) it is only necessary to view the query in SQL View. This shows the underlying SQL for the QueryDef and is text (See Access QueryDefs Mis-save Subquery SQL for problems with SubQueries). When a QueryDef is open (either in Design View or Datasheet View) it is possible to switch to the SQL View simply by selecting View \ SQL View from the menu.

From here it is simple to Copy & Paste it to wherever you need it.

[moobarrymore]

SELECT Sum(Invoice.Amount) AS SumOfAmount
FROM Invoice
GROUP BY Invoice.[Date Completed]
HAVING (((Invoice.[Date Completed]) Between [Start Date] And [End Date]));

*

[moobarrymore]

Worked it out thank you, dropped group by and it's working exactly as I want it to

[NeoPa]

You should probably be looking at a query whose SQL is like :

SELECT Sum([Amount]) AS [SumOfAmount]
FROM   [Invoice]
WHERE  ([Date Completed] Between [Start Date] And [End Date])

[NeoPa]

Moobarrymore:
Worked it out thank you, dropped group by and it's working exactly as I want it to

That's good to hear. You also need to change the HAVING clause to a WHERE clause, but if it's working I expect you've worked that out already ;-)