Crosstab calculating dates to days

[Guest]

Hello All

Please assist.
I need to create a crosstab query that will count patient visits
between 2 dates in the table.
dates: report rundate - visit date
clinic id 1-2 days 3 -7 days 10-30 days 30+

6 10 50 60

Thanks

 

[Michel Walsh]

TRANSFORM Nz(COUNT(*), 0)
SELECT clinicID
FROM tableName
GROUP BY clinicID
PIVOT CHOOSE( visitDate>=date()-2, "1-2 days",
visitDate >= date()-9, "3-9 days",
visitDate >= date()-30, "10-30 days",
true, "30+")





Note that you were having a 'hole' (For days 8 and 9), so I assume it was 9
rather than 7.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Need a little more to figure out how to do the crosstab query. What are
your field names? Post sample data.

 

[Guest]

Thanks
Here it is:

clinicid clinic1 clinic2
patientid patient123 patient124
run date 3/23/2007 3/23/2007
visit date 3/21/2007 3/1/2007

 

[Michel Walsh]

Change the CHOOSE for:

CHOOSE([run date]-[visit date] >=2, "1-2 days", ... )


Vanderghast, Access MVP

 

[Guest]

Thanks
However, the query result does not show the days columns headings.
It only gives total counts for each clinic but not by date.

Pivot Choose([run date]-[vis date]<=2,"1-2 days",[run date]-[vis
date]<=7,"3-7 days",[run date]-[vis date]<=30,"10-30 days",True,"30+")

Change the CHOOSE for:

CHOOSE([run date]-[visit date] >=2, "1-2 days", ... )


Vanderghast, Access MVP

TRANSFORM Nz(COUNT(*), 0)
SELECT clinicID
FROM tableName
GROUP BY clinicID
PIVOT CHOOSE( visitDate>=date()-2, "1-2 days",
visitDate >= date()-9, "3-9 days",
visitDate >= date()-30, "10-30 days",
true, "30+")





Note that you were having a 'hole' (For days 8 and 9), so I assume it was
9 rather than 7.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Add the date field in the SELECT clause and in the GROUP BY clause. That
date can only be a GROUP, since it is NOT COMMON for all records counted
under a given column. I mean,

runDate VisitDate
2007.01.03 2007.01.01 difference of 2 days
2007.04.03 2007.04.01 difference of 2 days


so, both records has to be counted under the same column, "1-2days", but
which of the two dates would it be, January or April, if ***a*** date
HAS TO be selected for that column?



Vanderghast, Access MVP

Thanks
However, the query result does not show the days columns headings.
It only gives total counts for each clinic but not by date.

Pivot Choose([run date]-[vis date]<=2,"1-2 days",[run date]-[vis
date]<=7,"3-7 days",[run date]-[vis date]<=30,"10-30 days",True,"30+")

Change the CHOOSE for:

CHOOSE([run date]-[visit date] >=2, "1-2 days", ... )


Vanderghast, Access MVP

TRANSFORM Nz(COUNT(*), 0)
SELECT clinicID
FROM tableName
GROUP BY clinicID
PIVOT CHOOSE( visitDate>=date()-2, "1-2 days",
visitDate >= date()-9, "3-9 days",
visitDate >= date()-30, "10-30 days",
true, "30+")





Note that you were having a 'hole' (For days 8 and 9), so I assume it
was
9 rather than 7.



Hoping it may help,
Vanderghast, Access MVP


Hello All

Please assist.
I need to create a crosstab query that will count patient visits
between 2 dates in the table.
dates: report rundate - visit date
clinic id 1-2 days 3 -7 days 10-30 days 30+

6 10 50 60

Thanks