bobbobruns12
Technical User
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
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