Adding Calculated field but on filtered data from another query?

[mugch]

Hi everyone, I have a report that is report showing odometer readings for our fleet of trucks. The report source is a simple query that pulls the data from the table. The report groups by Unit # and the detail shows date 01/31/2010, business km, personal km and driver.

Here is the SQL statement from the query
SELECT Vehicles.[Technicoil Unit Number], Vehicles.[VIN (Serial No)], Vehicles.Plate, [Monthly KM Logs].Month, [Monthly KM Logs].[Business KM], [Monthly KM Logs].[Personal KM], [Monthly KM Logs].Driver, [2008 Odometer].Odometer, [Monthly KM Logs].Comments
FROM (Vehicles INNER JOIN [Monthly KM Logs] ON Vehicles.[Technicoil Unit Number] = [Monthly KM Logs].[Unit #]) INNER JOIN [2008 Odometer] ON [Monthly KM Logs].[Unit #] = [2008 Odometer].[Unit #]
WHERE (((Vehicles.[Technicoil Unit Number]) Like [Which unit number would you like to search or would you like to see all (*)?]) AND (([Monthly KM Logs].Month) Like [What Year? *2010*]) AND ((Vehicles.[Sold / Returned])=No))
ORDER BY [Monthly KM Logs].Month;


I need to show the total km for 2010, so I put a filter on the query so the report asks for what year. ie *2010* and it pulls the year specified and calculates the sum. However I also need to show a bottom field for total 2008 km and one for total 2009, then add to the existing sum of the report for 2010 to show a running total. Is there any way to do this? I'm not very good at formulas, and every expression I've tried gives an error or is too complex. I'm hoping someone can help me out. I've spent about 5 hours straight on something I'm thinking is probably simpler than I'm making it...

[NTC]

well you can not filter for just 2010 and also get 08 & 09 data from the same record source - so you are going to need to alter your approach.

1 approach; don't filter at the query - instead use the Report feature to sort by Year - and sum each.

2nd approach; set up an aggregate query that sums 2008 & 2009 year data. Insert this query as a subreport into the footer of your report ( or Make a report on this result and insert this report as a subreport - might look a little bit better - plus with a report you can then aggregate/sum the 2 years together also if need be)

hope this helps.

[mugch]

Thanks I'll try grouping by year on the report and see if they will go for that. The word aggregate scared me. lol