Crosstab - help with what should be a simply crosstab

[foma]

given data from a table in the form of:

uid	state	oth_uid
101	cat1	1
101	cat2	2
102	cat1	3
103	cat1	4
104	cat1	5
104	cat1	6
104	cat1	7
104	cat1	8
104	cat1	9
104	cat2	10
105	cat2	11

I need results from a crosstab in the form of:

Type	Cat1	Cat2	Total
Total Recs	8	3	11
Unique Recs	4	3	5

I can run a crosstab query to get one line of the above, but not the other. Not only don't I know how to transform this into two rows, I don't know how to limit the 2nd row to unique uid's. Help please.



TRANSFORM Count(db.uid) AS CountOfid
SELECT "Total Recs" AS hdr
FROM db
GROUP BY "Total Recs"
PIVOT IIf([state]="cat1","Cat1,"Cat2");

[ranman256]

did you use the crosstab wizard? if so, it would have asked you what fields to put as row items,
your answer would have been UID.
then columns, you would click STATE.

use the wizard and always get it right.

[InsuranceGuy]

I've had to do this before in Access. What I ended up doing was creating two queries and a table to assist in getting my result.
The first query was a create table query which took the first Crosstab and generated the Total Recs row only (and in doing so the data structure for the table).
The second query was an append query which took the second Crosstab which generated the Unique Recs row only and appended it to the table just created.
Then my report used this output table which I constantly recreated.

the button to run the report would run the make table query, then the append query, before opening the report.

Good luck,


Jeff

[foma]

did you use the crosstab wizard? if so, it would have asked you what fields to put as row items,
your answer would have been UID.
then columns, you would click STATE.

use the wizard and always get it right.

@ranman256, Thanks for the quick help attempt. Yes, I used the wizard this time. No, it didn't produce the desired results with those field choices. I'm an old coder and even know sql to a fair degree. Access's innerds aren't fully sql compliant and I have to do this project in Access. So I dinked around entirely too long to try to get the results requested and got stuck.

I like how responsive this forum is, though.

Thanks again for trying.

[foma]

I've had to do this before in Access. What I ended up doing was creating two queries and a table to assist in getting my result.
The first query was a create table query which took the first Crosstab and generated the Total Recs row only (and in doing so the data structure for the table).
The second query was an append query which took the second Crosstab which generated the Unique Recs row only and appended it to the table just created.
Then my report used this output table which I constantly recreated.

the button to run the report would run the make table query, then the append query, before opening the report.

Good luck,


Jeff

@InsuranceGuy Thanks for responding. I'd briefly thought about making pre-queries, but was just insisting that I find an elegant solution all in a single crosstab. Naivete, I suppose. This project will require a rather large amount of duplicated efforts and I was hoping to minimize that effort by getting it all done in one query. Oh well. Thanks for the tip. I'll buckle down and go the route of multiple queries.