Multiple Counts In Select Query
in [Help]
|
Prev: Capture field of certain records to display in code as comma separated text
Next: Creating an artificial rownum value in a query
From: bezz on 9 Aug 2010 14:37 I have several tables which have imported data for a back end datafile, these can be simplified as: Syscode NumOfD 101 34647 101 43567 101 12354 102 12345 103 4356 104 49907 104 21455 104 54678 193 34567 209 54789 209 22234 209 12345 209 43567 240 456666 Syscode NumOfX 101 344647 101 743567 101 162354 102 3212345 103 654356 104 2349907 104 4521455 104 3254678 193 1134567 209 3454789 209 4522234 209 3212345 209 6543567 240 2345664 I can set up a select query using a base table which has the system filtered for a particular event, with a link from the "system field" to the syscode field, and then using Group By on Syscode, and Count on NumOfD to return : 101 3 102 1 103 1 104 3 193 1 209 4 240 1 I could then use another query to do the same for Syscode and NumOfX to count the number of occurrences of NumOofX against the same system number. What I want to do eventually is have System, NumOfD, NumOfX, NumOfY, NumOfS etc. and to put this data into a table to produce a glidepath as the numbers decrease. Is there a way to do this in one query?, or do you have to write a query for each count peration, and use and append query or similar to put the data into a table.... Thanks J -- --------------------------------- --- -- - Posted with NewsLeecher v3.9 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- -
From: Salad on 9 Aug 2010 15:51
bezz wrote: > I have several tables which have imported data for a back end datafile, these can be simplified as: > > > Syscode NumOfD > 101 34647 > 101 43567 > 101 12354 > 102 12345 > 103 4356 > 104 49907 > 104 21455 > 104 54678 > 193 34567 > 209 54789 > 209 22234 > 209 12345 > 209 43567 > 240 456666 > > Syscode NumOfX > 101 344647 > 101 743567 > 101 162354 > 102 3212345 > 103 654356 > 104 2349907 > 104 4521455 > 104 3254678 > 193 1134567 > 209 3454789 > 209 4522234 > 209 3212345 > 209 6543567 > 240 2345664 > > I can set up a select query using a base table which has the system filtered for a particular event, with a link from the "system > field" to the syscode field, and then using Group By on Syscode, and Count on NumOfD to return : > > 101 3 > 102 1 > 103 1 > 104 3 > 193 1 > 209 4 > 240 1 > > I could then use another query to do the same for Syscode and NumOfX to count the number of occurrences of NumOofX against the same > system number. > > What I want to do eventually is have System, NumOfD, NumOfX, NumOfY, NumOfS etc. and to put this data into a table to produce a > glidepath as the numbers decrease. > > Is there a way to do this in one query?, or do you have to write a query for each count peration, and use and append query or similar to > put the data into a table.... > > Thanks J > > I think I understand your issue. Then again maybe not. So I'll just toss some mud at the wall. It all records were in 1 table, and you had a field that would denote a D,X,Y,or S then you could create a calculated column in your query. Ex: StatusDCnt : IIF(Status = "D",1,0) StatusXCnt : IIF(Status = "X",1,0) The "CntOfDs" is the column name separated by a colon and followed by an expression. Since this is a groupby totals SQL you would select the word SUM in the totals row for those columns. Since you have multiple tables the data is coming from I think you might need 2 queries. The first query would be a Union query. Select Syscode, "D" AS Status From TableD UNION ALL Select Syscode, "X" AS Status From TableX UNION ALL etc Save this is query as SysCodeUnion Now you could a create/run the totals query. Ex: SELECT SysCodeUnion.ID, Sum(IIf([Status]="D",1,0)) AS StatusDCnt, Sum(IIf([Status]="X",1,0)) AS StatusXCnt FROM SysCodeUnion GROUP BY SysCodeUnion.ID ORDER BY SysCodeUnion.ID; If you enter StatusDCnt : IIF(Status = "D",1,0) in the query builder it is converted to Sum(IIf([Status]="D",1,0)) AS StatusDCnt if you View/SQL |