Summarizing a table based on a list of values

[soloquery]

Hi everyone,

I have a two tables with a one-to-many relationship between them.

Table 1: tblIssues
IssueID (Primary Key – Autonumber)

Table 2: tblSubIssues
IssueID (FK from tblIssues)
IAVerification (Value list - "Verified";"Not Yet Verified";"In Progress";"Passed";"N/A")

One IssueID can have multiple Verification values. For instance, this query:

SELECT tblIssue.IssueID, tblSubIssue.IAVerification
FROM tblIssue INNER JOIN tblSubIssue ON (tblIssue.IssueID = tblSubIssue.IssueID) AND (tblIssue.IssueID = tblSubIssue.IssueID);

Gives this result:

IssueID IAVerification
93 In Progress
93 Not Yet Verified
93 In Progress
94 Verified
94 In Progress
95 Verified
96 Not Yet Verified
97 Not Yet Verified
97 Verified
98 Verified
99 Not Yet Verified
100 Verified
101 Verified
101 Verified
101 In Progress
102 Not Yet Verified
102 Not Yet Verified
103 Verified
103 In Progress
103 In Progress
103 Not Yet Verified
104 Not Yet Verified
104 Not Yet Verified
104 Not Yet Verified
105 Verified
105 Not Yet Verified

I’m trying to write a query to “roll up” or summarize on the IssueID with the following preference criteria:

1. Not Yet Verified
2. In Progress
3. Verified
4. Passed
5. N/A

So the logic would be, if there is more than one IAVerification associated with an IssueID it would be filtered according to the list above. So for instance, Not Yet Verified takes precedence over all other values, if NYV doesn’t exist then In Progress takes precedence, etc. So the table above essentially becomes:

IssueID IAVerification
93 Not Yet Verified
94 In Progress
95 Verified
96 Not Yet Verified
97 Not Yet Verified
98 Verified
99 Not Yet Verified
100 Verified
101 In Progress
102 Not Yet Verified
103 Not Yet Verified
104 Not Yet Verified
105 Not Yet Verified

My gut tells me I need a subquery do this but I’m stumped as to how to craft the logic. Any assistance would be greatly appreciated. Thanks!

 

[Arvin]

Sounds like you need a Total's query ... with a count of issueId's grouped by IAVerification

Question ... do you wan to display the total number of "issues" based on "IAVerification"

so, the example would be ....

CountofIssueId's IAVerification
10 Not Yet Verified
5 Verified
and so on ....

 

[soloquery]

Actually, I don't want a Total of the issues, I need to have a complete list of all IssueID's but with only one IAVerification per IssueID. Each IssueID can have multiple IAVerifications associated with it. But the results table needs to only show one IAVerification value based on the preference of the 1 through 5 values above.

So, if IssueID 93 has two In Progress values and one Not Yet Verified value the results should show a single record with IssueID 93 and IAVerification of Not Yet Verified, since Not Yet Verified is the #1 in the preference list. If Not Yet Verified isn't associated with an IssueID then the next one down the list takes precedence.

Basically the results should look like the second table when all is said and done.

Thanks for your suggestion!

-Solo

 

[Arvin]

Just wondering ....

can you provide a sample of your Db? ...before I start creating one

 

[soloquery]

It's a huge DB so I just extracted the two tables in question and put them in the attached DB.

Thanks!

-Solo

 

[Arvin]

Good morning,

1st off my apologizes for the delay ... we had some things blow up at work and I have been busy with that ... I will be playing with this sample db later on today ...

Just wanted to provide an update ...

take care

 

[soloquery]

Thanks! I appreciate it!