report with multiple expense summary

[EthanL]

I try use Cross tab query but cross tab only allow one value.

is there any way I can creat a report like excel pivot table using access?

my database includes folloing colums: product, quantity, sale price, allocated adm exp, allocated purchasing exp, allocated supplies, allocated others,allocated Capital, allocated hotel service.

I just need to have a report that shows summary of all those exp for certail time period.

Thanks,
EL

 

[lespaul]

SELECT SUM(PRODUCT), SUM(QUANTITY), SUM([SALE PRICE]), SUM([ALLOCATED PURCHASING EXP], SUM(allocated supplies), SUM(allocated others), SUM(allocated Capital), SUM(allocated hotel service) FROM TableName WHERE aDate Between #1/1/2006# AND #6/30/2006#

If what you meant was you needed the sum of each category BY PRODUCT, you would need to modify slightly to:

SELECT PRODUCT, SUM(QUANTITY), SUM([SALE PRICE]), SUM([ALLOCATED PURCHASING EXP], SUM([allocated supplies]), SUM([allocated others]), SUM([allocated Capital]), SUM([allocated hotel service]) FROM TableName WHERE aDate Between #1/1/2006# AND #6/30/2006# GROUP BY PRODUCT

A CrossTab query takes something like:

Jan 2006 Shoes 25
Jan 2006 Socks 50
Feb 2006 Shoes 18
Feb 2006 Socks 3

and transforms it to:
[tt]
Jan 2006 Feb 2006
Shoes 25 18
Socks 50 3

[/tt]

If you really want something like that post back with some sample data and your expected results.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi