Sorting & Counting

[cstanley]

Hello all,

I have a table (tblComponentCriticality) with the following fields:

SystemNumber
Tagnumber
compclass


The SystemNumber and Tagnumber fields make up a concatenated PK. SystemNumber is also an FK for another table, which lists all systems by number and name. The field complcass can be "Critical", "Non-Critical" or empty (not yet assessed).

What I want to try and do is make a report that lists each system, how many Tagnumber values are associated with each system in the table, how many of those are Critical, Non-Critical, and not yet assessed.

I've been fiddling with the Count expression and can get the number of tagnumber fields per system to appear, one system at a time. I can get a list of system numbers to appear, etc. What I would really like to know is how to combine all of these things together. Any suggestions? Please help!

Thanks,

Chris

 

[cstanley]

Here's the answer:

I made a query to select the individual systems in the table and count the components:

SELECT DISTINCT tblComponentCriticality.SystemNumber, tblSystemIdentification.SystemName, Count(tblComponentCriticality.Tagnumber) AS CountOfTagnumber, Count(tblComponentCriticality.compclass) AS CountOfcompclass
FROM tblSystemIdentification INNER JOIN tblComponentCriticality ON tblSystemIdentification.SystemNumber = tblComponentCriticality.SystemNumber
GROUP BY tblComponentCriticality.SystemNumber, tblSystemIdentification.SystemName;

And then created three other individual queries for each of the values (critical, non-critical and null), e.g

SELECT DISTINCT tblComponentCriticality.SystemNumber, tblSystemIdentification.SystemName, Count(tblComponentCriticality.Tagnumber) AS CountOfTagnumber, Count(tblComponentCriticality.compclass) AS CountOfcompclass
FROM tblSystemIdentification INNER JOIN tblComponentCriticality ON tblSystemIdentification.SystemNumber = tblComponentCriticality.SystemNumber
GROUP BY tblComponentCriticality.SystemNumber, tblSystemIdentification.SystemName, tblComponentCriticality.compclass
HAVING (((tblComponentCriticality.compclass)="critical"));


Then joined all the queries together:

SELECT qryCountSysComp.SystemNumber, qryCountSysComp.SystemName, qryCountSysComp.CountOfTagnumber, qryCountSysComp.CountOfcompclass, qryCountCritSysComp.CountOfTagnumber, qryCountNCritSysComp.CountOfTagnumber, qryCountNullSysComp.CountOfTagnumber
FROM ((qryCountSysComp LEFT JOIN qryCountCritSysComp ON qryCountSysComp.SystemNumber = qryCountCritSysComp.SystemNumber) LEFT JOIN qryCountNCritSysComp ON qryCountSysComp.SystemNumber = qryCountNCritSysComp.SystemNumber) LEFT JOIN qryCountNullSysComp ON qryCountSysComp.SystemNumber = qryCountNullSysComp.SystemNumber;

And it all works! Hooray for me! For once, I figured something out for myself. It's a milestone or something. However... it seems to me that the above code is somewhat inelegant. What if, for example, I had more than three selections to sort off of? Creating lots of queries would get old fast. Can anybody offer a simpler solution?

Chris

 

[dcx693]

What matters most in the end is the result, but as you stated, things can get ugly quickly.

What you need, I think, is a totals query, or a crosstab query. This will then be possible all in one query. A crosstab would be my choice.