Possible to Pivot Quarterly Grouping? 1

[cfuser10]

Hello,
I would like to generate a report based on quarterly percentages for reviewed criteria. The report output needs to look like this:

#Records
Criteria Reviewed Month1 Month2 Month3 QTotal
Field1 # % % % %
Field2 # % % % %
etc...

I can get the groupings to work with the month running down in rows, but I want them to appear in columns as shown. The dates are in one field. I can't do a crosstab query because the rows are separate fields. Not sure how to get my data pivoted around so it appears as I'd like it to.

Any suggestions would be appreciated. Thank you.

 

[dhookom]

You have somewhat described where you want to end up but we have no idea where you are. Please provide your original table structure with field names and data types as well as some sample records.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

My report is figuring the percent of compliance in a selected department based upon certain criteria. The user chooses the date range. The dates are in one field called ReviewDate. I've generalized the Criteria fields here. In the table they have more meaningful names. The criteria are in separate fields in the record.

I have the report grouping the data by month.

#records
Month reviewed Criteria1 Criteria2 Criteria3 etc.
Aug 12 83% 33% 100%
Sept 11 45% 54% 50%
Oct 10 80% 40% 100%

QTotal 33 70% 42% 85%


The formula in each of the Criteria cells is:
=Format((IIf(Sum(IIf([Criteria]<>"NA",1,0))=0,1,Sum(IIf([Criteria]="yes",1,0))/Sum(IIf([Criteria]<>"NA",1,0)))),"Percent")

What I would like to do is flip the report so the Month is along the top as column headers and the field names are down the side as row headers.

 

[dhookom]

Apparently you didn't understand

Please provide your original table structure with field names and data types as well as some sample records.

I think you will first need a normalizing union query but I dan't tell.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

The table is large and contains confidential information. Below are some of the fields that I need to work with:

Dept - text field
ReviewDate - date field
Field1 - text field (contains YES, NO, NA)
Field2 - text field (contains YES, NO, NA)
Field3 - text field (contains YES, NO, NA)
and so on for the other critieria fields.

