Count specific entries in records in report fed by xtab query

[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

 

[Pat Hartman]

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.

 

[Duane Hookom]

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.

 

[Bill]

Pat,
Again, thanks for the input. I had to change a few things....
I gave the wrong field, should have been RateType, not TherapyType.
Sorry
I added two ) to your suggestion, to even them out
I changed the sum to count
I changed the query for the subreport to the one that supplies the crosstab

The following gives me the correct numbers in a subreport.
Placed in the CoFullName footer.

SELECT Count(IIf([Ratetype]="O",1)) AS SumO,
Count(IIf([Ratetype]<>"O","")) AS SumEXD
FROM qryVisitsandRateType;

I am close to getting the correct numbers.
I am getting the report total instead of the company total that I need.
I need help getting the subreport to link to the txtCoFullName
control on the Crosstab Report.
The control source is CoFullName.
How do I add that field to the query for the subreport
to link that field in both queries?
CoFullName appears in both queries. When I try to add the field to the
subreport query, I get the following error:

You tried to execute a query that does not include the specified expression
'CoFullName' as part of an aggregate function.

Thanks again for the assistance.
Bill

Here is the SQL view of the query for the crosstab.

TRANSFORM First(qryVisitsandRateType.RateType) AS FirstOfRateType
SELECT qryVisitsandRateType.TherFullName,
qryVisitsandRateType.PtFullName, qryVisitsandRateType.CoFullName,
Count(qryVisitsandRateType.RateType) AS Total
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");

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.

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

 

[Bill]

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

 

[Bill]

Pat,
Thanks again for your help. You really got me headed
in the right direction.
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.

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

 

[Duane Hookom]

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

 

[Duane Hookom]

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

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

 

[Bill]

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

 

[Duane Hookom]

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
--

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

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

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

 

[Bill]

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

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

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

 

[Bill]

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

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
--

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

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


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