Cross Tab Query- Unique Counts

[lewis33]

Hi, Maybe this problem has been beaten to death. However, I'm looking for a viable solution to a crosstab query problem. Basically, here's the SQL:

TRANSFORM Count([ANCILLARY MASTER].NAME) AS CountOfNAME
SELECT [ANCILLARY MASTER].COUNTY_NAME, [ANCILLARY MASTER].SPECIALTY
FROM [ANCILLARY MASTER]
GROUP BY [ANCILLARY MASTER].COUNTY_NAME, [ANCILLARY MASTER].SPECIALTY
PIVOT [ANCILLARY MASTER].PRODUCT;

Basically, I am counting ancillary providers by specialty (in our world, dialysis, home health, home infusion, etc) by county and doing unique counts. Problem is, the cross tab gives me a sum total of the counts where I want a unique count.

I get results like:

Dialysis Product
Allegheny County HMO PPO POS Total
3 3 3 9

However, I want the total to be like 3 or whatever the unique count is regardless of the product.

Thank you Thank you Thank you for any help

 

[dhookom]

Your crosstab SQL doesn't include a total. How do you get a 9 and why would you want to get a 3?

You might need another query to calculate your 3.

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]

 

[lewis33]

Hi Duane,
I do have a total. It's is like my data is:
County,Name,Product
Allegheny,joe's dialysis,HMO
Allegheny,joe's dialysis,PPO
Allegheny,joe's dialysis,POS
Allegheny,jim's dialysis,HMO
Allegheny,jim's dialysis,PPO
Allegheny,jim's dialysis,POS
Allegheny,tim's,dialysis,HMO
Allegheny,tim's dialysis,PPO
Allegheny,tim's dialysis,POS

I want to get counts in the cross tab like:
HMO:3
PPO:3
POS:3
Total (unique in county):3
I want it to give me a unique count of name in county and not consider the product (HMO, PPO, POS). Now, I get a sum of 9 for my total instead of a unique count of name.

 

[dhookom]

I would start by creating a totals query that groups by County and Name (without the product). You can then join this totals query to your crosstab.

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]

 

[lewis33]

Thanks a ton Duane. I'll give it a whirl. Wasn't sure if there was a way to write the SQL within Access to have just one query.

 

[dhookom]

BTW: Name is not a good name for a field (or anything else) in Access since every object has a name property. Besides most objects in the world have a name (property). Rather than "Name", you should use FirstName, PersonName, ClinicName, ...

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]