Calculate sum of values in fields by date

[PavelowFE]

Hi again.

Trying to get a query to sum the values in all the records of one field, but only between certain dates. I've manage to do the Expression to sum the values, but can't figure out how to do it for a date range.

Thanks in advance for the help again.

Vince

 

[PHV]

Simply use a WHERE clause with the BETWEEN operator in your SQL code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PavelowFE]

Can you provide me an example of what it looks like?

Here's what I have so far.

SELECT Sum([# of Operators Trained]) AS Expr1
FROM [Mission Report];

Thanks,

Vince

 

[PHV]

an example of what it looks like
SELECT Sum([# of Operators Trained]) AS Expr1
FROM [Mission Report]
WHERE [your date field] Between #2009-01-01# And #2009-06-30#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

SELECT Sum([# of Operators Trained]) AS Expr1
FROM [Mission Report]
WHERE YourDateField BETWEEN #StartDate# and #EndDate#

Randy

 

[PavelowFE]

Finally got it to work. However, I tried to create a second one. It seemed to work at first, however the sum is not correct according to the table. It should have summed up to 20, but it comes out as 12. The difference between the two is that the "# of Aircrew Trained" field in Mission Report is a lookup to another table with the value set according to the airframe the user selects.

SELECT Sum([# of Operators Trained]) AS Expr1, Sum([# of Aircrew Trained]) AS Expr2
FROM [Mission Report]
WHERE [Date Tng Completed] Between #10/1/2008# And #9/30/2009#;

The help is greatly appreciated!

Vince

 

[PHV]

Are you sure you want a Sum instead of a Count ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PavelowFE]

Yes, I don't want to count how many records I have, I want the total of the values input into each record.

I.e. (The # of Aircrew Trained is a ComboBox to a table that has all the airframes and how many aircrew are in each. The user selects the airframe, but the value is the number assigned to the airframe selected).

Date # of Aircrew Trained # of Operators Trained
14 Jul 09 3 5
15 Jul 09 5 12

Total Aircrew Trained 8
Total Operators Trained 17

Thanks,

Vince

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886