Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible to Pivot Quarterly Grouping? 1

Status
Not open for further replies.
cfuser10

cfuser10

Technical User
May 13, 2010
29
0
0
US
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.


 
Upvote 0 Downvote
Apparently you didn't understand
dhookom said:
Please provide your original table structure with field names and data types as well as some sample records.
Click to expand...

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

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Since you stated "The table is large"
Start by creating the union query like:
Code: 
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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")

 
Upvote 0 Downvote
I would get the counts first in a union query like:
Code: 
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:
Code: 
 [Forms]![frmEnterDates]![BeginDate]   Date/Time

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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!!!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Report often takes multiple minutes to load, lots of formatting time between pages
Replies
5
Views
209
dhookom
dhookom
iamchemist
  • Locked
  • Question
Push To WORD?
Replies
3
Views
176
iamchemist
iamchemist
EwaFAMA
  • Locked
  • Question
ACCESS - Adding image from URL to the report 1
Replies
11
Views
485
EwaFAMA
EwaFAMA
FancyPrairie
  • Locked
  • Question
How to Format a date on a report
Replies
1
Views
131
sxschech
sxschech
GPnew
  • Locked
  • Question
Access 2016 report export to excel column order changes
Replies
2
Views
82
GPnew
GPnew

Part and Inventory Search

Sponsor

Back
Top