Distinct Values in Crosstab

[bobbobruns12]

Hi there,

I'm trying to format a crosstab query to fit better to an excel spreadsheet I have. I have a query here:

TRANSFORM Count(*) AS TotalCount
SELECT IDXRAD_RFB_WEEKSTATS.XRSC, IDXRAD_RFB_WEEKSTATS.XDATE, Count(TotalCount) AS DistinctCount
FROM IDXRAD_RFB_WEEKSTATS
GROUP BY IDXRAD_RFB_WEEKSTATS.XRSC, IDXRAD_RFB_WEEKSTATS.XDATE
PIVOT IDXRAD_RFB_WEEKSTATS.PMRN In (Null);

Which Produces

XRSC XDATE XDISTINCT VALUE <>
A 12/17/06 24
A 12/18/06 24
A 12/19/06 36
A 12/20/06 42
A 12/21/06 22
B 12/17/06 15 etc,etc,

I could just keep flipping back and forth between access and excel to enter this but I'd rather set up a macro to import it which I've done with other data. However, in order to do this I need it set up like this:

XRSC 12/17/06 12/18/06 12/19/06 12/20/06 12/21/06
A 24 24 36 42 22
B 15

If anyone can help me upgrade my sql to help me do this I'd be most grateful as I feel like I've tried everything. Thanks in advance.

Bobby

 

[dhookom]

Your crosstab looks like it outputs a basic totals query. If you want dates as your column headings, consider something like:

TRANSFORM Count(*) AS TotalCount
SELECT IDXRAD_RFB_WEEKSTATS.XRSC, Count(TotalCount) AS DistinctCount
FROM IDXRAD_RFB_WEEKSTATS
GROUP BY IDXRAD_RFB_WEEKSTATS.XRSC
PIVOT IDXRAD_RFB_WEEKSTATS.XDATE;

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]

 

[bobbobruns12]

That code gives me a distinct count of the dates with values, not the distinct records for a particular day.

ie Looks Like

XRSC DISTINCT COUNT DATE 1 DATE 2 DATE 3 DATE 4 DATE 5
A 5

 

[dhookom]

What happened to XRSC value B? How was 5 generated? How did your first SQL generate columns named "XDISTINCT" and "VALUE" when there was nothing in your SQL to produce them? How does your query "Looks Like" return "Date 1", "Date 2",... when it should return date values?

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]