Crosstab Query for more than one value 2

[jpkeller55]

I have data from a query (NRqry) that looks like this:
ClientName ServiceDate NetRevenue GrossMargin
Jones 3/12/07 5,000 1,800
Jones 3/27/07 6,000 2,300
Jones 4/01/07 3,500 1,900
Smith 3/15/07 4,000 2,000
Smith 3/21/07 3,500 1,700
Smith 4/05/07 4,500 1,900

I would like to run a query that will return something that totals NetRevenue and totals GrossMargin and then calculates the GM% (GrossMargin/NetRevenue) so it would look like this:
[blue]ClientName TotalNR TotalGM GM%
Jones 14,500 6,000 41.4%
Smith 12,000 5,600 46.7%
[/blue]
I have tried to use the Cross Tab query wizard in Access 2000 but it only calculates one total. Any help with some script to accomplish this would be greatly appreciated.

Thanks, JK

 

[JerryKlmns]

If one is not enough then take two!

One crosstab for TotalNR and second for TotalGM, and a new query joinning them on ClientName and calculate the percent. Just make sure that all ClientName values are included in both crosstab queries.

 

[jpkeller55]

Wow...that was much more simple than I thought it would be. I made two separate queries as you suggested and then a third combining the two to give me what I need. Thanks so much!

 

[JerryKlmns]

It's a trick to count and sum at the same time for the same variable, like excel.

Thanx for the star!

 

[dhookom]

Why use a crosstab query when you shouldn't need one? Won't this work for you?

SELECT ClientName, 
	   Sum(NetRevenue) as TotalNR, 
	   Sum(GrossMargin) as TotalGM,
	   IIf(Sum(NetRevenue)=0,0, Sum(GrossMargin)/Sum(NetRevenue)) as GMPct
FROM NRqry
GROUP BY ClientName;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

Looks like I missed JK's first part of his post

 

[jpkeller55]

Thanks Duane...I guess this was all much easier than I was thinking it would be. Appreciate your help. JK