By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Multiple counts in a query

P: 34
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
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, Count(*) AS [Total Number of Panels]
  2. FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
  3. GROUP BY tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, tblOrders.JobOrderID, tblFloors.FloorID;
  4.  
the other queries have a WHERE clause
Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblPanels.Height) Between 72 And 145.125))
  2.  
and
Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblPanels.Length)<48) AND ((tblPanels.Height) Between 72 And 145.125))
  2.  
Feb 7 '07 #1
Share this Question
Share on Google+
8 Replies


MSeda
Expert 100+
P: 159
you could add the last two counts to the first query by using Dcount.

=DCount("[JobID]"," myQuery"," (tblPanels.Height) Between 72 And 145.125")
Feb 8 '07 #2

P: 34
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
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
  3. GROUP BY tblOrders.JobID, tblOrders.JobSubID, tblFloors.Floor, tblOrders.JobOrderID, tblFloors.FloorID;
  4.  
Am I using dcount properly?
Feb 8 '07 #3

P: 34
I will also give table structure

tblOrders

JobOrderID
JobID
JobSubID
Model

tblFloors
FloorID
JobOrderID
Floor

tblPanels
PanelID
FloorID
Panel
Length
Height
Feb 8 '07 #4

MSeda
Expert 100+
P: 159
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
  3. FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
  4.  
  5. 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] & "'"));
  6.  
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
Feb 8 '07 #5

P: 34
Thanks Megan works great.

The ' character created a datatype mismatch on mine.

Here is my final code (for posterity)
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM (tblOrders INNER JOIN tblFloors ON tblOrders.JobOrderID = tblFloors.JobOrderID) INNER JOIN tblPanels ON tblFloors.FloorID = tblPanels.FloorID
  3. GROUP BY tblOrders.JobSubID, tblOrders.Model, tblOrders.JobID, tblFloors.Floor, tblOrders.JobOrderID, tblFloors.FloorID;
  4.  
If I need it by Job I will give it a go before I ask, and have a working example now to play with.
Feb 8 '07 #6

P: 34
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?
Feb 8 '07 #7

MSeda
Expert 100+
P: 159
Glad it worked out for you
Feb 8 '07 #8

NeoPa
Expert Mod 15k+
P: 31,494
Another concept you could use (more efficient - but that will only be important for fairly large numbers of records) is to have :
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 10 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.