Count specific entries in records in report fed by xtab query

  • Thread starter Thread starter Bill Pauley
  • Start date Start date
B

Bill Pauley

Hi All,
I have a report that is based on a crosstab query. It is a grid style report
with 31 columns for the visitdate and patients and therapists used as row
headings. What appears in the grid on the date a visit was made is the code
for the type of visit. There are four different [therapytype] codes.....O,
E, X, D. I am trying without success to count the total number of E, X and D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to "31" as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
 
Rather than trying to count the data after it has been pivoted, count it
before.

Select Sum(IIf(therapytype = "O", 1, 0) as SumO, Sum(IIf(therapytype <> "O")
as SumEXD
From qryVisitsandRateType_Crosstab;

Then use this totals query as the RecordSource for a subreport in the
mainreport's report footer.
 
Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the query for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Duane Hookom said:
Are you attempting to create 2 X 31 controls (2 per column) or only 2
controls in the report. If only two in the report, change your SQL to:

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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
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");

Then you can create a sum of TotalOs and TotalEXDs in your report footer.
--
Duane Hookom
MS Access MVP


Bill Pauley said:
Hi All,
I have a report that is based on a crosstab query. It is a grid style
report
with 31 columns for the visitdate and patients and therapists used as row
headings. What appears in the grid on the date a visit was made is the
code
for the type of visit. There are four different [therapytype] codes.....O,
E, X, D. I am trying without success to count the total number of E, X and
D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to "31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
 
Pat,
Thanks again for your help. You really got me headed
in the right direction.
Bill
Pat Hartman said:
Rather than trying to count the data after it has been pivoted, count it
before.

Select Sum(IIf(therapytype = "O", 1, 0) as SumO, Sum(IIf(therapytype <> "O")
as SumEXD
From qryVisitsandRateType_Crosstab;

Then use this totals query as the RecordSource for a subreport in the
mainreport's report footer.

Bill Pauley said:
Hi All,
I have a report that is based on a crosstab query. It is a grid style report
with 31 columns for the visitdate and patients and therapists used as row
headings. What appears in the grid on the date a visit was made is the code
for the type of visit. There are four different [therapytype] codes.....O,
E, X, D. I am trying without success to count the total number of E, X
Click to expand...
and
D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to
Click to expand...
"31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
 
I don't understand your additional comment. Share your sql view that creates
the error and the exact error message.

--
Duane Hookom
MS Access MVP
--

Bill said:
Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the query for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Duane Hookom said:
Are you attempting to create 2 X 31 controls (2 per column) or only 2
controls in the report. If only two in the report, change your SQL to:

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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
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");

Then you can create a sum of TotalOs and TotalEXDs in your report footer.
--
Duane Hookom
MS Access MVP


Bill Pauley said:
Hi All,
I have a report that is based on a crosstab query. It is a grid style
report
with 31 columns for the visitdate and patients and therapists used as row
headings. What appears in the grid on the date a visit was made is the
code
for the type of visit. There are four different [therapytype] codes.....O,
E, X, D. I am trying without success to count the total number of E, X and
D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to "31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
This part is clearly wrong:
Field: Expr1: 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")
The "01",... belong in the Column Headings property, not anywhere in the
query grid.

Can't you post your full SQL view?

--
Duane Hookom
MS Access MVP


Bill said:
Hi Duane,
In design view of the query behind the crosstab, I adjusted the 2 halves
of
the query view to be more easily read. When I was exiting the query, a
save
dialog popped up and I attempted to save the query. When I clicked OK to
save, a second dialog popped up stating:

"You must enter Group By in the Total row for a field that has a Column
Heading in the Crosstab row.

The values derived from the field or expression that you designate as the
Column Heading are used to group data in the crosstab query."
It will not allow me to save the query.
The following is in the top line of the last column of the query.

Field: Expr1: 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")

It is the only column in the query with
Crosstab: Column Heading
And
Total: Expression

When I set the to
Total: Group By (as I think the popup dialog is demanding)
In that same column, I get a row for each of the "O, E, X, D" entries.
Undesired.
So I set it back and exited without saving. All works well, but I did not
know if I should be concerned over this.
Here is the code copied from your post to the query and now back again.
Have
I done something wrong in the copy and paste process?

Thanks again for all of you help.
Bill

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]<>"O")) AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
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");

Duane Hookom said:
I don't understand your additional comment. Share your sql view that creates
the error and the exact error message.

--
Duane Hookom
MS Access MVP
--

Bill said:
Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the query for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Are you attempting to create 2 X 31 controls (2 per column) or only 2
controls in the report. If only two in the report, change your SQL to:

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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
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");

Then you can create a sum of TotalOs and TotalEXDs in your report footer.
--
Duane Hookom
MS Access MVP


Hi All,
I have a report that is based on a crosstab query. It is a grid
style
report
with 31 columns for the visitdate and patients and therapists used
as
row
headings. What appears in the grid on the date a visit was made is the
code
for the type of visit. There are four different [therapytype]
codes.....O,
E, X, D. I am trying without success to count the total number of E, X
and
D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to
"31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,
In design view of the query behind the crosstab, I adjusted the 2 halves of
the query view to be more easily read. When I was exiting the query, a save
dialog popped up and I attempted to save the query. When I clicked OK to
save, a second dialog popped up stating:

"You must enter Group By in the Total row for a field that has a Column
Heading in the Crosstab row.

The values derived from the field or expression that you designate as the
Column Heading are used to group data in the crosstab query."
It will not allow me to save the query.
The following is in the top line of the last column of the query.

Field: Expr1: 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")

It is the only column in the query with
Crosstab: Column Heading
And
Total: Expression

When I set the to
Total: Group By (as I think the popup dialog is demanding)
In that same column, I get a row for each of the "O, E, X, D" entries.
Undesired.
So I set it back and exited without saving. All works well, but I did not
know if I should be concerned over this.
Here is the code copied from your post to the query and now back again. Have
I done something wrong in the copy and paste process?

Thanks again for all of you help.
Bill

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]<>"O")) AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
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");

