14 Day Crosstab problem

B

Bill

Hi All:
Access 2000, Win98SE.
Crosstab 14 day report problem.
I have a 31 day report working as desired.
(Thanks to Duane Hookom)
I am attempting to convert the sql into a 14 day period.
Everything is good, except I am getting a separate line
in the report for every visit made to a patient (E X D).
I need to display visits to a specific patient, by the specific therapist,
from a specific company, on the same line. This was being achieved
in the 31 day report. I am posting both SQL views.
Thanks for your assistance.
Bill

SQL VIEW OF 14 DAY:

TRANSFORM First(qryVisitsandRateType2Wk.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType2Wk.TherFullName,
qryVisitsandRateType2Wk.PtFullName, qryVisitsandRateType2Wk.CoFullName,
Count(qryVisitsandRateType2Wk.RateType) AS Total, Sum(Abs([RateType]="O"))
AS TotalOs, Sum(Abs([RateType]="CS")) AS TotalCS, Sum(Abs([RateType]<>"O"
And [RateType]<>"CS")) AS TotalEXDs
FROM qryVisitsandRateType2Wk
GROUP BY qryVisitsandRateType2Wk.TherFullName,
qryVisitsandRateType2Wk.PtFullName, qryVisitsandRateType2Wk.CoFullName,
qryVisitsandRateType2Wk.RateAmt, qryVisitsandRateType2Wk.VisitDate
PIVOT "Day" & DateDiff("d",[VisitDate],[EndDate]) In
("Day0","Day1","Day2","Day3","Day4","Day5","Day6","Day7","Day8","Day9","Day1
0","Day11","Day12","Day13");

SQL VIEW OF 31 DAY:
TRANSFORM First(qryVisitsandRateType.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, Count(qryVisitsandRateType.RateType) AS
Total, Sum(Abs([RateType]="O")) AS TotalOs, Sum(Abs([RateType]="CS")) AS
TotalCS, Sum(Abs([RateType]<>"O" And [RateType]<>"CS")) AS TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31")
PIVOT Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31");

PS The original SQL from Duane did not have two "In" lines, but I could not
get the query to save without changing the column heading from "expression"
to "group by".
 
D

Duane Hookom

You need to remove some fields/columns from the GROUP BY:

SQL VIEW OF 14 DAY:
qryVisitsandRateType2Wk.RateAmt, qryVisitsandRateType2Wk.VisitDate

SQL VIEW OF 31 DAY:
qryVisitsandRateType.RateAmt,Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31")

--
Duane Hookom
MS Access MVP
--

Bill said:
Hi All:
Access 2000, Win98SE.
Crosstab 14 day report problem.
I have a 31 day report working as desired.
(Thanks to Duane Hookom)
I am attempting to convert the sql into a 14 day period.
Everything is good, except I am getting a separate line
in the report for every visit made to a patient (E X D).
I need to display visits to a specific patient, by the specific therapist,
from a specific company, on the same line. This was being achieved
in the 31 day report. I am posting both SQL views.
Thanks for your assistance.
Bill

SQL VIEW OF 14 DAY:

TRANSFORM First(qryVisitsandRateType2Wk.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType2Wk.TherFullName,
qryVisitsandRateType2Wk.PtFullName, qryVisitsandRateType2Wk.CoFullName,
Count(qryVisitsandRateType2Wk.RateType) AS Total, Sum(Abs([RateType]="O"))
AS TotalOs, Sum(Abs([RateType]="CS")) AS TotalCS, Sum(Abs([RateType]<>"O"
And [RateType]<>"CS")) AS TotalEXDs
FROM qryVisitsandRateType2Wk
GROUP BY qryVisitsandRateType2Wk.TherFullName,
qryVisitsandRateType2Wk.PtFullName, qryVisitsandRateType2Wk.CoFullName,
qryVisitsandRateType2Wk.RateAmt, qryVisitsandRateType2Wk.VisitDate
PIVOT "Day" & DateDiff("d",[VisitDate],[EndDate]) In
("Day0","Day1","Day2","Day3","Day4","Day5","Day6","Day7","Day8","Day9","Day1
0","Day11","Day12","Day13");

SQL VIEW OF 31 DAY:
TRANSFORM First(qryVisitsandRateType.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, Count(qryVisitsandRateType.RateType) AS
Total, Sum(Abs([RateType]="O")) AS TotalOs, Sum(Abs([RateType]="CS")) AS
TotalCS, Sum(Abs([RateType]<>"O" And [RateType]<>"CS")) AS TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31")
PIVOT Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31");

PS The original SQL from Duane did not have two "In" lines, but I could
not
get the query to save without changing the column heading from
"expression"
to "group by".
Click to expand...
 
B

Bill

Hi Duane,
Thanks for the solution. I did try to find the answer at google. But
couldn't work it out.
I keep wondering why the questions seem so difficult and the answers seem so
easy.
Some day it will come :)
Thanks again,
Bill

Duane Hookom said:
You need to remove some fields/columns from the GROUP BY:

SQL VIEW OF 14 DAY:
qryVisitsandRateType2Wk.RateAmt, qryVisitsandRateType2Wk.VisitDate

SQL VIEW OF 31 DAY:
qryVisitsandRateType.RateAmt,Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31")

--
Duane Hookom
MS Access MVP
--

Bill said:
Hi All:
Access 2000, Win98SE.
Crosstab 14 day report problem.
I have a 31 day report working as desired.
(Thanks to Duane Hookom)
I am attempting to convert the sql into a 14 day period.
Everything is good, except I am getting a separate line
in the report for every visit made to a patient (E X D).
I need to display visits to a specific patient, by the specific therapist,
from a specific company, on the same line. This was being achieved
in the 31 day report. I am posting both SQL views.
Thanks for your assistance.
Bill

SQL VIEW OF 14 DAY:

TRANSFORM First(qryVisitsandRateType2Wk.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType2Wk.TherFullName,
qryVisitsandRateType2Wk.PtFullName, qryVisitsandRateType2Wk.CoFullName,
Count(qryVisitsandRateType2Wk.RateType) AS Total, Sum(Abs([RateType]="O"))
AS TotalOs, Sum(Abs([RateType]="CS")) AS TotalCS,
Click to expand...
Sum(Abs( said:
And [RateType]<>"CS")) AS TotalEXDs
FROM qryVisitsandRateType2Wk
GROUP BY qryVisitsandRateType2Wk.TherFullName,
qryVisitsandRateType2Wk.PtFullName, qryVisitsandRateType2Wk.CoFullName,
qryVisitsandRateType2Wk.RateAmt, qryVisitsandRateType2Wk.VisitDate
PIVOT "Day" & DateDiff("d",[VisitDate],[EndDate]) In
("Day0","Day1","Day2","Day3","Day4","Day5","Day6","Day7","Day8","Day9","Day1
0","Day11","Day12","Day13");

SQL VIEW OF 31 DAY:
TRANSFORM First(qryVisitsandRateType.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, Count(qryVisitsandRateType.RateType) AS
Total, Sum(Abs([RateType]="O")) AS TotalOs, Sum(Abs([RateType]="CS")) AS
TotalCS, Sum(Abs([RateType]<>"O" And [RateType]<>"CS")) AS TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31")
PIVOT Format([VisitDate],"dd") In
("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15",
"16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","
31");

PS The original SQL from Duane did not have two "In" lines, but I could
not
get the query to save without changing the column heading from
"expression"
to "group by".
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Big Problem, can't get sheet to calc correctly 3

Top