Select Into Query to show what is missing

[AccessNewb01]

All -

I am new to this forum and relatively new to more complicated queries in SQL. I have a table with four columns. The first column contains ID's where duplicates can often occur and the other three column tell me information about the first. The fourth column in my table has identify numbers that represent a specific set up and I need a query to return the values if a particular value does not appear for the ID.

Example Data set.

ID Field 2 Field 3 Field 4
41 59 12 1
41 59 12 2
41 59 12 71
42 60 12 1
42 60 12 2
42 60 12 741


What I need is for the query to look at the ID field and Field 4. Based on the ID, I need to know if field 4 is missing a particular number for just that group of ID's.. So for this example, look through ID field and tell me if any of the ID's "by group" are missing a 71 or a 2 in Field 4. So this particular example would return ID 42 is missing a 71. (I would want Fields 2 and 3) returned as well.

I would prefer it tell me what number in Field 4 it is missing, but understand if that is not possible.

 

[plog]

How vague is your example? I mean is the criteria always 71 & 2? Or could it change? Also, could you have ID's that have duplicate values in Field4 records (e.g. 2 records with Field4=71)?

In general (since you haven't provided a table name nor specific fields), you would create a subquery to get all the 71 & 2 records for an ID:

SELECT ID, Field4
FROM YourTableNameHere
WHERE Field4=71 OR Field4=2  
GROUP BY ID, Field4

Then you would build another query using that one. You would GROUP BY ID and COUNT Field4 and put criteira under the Field4 of >1. That would give you the exact list that had both. To get the IDs with missing ones you wold need to take that second query and bump it against a list of unique IDs you want to test. All the ones without a match in that second query would be the ones missing records.

 

[MarkK]

I think you could also do something like...

SELECT ID, -Sum(Field4 = 71 OR Field4 = 2) As CountState
FROM YourTable
GROUP BY ID

...in which you sum the result of a boolean test, and thereby effectively count the rows where the expression is true, by ID.

And it's easy enough to subquery to only find the IDs that are not complete...

SELECT ID, CountState
FROM 
   (
   SELECT ID, -Sum(Field4 = 71 OR Field4 = 2) As CountState
   FROM YourTable
   GROUP BY ID
   )
WHERE CountState < 2

And obviously from this, there is no need for a SELECT INTO query.
hth
Mark

 

[jdraw]

Markk,

Very nice.

 

[MarkK]

Thanks jdraw.

 

[AccessNewb01]

This worked great, thank you to everyone that responded. I'm all set now!