Duane Hookom said:
I don't understand your additional comment. Share your sql view that creates
the error and the exact error message.

--
Duane Hookom
MS Access MVP
--

Bill said:
Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the query for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Duane Hookom said:
Are you attempting to create 2 X 31 controls (2 per column) or only 2
controls in the report. If only two in the report, change your SQL to:

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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
PIVOT Format([VisitDate],"dd") In
Click to expand...
("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");

Then you can create a sum of TotalOs and TotalEXDs in your report footer.
--
Duane Hookom
MS Access MVP


Hi All,
I have a report that is based on a crosstab query. It is a grid style
report
with 31 columns for the visitdate and patients and therapists used as row
headings. What appears in the grid on the date a visit was made is the
code
for the type of visit. There are four different [therapytype] codes.....O,
E, X, D. I am trying without success to count the total number of E,
Click to expand...
X
and
D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to "31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
I find it difficult to believe that since the "01","02",... is clearly not
in the "SELECT " part of the sql and it clearly follows the "PIVOT " that
Access would be attempting to place this in the Field line.

I expect it might be some form of corruption. I would try open a new blank
query and paste the sql into the sql view. See if the problem persists.

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]<>"O")) AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
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");

--
Duane Hookom
MS Access MVP
--

Bill said:
Hi Duane,
I am not doing a good job communicating. Sorry.
The "01",... that you referred to is in the Field line.
I believe it was a result of the SQL.
As for posting the SQL query view, it is at the bottom of
my last post.

Thanks again for all of your assistance.
Bill
Duane Hookom said:
This part is clearly wrong:
Field: Expr1: 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")
The "01",... belong in the Column Headings property, not anywhere in the
query grid.

Can't you post your full SQL view?

--
Duane Hookom
MS Access MVP


Bill said:
Hi Duane,
In design view of the query behind the crosstab, I adjusted the 2
halves
of
the query view to be more easily read. When I was exiting the query, a
save
dialog popped up and I attempted to save the query. When I clicked OK
to
save, a second dialog popped up stating:

"You must enter Group By in the Total row for a field that has a Column
Heading in the Crosstab row.

