Count collecting in VBA
I have many queries were simply to come up with a number that is a count of records with a specific criteria, to be used later in a calculation. Because those criteria are so lengthy, and change with each variation of the dataI am collecting, I’m not sure if an IIF structure would return the results you want. Crosstab queries can get quirky when fields are not available on different data runs. And to be honest, a crosstab query is really just a type of pivot table like in Excel, but not as flexible. I want to do most (if not all) of the count-collecting in VBA using recordsets or the DCOUNT() function. Then, dumping the values into a temporary table and base the report off of that. The queries I already have built are doing pretty much what I described, but when I try to link them together for your final report, if anything had missing or null values, it causes problems. Doing the work in VBA givesme much more flexibility in handling these situations.
But I do not know how to write up the function to do that how can u do what is above?
But I do not know how to write up the function to do that how can u do what is above?
Martin Liss
Please supply a sample workbook and some examples of what the counts should be.
ASKER
i would but its over 50 and it wont let me attach the file
ASKER
The numerator query:
the denominator query
[/code]
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])));the denominator query
SELECT MaximoReport.WorkOrder, MaximoReport.WorkType, MaximoReport.Status, MaximoReport.[Target Start]
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])));[/code]
Apart from pulling from the same table, at least to me, it is unclear how these queries should be related.
You can always zip it
its over 50You repaired and compacted the database. It is now 18MB (unzipped). Thanks.
Are you missing Count from your denominator query?
If you have counts from these two queries, you can pass these counts into other queries by way of query parameters, like you did with dates.
ASKER
no i have count but i can not figure out how to combine 2 queries to divide numerator into denominator and group by department . since i ahve several queries i have to do i only want to do one query per metric.
dash3.jpg
dash3.jpg
How does this work for your nominator?
SELECT MaximoReport.Lfc_assetdept, 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])))
GROUP BY MaximoReport.Lfc_assetdept;ASKER
SELECT Count(MaximoReport.WorkOrd
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMREG","P
GROUP BY MaximoReport.Lfc_assetdept
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMREG","P
GROUP BY MaximoReport.Lfc_assetdept
With a wild guess... a single query that performs the calculation
SELECT Count([WorkOrder])/[CountofworkOrder] AS Expr1, MaximoReport.WorkType, MaximoReport.STATUS, Min(Nz(MaximoReport.[Target Start],Date())) AS StartOfStartTarget, MaximoReport.Lfc_assetdept, Query7.CountOfWorkOrder
FROM MaximoReport INNER JOIN (SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder, MaximoReport.Lfc_assetdept
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","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])))
GROUP BY MaximoReport.Lfc_assetdept) AS Query7 ON MaximoReport.Lfc_assetdept = Query7.Lfc_assetdept
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])))
GROUP BY MaximoReport.WorkType, MaximoReport.STATUS, MaximoReport.Lfc_assetdept, Query7.CountOfWorkOrder;ASKER
can you explain the query for me so if i need to do the same thing
ASKER
that not what im looking for but i like the idea behind it
ASKER
I just need to calculate the percentages of each department that has the criteria
it works with two seperate queries but I want to put them in one query. I do not know how I can combine the two queries so I can divide the numerator into the denominator and then group by departments
it works with two seperate queries but I want to put them in one query. I do not know how I can combine the two queries so I can divide the numerator into the denominator and then group by departments
If you examine the query i provided then probably you will find what you are looking for...provided that there some common ground to work on this...as i said in the other question you posted in order to help you better i need some sample results...i reckon that you have run my query and you have found you are getting wrong results..if you can pinpoint where are the differences probably the solution won't be far away...
ASKER
Did you run my query?
ASKER
aikimark I dont see your query I must missing something.
Here's the link to my comment:
https://www.experts-exchange.com/questions/29135925/Count-collecting-in-VBA.html?anchorAnswerId=42799395#a42799395
https://www.experts-exchange.com/questions/29135925/Count-collecting-in-VBA.html?anchorAnswerId=42799395#a42799395
ASKER
it doesnt give me percentages bit totals the denominator
I asked you if this would be the type of query for your numerator. If I'm off base here, I would want to make a correction before tackling the denominator.
ASKER
yes that works for numerator
SELECT MaximoReport.Lfc_assetdept, 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])))
Group BY MaximoReport.Lfc_assetdeptASKER
that works too, now how do i combine the two queries ?
ASKER
the queries will allow me to fill in the results in a report like the attached picture.
report.jpg
report.jpg
Assuming they are named queries (qryNum and qryDenom for this example), you would add them to the FROM clause as inner joins on the other record sources where the department name equals the department name in each query.
Then you can reference the count field in each of the two queries on the report or in the source query for the report, as a new column that does the division.
Then you can reference the count field in each of the two queries on the report or in the source query for the report, as a new column that does the division.
ASKER
how do i do an inner join and then how do i refrnece them if my report looks like this i have embeded a picture of my report.
ASKER
Your report is getting its data from either a query or a table. If a table, you will need to change it into a select from table as well as add the joining of the two (num & demon) queries.
If a query, just add the two (num & demon) queries.
A picture of your report doesn't tell me anything about its data source(s).
If a query, just add the two (num & demon) queries.
A picture of your report doesn't tell me anything about its data source(s).
ASKER
The data source is empty its an unbound report with no datasource because the report has many queries in the one report .
The data source is empty its an unbound report with no datasource because the report has many queries in the one reportThis is contradictory. Unless the report source property is updated in code, both of these things can not be simultaneously true.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.