IIF Function to create a crosstab analysis

jeannier1975

Registered User.
Local time
Today, 00:21
Joined
May 17, 2018
Messages
48
I have a table that is deleted and new data is uploaded to it on a weekly basis i need to create a query that takes criteria from the table and forms a denominator, and then another calculation for the denominator and then gets the percentages from those numbers for each departments compliance metrics.
query 1 numerator:
SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReport.Status) Like "*COMP") AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) AND ((MaximoReport.ActualLaborHours)<>"00:00") AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])));
and
query 2
SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReport.Status)<>"CAN") AND ((MaximoReport.[Target Start])>=DateAdd("h",-11.8,[Enter the start date]) And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])));

I divide thew two queries aND I GET THE PERCENTAGE BUT I NEED ALSO TO HAVE IT IN A CROSSTAB QUERY BREAK IT INTO DEPARTMENT PERCENTAGES.
 
Deleting and creating tables alters db design. Better for table to be permanent and just delete/insert records.

Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.
 
What field has department?

Percentage calcs are often most easily accomplished with report design. Use report Sorting & Grouping features with aggregate calcs in textboxes.
 
You mean [Assigned Owner Group]? This field is empty for a lot of records.

Do you want data summarized by year or month or by user defined range?

Should eliminate spaces from naming convention. And ALLCAPS is just uncomfortable to read as is alllowercase.
 
I avoid dynamic parameterized queries. I prefer to apply filter to form or report using filter criteria constructed with VBA that references controls on form for user inputs. Following OpenReport shows static criteria but VBA could dynamically construct. http://allenbrowne.com/ser-62.html

DoCmd.OpenReport "Pct", acViewPreview,, "WorkType In ('PMINS','PMOR','PMPDM','PMREG','PMRT') AND [Status] Like '*COMP' AND [Target Start]>=DateAdd('h',-1,#12/12/2018#) And [Target Start]<DateAdd('h',23,#12/12/2018#) AND ActualLaborHours<>'00:00' AND ActualStartDate>=DateAdd('h',-11.8,#12/12/2018#) And ActualStartDate<DateAdd('h',23,#12/12/2018#)"

Consider building a report and use its Sorting & Grouping features with aggregate calcs in textboxes. Grouping on [Assigned Owner Group] will display data vertically. Trying to CROSSTAB so groups run horizontal will get complicated. For one thing, CROSSTAB query with filter criteria requires use of Parameters clause.
 

Attachments

  • Capture.PNG
    Capture.PNG
    18.4 KB · Views: 182

Users who are viewing this thread

Back
Top Bottom