The values derived from the field or expression that you designate as the
Column Heading are used to group data in the crosstab query."
It will not allow me to save the query.
The following is in the top line of the last column of the query.

Field: Expr1: 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")

It is the only column in the query with
Crosstab: Column Heading
And
Total: Expression

When I set the to
Total: Group By (as I think the popup dialog is demanding)
In that same column, I get a row for each of the "O, E, X, D" entries.
Undesired.
So I set it back and exited without saving. All works well, but I did not
know if I should be concerned over this.
Here is the code copied from your post to the query and now back again.
Have
I done something wrong in the copy and paste process?

Thanks again for all of you help.
Bill

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]<>"O"))
AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
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");

I don't understand your additional comment. Share your sql view that
creates
the error and the exact error message.

--
Duane Hookom
MS Access MVP
--

Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the query
for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get
a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Are you attempting to create 2 X 31 controls (2 per column) or only 2
controls in the report. If only two in the report, change your SQL to:

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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
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");

Then you can create a sum of TotalOs and TotalEXDs in your report
footer.
--
Duane Hookom
MS Access MVP


Hi All,
I have a report that is based on a crosstab query. It is a grid
style
report
with 31 columns for the visitdate and patients and therapists
used
as
row
headings. What appears in the grid on the date a visit was made
is
the
code
for the type of visit. There are four different [therapytype]
codes.....O,
E, X, D. I am trying without success to count the total number of E,
X
and
D
entries in one control in the report and the total O entries in
another
control in the report. The report column controls are set to "01" to
"31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,
I am not doing a good job communicating. Sorry.
The "01",... that you referred to is in the Field line.
I believe it was a result of the SQL.
As for posting the SQL query view, it is at the bottom of
my last post.

Thanks again for all of your assistance.
Bill
Duane Hookom said:
This part is clearly wrong:
Field: Expr1: 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")
The "01",... belong in the Column Headings property, not anywhere in the
query grid.

Can't you post your full SQL view?

--
Duane Hookom
MS Access MVP


Bill said:
Hi Duane,
In design view of the query behind the crosstab, I adjusted the 2 halves
of
the query view to be more easily read. When I was exiting the query, a
save
dialog popped up and I attempted to save the query. When I clicked OK to
save, a second dialog popped up stating:

"You must enter Group By in the Total row for a field that has a Column
Heading in the Crosstab row.

The values derived from the field or expression that you designate as the
Column Heading are used to group data in the crosstab query."
It will not allow me to save the query.
The following is in the top line of the last column of the query.

Field: Expr1: 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")

It is the only column in the query with
Crosstab: Column Heading
And
Total: Expression

When I set the to
Total: Group By (as I think the popup dialog is demanding)
In that same column, I get a row for each of the "O, E, X, D" entries.
Undesired.
So I set it back and exited without saving. All works well, but I did not
know if I should be concerned over this.
Here is the code copied from your post to the query and now back again.
Have
I done something wrong in the copy and paste process?

Thanks again for all of you help.
Bill

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]<>"O")) AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
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");

Duane Hookom said:
I don't understand your additional comment. Share your sql view that creates
the error and the exact error message.

--
Duane Hookom
MS Access MVP
--

Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the
Click to expand...
query
for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Are you attempting to create 2 X 31 controls (2 per column) or only 2
controls in the report. If only two in the report, change your SQL to:

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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
PIVOT Format([VisitDate],"dd") In
Click to expand...
("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");

Then you can create a sum of TotalOs and TotalEXDs in your report footer.
--
Duane Hookom
MS Access MVP


Hi All,
I have a report that is based on a crosstab query. It is a grid
style
report
with 31 columns for the visitdate and patients and therapists used
as
row
headings. What appears in the grid on the date a visit was made is the
code
for the type of visit. There are four different [therapytype]
codes.....O,
E, X, D. I am trying without success to count the total number of
Click to expand...
E,
X
and
D
entries in one control in the report and the total O entries in another
control in the report. The report column controls are set to "01" to
"31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Duane,
I tried a new query, same result, can't save the query.
I am grateful of your time and expertise in getting me this far.
I will attempt to import the tables into a new datebase this
weekend and communicate back my results after completing.
Thank you
Bill

