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
------------------- ----- ---- -- -

bezz wrote:

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