Problem grouping data

[Larry Hodges]

Running Access 10.

I have a table. Fields are:

Customer
Date
Price

I want to create a report that displays like this:

Customer YTD Last Year
Jim's Service $500 $400

Both YTD and Last Year on the report are derived from the price field, using
Date to group the data. Is there a way to do this in the report and with a
query?

What I'm currently doing is running two queries that append a temp table in
the front end...one appends YTD data from the Price field and puts it in a
field named YTDPrice. The other appends Last Year's data to a different
field. That way I can group and sum the data in the report side by side
without doing subreports. Then upon exiting the report, I run a delete
query to clean up.

The problem is the front end grows each time the report is run. I see no
way to programmatically compact it while it's open either. I can't even
find a way to compact it programmatically when closing.

So, any suggestions?

 

[Duane Hookom]

Why is it that OPs rarely provide their table name?

Try:
SELECT Customer,
Sum(Abs(Year(Date())=Year([Date]))*Price) AS YTD,
Sum(Abs(Year(Date())-1=Year([Date]) And
DatePart("y",Date())>=DatePart("y",[Date]))*[Price]) AS [Last Year]
FROM tblHodges
GROUP BY Customer;

Consider never using Date as a field name since it is the name of a
function.