Crosstab Query?

[sinyce]

I have an table in Access that looks like this:

NAME APPLICATION
JOE WINDOWS
JOE OFFICE XP
JOE WINZIP
MARY WINDOWS
MARY ADOBE
MARY CRYSTAL
BOB WINDOWS
BOB NETSCAPE
BOB WINZIP
BOB ADOBE

TRYING TO GET A QUERY TO DISPLAY THIS:
NAME WINDOWS OFFICE XP ADOBE CRYSTAL NETSCAPE WINZIP
JOE X X X
MARY X X X
BOB X X X X

TOTAL 3 1 2 1 1 2

This is simplified. It will give me at a glance the number of licences I am using for each application.

Any help will be greatly appreciated.

Sinyce

 

[dwalker]

Synce

There's no single query that can give you a total along the bottom row. You might consider a sequence of queries that does something like this...

A crosstab query that produces the results you've shown above, but without the total (because it can't).

A MakeTable query based on your CrossTab query that makes a table that looks like your CrossTab

A Select query that calculates the Total row. This might actually take 2 or 3 select queries in sequence.

Finally, an Append query that appends the Total row to the table you made earlier.

This is not a very elegant a solution, but it can be made to work.

Perhaps you could consider a single CrossTab query that gives you all the data you need but transposes the rows and columns. The following SQL will produce a table like the one you have above but with the application names as rows and user names as column headings - plus an extra column containing the totals.

TRANSFORM Count(Table1.application) AS CountOfapplication
SELECT Table1.application, Count(Table1.application) AS CountOfapplication1
FROM Table1
GROUP BY Table1.application
PIVOT Table1.name;

Replace Table1 with your table name.

Paste this into the SQL view of a new query.

Regards

Damien

 

[dwalker]

.... one other thing. You can probably get the results you originally posted using a Union query. I'm not too familiar with these though, perhaps someone else could post the SQL from a Union query that might do the job.

Regards

Damien

 

[mwatrous]

Another approach that keeps the whole thing "live" and updatable:

For each application, create an aliased field:

Windows: IIf([Application]="Windows","X&quot
XP: IIf ([Application]="XP","X&quot

Then create a report based on the query and include fields in the report that count the Xs.

Or create a second query based on the first query and count the Xs there. You can even link the two queries together to form a third query, and then display everything in a report based on the combined query.