Sales Query

[jasonphillipstx]

I have a table called tbl_sales with fields saledate, department, amount, etc

I want a query that will show me results something like the following

Department Sales_YTD Sales_LastYearTD Sales_MTD Sales_LastYearMTD Sales_ThisWeek Sales_Today
Dept1 $100 $100 $100 $100
Dept2 $100 $100 $100 $100
Dept3 $100 $100 $100 $100
Total $300 $300 $300 $300

I think I may need to make it a report to get a total. I have played with various queries but just cannot get all the data I want. I suppose I could use DLookup for each date range. I would appreciate any help.

 

[alvechurchdata]

A query would have to be fiendishly complicated to have such a mix of columns. Every row of a query has to be the same so you'd have to have one row per day (to show Sales_Today) and each day's row would have to hold a value of the other fields like Sales_ThisWeek, Sales_YTD.

I think you might find it easier with a report.

Geoff Franklin

http://www.alvechurchdata.co.uk