Complex query question

[ghh3rd]

I have a table, such as this:

State	Count	Gross	Net
FL	4	120	60
NC	3	90	45
NY	5	30	15

I am trying to query it so the state FL, NC, etc become Column names, with their Gross beneath them, followed by a 'Total Gross' column, etc...

FL	NC	NY	Gross	Net	Count
120	90	30	240	120	12

A crosstab query doesn't seem to work out for me - I looked at pivot tables without any luck.

Any help would be appreciated.

Thanks - Randy

ps An XLS is attached

 

[Banana]

Actually you probably are better off with a crosstab query and a aggregate query as I don't think you can do it in single query. (You are in essence asking two questions; what are Gross for every states, and what are the total?)

 

[sportsguy]

very simple

select Iif([State]="FL",[Gross],0) as FL, Iif([State]="NC",[Gross],0) as NC, Iif([State]="NY",[Gross],0) as NY, Sum([Gross]) as TotalGross, Sum([Net]) as TotalNet, Sum([Count]) as TotalCount FROM TABLENAME;

this query is more efficient than a crosstab, as the query only goes through the database once, and the fields are predefined. . .

sportsguy

 

[ghh3rd]

Thanks Sportsguy -- I see that you live in Boston. I lived in the Wadsworth Hotel (dormitory) in Kenmore Square for one year while going to Franklin Institute in the mid 70's. Great town!