creating summary of data

[anderma8]

I have the detail data in a query working fine in MSAccess 2000 sp3 and I'm trying to create a summary of the data. My query is as follows:

SELECT CUH.CULevel, CUH.AREA, TYPES.Asset, Count(TYPES.Asset) AS Counts
FROM (TYPES LEFT JOIN Broker ON TYPES.[Owner/LastLogin] = Broker.LOGIN_ID) LEFT JOIN CUH ON Broker.COSTUNIT = CUH.COST_UNIT
WHERE (((TYPES.Asset) In ("T30", "DESKTOP")))
GROUP BY CUH.CULevel, CUH.AREA, TYPES.Asset;

with the output like:

CULevel AREA Asset Counts
11000 Disease Areas DESKTOP 121
11000 Disease Areas T30 36

but I'm trying to get the output like:
CULevel AREA T30Counts Desktop Count
11000 Area1 36 121

I'm pretty new in MSAccess and I've tried a number of things, but this is the closest I've gotten. Any help from anyone would be greatly appreciated!

 

[Jonathan Kok]

You need to use PivotTables to do this.

If 'Counts' is actually performing a record count, then make sure 'Totals' is turned on ('View-->Totals').
Set CULevel, AREA, ASSET to 'Group By'. Then set Counts to 'Count'.

If it's a typed-in value, leave totals off.

Change to 'PivotTable' View

From the Field List, drag CULevel and Area to the "Row Fields".
Drag "Asset" to the "Column Fields".
Drag "Counts" to the "Data".

That should do what you're looking for.

EDIT: I was under the impression that 'Counts' was actually counting number of records. I've edited the above to reflect that.

 

[pdx_man]

You can also try this in your SELECT statement:

SELECT CUH.CULevel, CUH.AREA, SUM(Iif(TYPES.Asset = "T30",1,0)) As T30Counts, SUM(Iif(TYPES.Asset = "DESKTOP",1,0)) As DesktopCounts

 

[anderma8]

EXCELLENT - both ways work!

I tried both ways and I've been able to each of them to work. I did not know about the TRANSFORM and PIVOT components nor had I used the 'SUM(Iif(' command!! THANKS to both of you... I've learned 2 new things today!

 

[Jonathan Kok]

You can also try this in your SELECT statement:

SELECT CUH.CULevel, CUH.AREA, SUM(Iif(TYPES.Asset = "T30",1,0)) As T30Counts, SUM(Iif(TYPES.Asset = "DESKTOP",1,0)) As DesktopCounts

Even better. What can I say...I've been on a PivotTable kick lately