The report is grouped by department. The user chooses the month parameters for the quarter. Then on the report I count the number of records for each month (#reviewed). The formula above then figures the percentage of Yes answers in that particular field - indicating compliance.

Hope this description helps a little bit more.

 

[dhookom]

Since you stated "The table is large"
Start by creating the union query like:

SELECT Dept, Month(ReviewDate) as Mth, Year(ReviewDate) as Yr,
1 as Criteria, Field1 as YN
FROM [Large]
WHERE Nz(Field1 ,"NA") <> "NA"
UNION ALL
SELECT Dept, Month(ReviewDate), Year(ReviewDate),2, Field2
FROM [Large]
WHERE Nz(Field2 ,"NA") <> "NA"
UNION ALL
SELECT Dept, Month(ReviewDate), Year(ReviewDate),3, Field3
FROM [Large]
WHERE Nz(Field3 ,"NA") <> "NA"
-- etc --
SELECT Dept, Month(ReviewDate), Year(ReviewDate),X, FieldX
FROM [Large]
WHERE Nz(FieldX ,"NA") <> "NA"

You can then build a crosstab based on the union query.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

Duane,
Thank you for the help with the Union Query. That worked great to normalize the data. I am now working on the Crosstab Query. I have a parameter in my union query asking for the begin and end dates. When I try to run the crosstab query on the union query, I get an error that it does not recognize the parameter asking for the date.

Thank you for any suggestions.

 

[dhookom]

I don't ever use parameter prompts. I always use controls on forms for user interaction. However, either way, you must declare the data types of all parameters in the Query->Parameters.

If you can't figure this out, please share some SQL views.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

The report I will create from the query will pop up a form asking the user to enter the department and the begin and end dates for the report.

Here is the SQL from the Union Query. I've only included two of the 6 fields that are involved to give you an idea of the query:

SELECT tblEmp.LName, tblEmp.FName, tblHistory.CurDept, [DeptName] & "/" & [DeptLocation] AS Unit, Month(tblHistory.ReviewDate) as Mth, Year(tblHistory.ReviewDate) as Yr, "Eval" as Criteria,IIf(tblHistory!EvalStatus="COMPLETE","yes",IIf(tblHistory!EvalStatus="NA","NA","no")) AS YN
FROM (tblHistory INNER JOIN tblEmp ON tblHistory.EmpNo = tblPatients.EmpNo) INNER JOIN tblDept ON tblHistory.CurDept = tblDept.DeptNumber
WHERE (((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]) AND (([CurDept]=[forms]![frmEnterDates]![cmbDept] Or [forms]![frmEnterDates]![cmbDept] Is Null)=True))
UNION ALL
SELECT tblEmp.LName, tblEmp.FName, tblHistory.CurDept, [DeptName] & "/" & [DeptLocation] AS Unit, Month(tblHistory.ReviewDate) as Mth, Year(tblHistory.ReviewDate) as Yr,"Note",IIf(tblHistory!note="COMPLETE","yes",IIf(tblHistory!note="NA","NA","no"))
FROM (tblHistory INNER JOIN tblEmp ON tblHistory.CNo = tblEmp.CNO) INNER JOIN tblDept ON tblHistory.CurDept = tblDept.DeptNumber
WHERE (((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]) AND (([CurDept]=[forms]![frmEnterDates]![cmbDept] Or [forms]![frmEnterDates]![cmbDept] Is Null)=True))
UNION ALL
........ etc.

I don't work with Crosstab queries, so I'm having a little trouble taking this query and turning the results into a crosstab. Is it possible to calculate percentages in a crosstab query?

 

[dhookom]

What do you want to do with the employee and department fields? They don't seem to be referenced in your report.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

I don't need the employee name so I removed those fields from the query. The report paramenter filters by the department chosen for the begin/end date specified.

 

[dhookom]

How about the department field? You have done work with these fields but none of your report representations mention these.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

I would like my report to look like the following:

Department Name: (DeptName)

Criteria Month1 Month2 Month3 QTotal
Reviewed #records #records #records #records
Field1 % % % %
Field2 % % % %
...etc

The formula to figure the % is:
=Format((IIf(Sum(IIf([Criteria]<>"NA",1,0))=0,1,Sum(IIf([Criteria]="yes",1,0))/Sum(IIf([Criteria]<>"NA",1,0)))),"Percent")

 

[dhookom]

I would get the counts first in a union query like:

SELECT tblHistory.CurDept, [DeptName] & "/" & [DeptLocation] AS Unit, Month(ReviewDate) as Mth,
 Year(ReviewDate) as Yr, "Eval" as Criteria, Sum(Abs(EvalStatus ="COMPLETE")) As Complete,
 Sum(Abs(EvalStatus="NA")) As NA , Sum(1) as ALL
FROM (tblHistory INNER JOIN tblEmp ON tblHistory.EmpNo = tblPatients.EmpNo) INNER JOIN tblDept ON tblHistory.CurDept = tblDept.DeptNumber
WHERE (((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]) AND (([CurDept]=[forms]![frmEnterDates]![cmbDept] Or [forms]![frmEnterDates]![cmbDept] Is Null)=True))
GROUP BY tblHistory.CurDept, [DeptName] & "/" & [DeptLocation], Month(tblHistory.ReviewDate), 
 Year(tblHistory.ReviewDate)

UNION ALL

SELECT tblHistory.CurDept, [DeptName] & "/" & [DeptLocation], Month(ReviewDate),
 Year(ReviewDate), "Note", Sum(Abs(Note ="COMPLETE")) As Complete,
 Sum(Abs(Note="NA")) As NA , Sum(1) as ALL
FROM (tblHistory INNER JOIN tblEmp ON tblHistory.EmpNo = tblPatients.EmpNo) INNER JOIN tblDept ON tblHistory.CurDept = tblDept.DeptNumber
WHERE (((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]) AND (([CurDept]=[forms]![frmEnterDates]![cmbDept] Or [forms]![frmEnterDates]![cmbDept] Is Null)=True))
GROUP BY tblHistory.CurDept, [DeptName] & "/" & [DeptLocation], Month(tblHistory.ReviewDate), 
 Year(tblHistory.ReviewDate)

-- etc --

You can then create another query that divides the Complete/All for each record.

Then create your crosstab that has Year, Department, and Criteria as the Row Headings, Months as the Column Headings, and min([Your Percent Column]) as the value.

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

I got the Union query working, then the second query to compute the percentage. My last problem appeared when I created the Crosstab query. When I try to run the query, I get an error message that the MS Database Engine does not recognize [Forms]![frmEnterDates]![BeginDate] as a valid field name or expression. My other queries work fine.

Do you have any suggestions?

I really appreciate your help with this problem. I've learned alot about queries and how much can be done with them.

 

[cfuser10]

I added the begindate, enddate and dept parameter to the parameter properties of the crosstab query, so I get past that error, but now I get an error that there is a complex expression. The expressions are working properly in the two other queries.

 

[dhookom]

Great! Crosstab queries require you to enter the data type of the parameters in the Parameters property of the query design. For instance one of your parameters will be:

 [Forms]![frmEnterDates]![BeginDate]   Date/Time

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

Ahhh! It was the data type that I did not have correct! Got the crosstab to work!

I have just one more question. The total column does not seem to be figuring the percentage correctly. I'm not sure how it is getting the value in that field. Is that coming from my second query or something the crosstab calculates?

 

[cfuser10]

Oops, never mind. It was taking the min value of the Month column. I changed it to AVG and got the value I wanted.

Thank you so much for all your help!!!

 

[dhookom]

cfuser10,
Congratulations for keeping up with me. I kinda knew from early on exactly how this might turn out but I intentionally revealed only a step at a time.

Glad you got to your final destination

Duane
Hook'D on Access
MS Access MVP