Duane Hookom said:
I find it difficult to believe that since the "01","02",... is clearly not
in the "SELECT " part of the sql and it clearly follows the "PIVOT " that
Access would be attempting to place this in the Field line.

I expect it might be some form of corruption. I would try open a new blank
query and paste the sql into the sql view. See if the problem persists.

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]<>"O")) AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName, qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
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");

--
Duane Hookom
MS Access MVP
--

Bill said:
Hi Duane,
I am not doing a good job communicating. Sorry.
The "01",... that you referred to is in the Field line.
I believe it was a result of the SQL.
As for posting the SQL query view, it is at the bottom of
my last post.

Thanks again for all of your assistance.
Bill
Duane Hookom said:
This part is clearly wrong:
Field: Expr1: Format([VisitDate],"dd") In
Click to expand...
("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")
The "01",... belong in the Column Headings property, not anywhere in the
query grid.

Can't you post your full SQL view?

--
Duane Hookom
MS Access MVP


Hi Duane,
In design view of the query behind the crosstab, I adjusted the 2
halves
of
the query view to be more easily read. When I was exiting the query, a
save
dialog popped up and I attempted to save the query. When I clicked OK
to
save, a second dialog popped up stating:

"You must enter Group By in the Total row for a field that has a Column
Heading in the Crosstab row.

The values derived from the field or expression that you designate as the
Column Heading are used to group data in the crosstab query."
It will not allow me to save the query.
The following is in the top line of the last column of the query.

Field: Expr1: Format([VisitDate],"dd") In
Click to expand...
("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")

It is the only column in the query with
Crosstab: Column Heading
And
Total: Expression

When I set the to
Total: Group By (as I think the popup dialog is demanding)
In that same column, I get a row for each of the "O, E, X, D" entries.
Undesired.
So I set it back and exited without saving. All works well, but I did not
know if I should be concerned over this.
Here is the code copied from your post to the query and now back again.
Have
I done something wrong in the copy and paste process?

Thanks again for all of you help.
Bill

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]<>"O"))
AS
TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName,
qryVisitsandRateType.CoFullName, qryVisitsandRateType.RateAmt,
qryVisitsandRateType.CoFullName
PIVOT Format([VisitDate],"dd") In
Click to expand...
("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");

I don't understand your additional comment. Share your sql view that
creates
the error and the exact error message.

--
Duane Hookom
MS Access MVP
--

Yes Sir,
Only 2 controls in the report.
THANK YOU!!!!!!!!!!!
The billing report is up and running. I was also able to use the query
for
the payroll process as well. I should have asked for help a month ago.
Just when you think you have a grasp on Access, one of these situations
crop up.
I do have an additional comment. When I view the query alone, I get
a
message:
You must enter Group By in the Total row for a field that has Column
Heading in the Crosstab row.
However when I do that for the 1-31 col head, I get a row for every
O, E, X, D in the query. Not desired. So I changed it back.
Any concern over this situation?
Thanks again.
Bill
Are you attempting to create 2 X 31 controls (2 per column) or
Click to expand...
only
2
controls in the report. If only two in the report, change your
Click to expand...
SQL
to:
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]<>"O")) as TotalEXDs
FROM qryVisitsandRateType
GROUP BY qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
qryVisitsandRateType.RateAmt, qryVisitsandRateType.CoFullName
PIVOT Format([VisitDate],"dd") In
Click to expand...
("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");

Then you can create a sum of TotalOs and TotalEXDs in your report
footer.
--
Duane Hookom
MS Access MVP


Hi All,
I have a report that is based on a crosstab query. It is a grid
style
report
with 31 columns for the visitdate and patients and therapists
used
as
row
headings. What appears in the grid on the date a visit was made
is
the
code
for the type of visit. There are four different [therapytype]
codes.....O,
E, X, D. I am trying without success to count the total number
Click to expand...
of
E,
X
and
D
entries in one control in the report and the total O entries in
another
control in the report. The report column controls are set to
Click to expand...
"01"
to
"31"
as
the control source.
The query behind the report is qryVisitsandRateType_Crosstab.
Any assistance will be greatly appreciated.
Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top