Multiple counts in a query

[Tetelestai]

I would like three counts in one query based on differing criteria. Each count is counting the same items. The difference between the items being counted is the characteristics (eg like to short).

I have created a three separate queries (with the help of the query wizard) that perform this. Can they be combined into one? Or should I be using some other function?

This is the query

SELECT DISTINCTROW tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, Count(*) AS [Total Number of Panels]
FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
GROUP BY tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, tblOrders.JobOrderID, tblFloors.FloorID;
 

the other queries have a WHERE clause

WHERE (((tblPanels.Height) Between 72 And 145.125))
 

and

WHERE (((tblPanels.Length)<48) AND ((tblPanels.Height) Between 72 And 145.125))
 

[MSeda]

you could add the last two counts to the first query by using Dcount.

=DCount("[JobID]"," myQuery"," (tblPanels.Height) Between 72 And 145.125")

[Tetelestai]

Having difficulty implementing this. I most likely have not given enough information.

Note I'm using A 2K

The original query is a summary query that is counting the number of panels in tblPanels that belongs to a floor in tblFloors which belongs to a order in tblOrders.

Here is the code with Dcount. Right now it is counting all the panels that are between that range, not seperating them per Floor or Order

SELECT DISTINCTROW tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, Count(*) AS [Total Number of Panels], Nz(DCount("[JobID]","qryPanelsSummary3","(tblPanels.Height) Between 72 And 145.125")) AS [Number of Panels]
FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
GROUP BY tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, tblOrders.JobOrderID, tblFloors.FloorID;
 

Am I using dcount properly?

[Tetelestai]

I will also give table structure

tblOrders
JobOrderID
JobID
JobSubID
Model

tblFloors
FloorID
JobOrderID
Floor

tblPanels
PanelID
FloorID
Panel
Length
Height

[MSeda]

since you gave info on all your tables I decided to build a test on your actual table structure. the SQL i ended up with looked like this

SELECT DISTINCTROW tblOrders.JobOrderID, tblOrders.JobID, tblOrders.JobSubID, tblFloors.FloorID, Nz(DCount("[PanelID]","[tblPanels]","[FloorID] = '" & [tblfloors].[FloorID] & "'")) AS ttlno, Nz(DCount("[PanelID]","[tblPanels]","[Length]<10 AND [FloorID] = '" & [tblfloors].[FloorID] & "'")) AS ttlShort, Nz(DCount("[PanelID]","[tblPanels]","[Length]>=10 AND [FloorID] = '" & [tblfloors].[FloorID] & "'")) AS ttlLong
 
FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
 
GROUP BY tblOrders.JobOrderID, tblOrders.JobID, tblOrders.JobSubID, tblFloors.FloorID, Nz(DCount("[PanelID]","[tblPanels]","[FloorID] = '" & [tblfloors].[FloorID] & "'")), Nz(DCount("[PanelID]","[tblPanels]","[Length]<10 AND [FloorID] = '" & [tblfloors].[FloorID] & "'")), Nz(DCount("[PanelID]","[tblPanels]","[Length]>=10 AND [FloorID] = '" & [tblfloors].[FloorID] & "'"));
 

I used dcount for all of the counting to simplify things I also based mine on simpler criteria but you should easily be able to swap in your own. the syntax in domain aggregates can be tricky, you may want to review a thread that explains that topic better.
It looked like you were basicaly counting Panels per Floor so thats how I set up my test query if you want to count panels per job the sql would be slightly different.
See how you can make this work for you and let us know.
Megan

[Tetelestai]

Thanks Megan works great.

The ' character created a datatype mismatch on mine.

Here is my final code (for posterity)

SELECT DISTINCTROW tblOrders.JobSubID, tblOrders.Model, tblOrders.JobID, tblFloors.Floor, Nz(DCount("[PanelID]","[tblPanels]","[Length]<48 AND [HEIGHT]>72 AND [HEIGHT]<145.125 AND [FloorID] = " & [tblfloors].[FloorID] & "")) AS [Short Panels], IIf([Line Panels]=0,"-",Format([Short Panels]/[Line Panels],"0%")) AS Percentage, Nz(DCount("[PanelID]","[tblPanels]","[HEIGHT]>72 AND [HEIGHT]<145.125 AND [FloorID] = " & [tblfloors].[FloorID] & "")) AS [Line Panels], Nz(DCount("[PanelID]","[tblPanels]","[FloorID] = " & [tblfloors].[FloorID] & "")) AS [Total Panels]
FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
GROUP BY tblOrders.JobSubID, tblOrders.Model, tblOrders.JobID, tblFloors.Floor, tblOrders.JobOrderID, tblFloors.FloorID;
 

If I need it by Job I will give it a go before I ask, and have a working example now to play with.

[Tetelestai]

In the above code ^^

Is it advisable not to put formating in the expression, but instead place formating in the text box control where it is being displayed?

[MSeda]

Glad it worked out for you

[NeoPa]

Another concept you could use (more efficient - but that will only be important for fairly large numbers of records) is to have :

SELECT Count(*) AS NoOfPanels, Sum(IIf(tblPanels.Height Between 72 And 145.125,1,0)) AS NoOfHighPanels,...

Obviously change the names and criteria to fit your needs.