Find Records That Do Not Have A Priority Code

[jereece]

I have a database table with the following fields.

Site
Event_No
Action_No
Priority

For Event_No may have more than one record becasue it may contain a number of Action_No. For example, Site A may have Event_No 123 which has 10 Action_No's. Each will be a separate record. I want a query to identify all Event_No's that only has records with Priority "3". So if Event 123 has 10 Actions, some will be Priority 1, some Priority 2 and some Priority 3. I want to identify the Events that only have Priority 3, no 2's or 1's. So if Event 123 had some Priority 2 I don't care. I only want the Events with only Priority "3".

Hope this is clear.

Thanks,
Jim

 

[jdraw]

Try: (untested)

Select * from YOURTable
Where PriorityCode = "3" And Event_no
NOT In (Select Event_no from YOURTable
Where PriorityCode <> "3" )

 

[bob fitz]

Sounds like you just need to create a query, based on this table with some criteria added to restrict the records that it returns. You would need to use 3 in the criteria row of the Priority column. Not entirely clear about which records you want returned, so you may need 123 in the Event column as well.

 

[jereece]

I don't think either of the suggestions will work. Here's why. Lets say I have the following records,

Site Event_No Action_No Priority
1 123 1 2
1 123 2 3
1 123 3 3
1 123 4 2
1 123 5 1

If I simply have a criteria to show me the records with Priority 3, then what will be returend is

Site Event_No Action_No Priority
1 123 2 3
1 123 3 3

What I want to know is which Event_No's only have Priority 3 actions (No Priority 1 or 2).

Thanks,
Jim

 

[bob fitz]

What I want to know is which Event_No's only have Priority 3 actions (No Priority 1 or 2).

Perhaps I'm missing something but isn't that what you get in the example that you've posted.

 

[accessAnswer]

hey,

So if I understood you correctly,

You need a such a query that if you run it on this table:

Site Event_No Action_No Priority
1 123 1 2
1 123 2 3
1 123 3 3
1 123 4 2
1 123 5 1


The result would be empty. Since This table only has Event_no =123 ( only 1 event)
and this Event has many priorities 1,2,3

If you table looked like this:

Site Event_No Action_No Priority
1 123 2 3
1 123 3 3


Then you query should give result
Event_No
123

As Even_No 123 ---> all priorities of this event are 3.

Am I correct?

 

[jereece]

accessAnswer - You are correct. For the example I gave the results would be blank because Event_No 123 has some Action_No's with Priority other than 3.

If it helps to understand what I am trying to do, Priority 1 and 2 actions fix the problem but Priority 3 are only enhancements. So I want to find our events where no fix has been identified.

So in my example below, if ALL the Action_No's had Priority 3,then the query would return a result.

Thanks again for the help.

Jim

 

[accessAnswer]

[FONT=&quot]
Hey Jereece,

From what I understood, this solution below should work:

[/FONT][FONT=&quot]So here is your solution:

Query 0: If the same Event_No has many diff Action_No with same priority- Group Then together: The below becomes 1 line:[/FONT]
[FONT=&quot][/FONT][FONT=&quot]
SELECT DiSTINCT Practice.Event_no, Practice.Priority
FROM Practice;


Query 1: - just selects Events have just have one Distinct Priority ( all actiones of the Event are of the same Priority)

SELECT Count(Query0.Event_no) AS CountOfEvent_no, Query0.Event_no
FROM Query0
GROUP BY Query0.Event_no
HAVING (((Count(Query0.Event_no))=1));


Query 2: - select only priority 3

SELECT Query0.Event_no, Query0.Priority
FROM Query1 LEFT JOIN Query0 ON Query1.Event_no = Query0.Event_no
Where Query0.Priority=3;

Let me know if this works, it works on my computer
As long as I understand requirements correctly[/FONT]

 

[jdraw]

Did you try the sql I suggested? What result?

 

[accessAnswer]

Try: (untested)

Select * from YOURTable
Where PriorityCode = "3" And Event_no
NOT In (Select Event_no from YOURTable
Where PriorityCode <> "3" )

Great! It works! Magic

In addition i see in my result, we have to do Final GROUPING
in case
Event 123 has :
Action 2 - Priority 3
Action 3 - priority 3

Other than that Great Query

 

[jereece]

Thanks everyone. I am headed out of town on business so it may be a while before I can test this out for myself but from what you say it should work fine. I really appreciate the help.

Jim