IIF Function to create a crosstab analysis (1 Viewer)

[jeannier1975]

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.

 

[June7]

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.

 

[jeannier1975]

here some sample data

 

[June7]

What field has department?

Percentage calcs are often most easily accomplished with report design. Use report Sorting & Grouping features with aggregate calcs in textboxes.

 

[jeannier1975]

its field owner group

 

[June7]

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.

 

[jeannier1975]

user defined range

 

[June7]

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.

 

[jeannier1975]

question how would i do that with dcount function instead?