Show all records not selected

[jjb373]

I have a table that looks like this:
(PK)
Auto Number Date Submitted(checkbox) <----fields
1 11 5/05/2005 Y (Y=checked, N=unchecked)
2 22 6/25/2005 Y
3 22 6/27/2005 N
4 22 4/30/2005 N
5 03 7/01/2005 N
6 14 6/30/2005 N
7 05 4/13/2005 N
8 05 3/28/2005 Y

I want to create a query that will display all records that do not have the checkbox checked. The kicker is: Some of these records have the same number (example: 22). One of the 3 records that have the number 22 is checked(Y). I want the query to eliminate all 22's.

This query should display:
Number Date
03 7/01/2005
14 6/30/2005

All others will be eliminated because at some point in time they were checked. I hope this is all clear enough.

 

[rac2]

SELECT * FROM MyTable
WHERE Number NOT IN (
       SELECT Number FROM MyTable
       WHERE Submitted = "Y"
)

 

[jjb373]

Where would I insert a DISTINCT to show only the "Number" once. In the above example of my table:

The 22 with the status of Y is eliminated but both the other records showing 22 are showing. I only want one.

I tried to change the properties for the query but i think it checks all records for the same exact data in all fields. I just want it to eliminate all duplicates for the "Number" field.

Is this clear?

 

[jjb373]

Does anyone have any ideas on where to add this Distinct in the query to only show the "number" once?

SELECT * FROM MyTable
WHERE Number NOT IN (
SELECT Number FROM MyTable
WHERE Submitted = "Y"
)

 

[lespaul]

SELECT DISTINCT * FROM ....


but I don't think that's going to do what you expect.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jjb373]

No, that gives me an error. I had tried that before then was left puzzled.

 

[PHV]

SELECT DISTINCT Number FROM MyTable
WHERE Number NOT IN (
SELECT Number FROM MyTable
WHERE Submitted = "Y"
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjb373]

Okay, now I was asked to show the oldest date associated with that record.

For example:

Number Date
1 5/25/05
2 5/20/05
2 1/20/05
2 10/30/05
3 6/25/05

The code above works perfect. All I want to do now is Select the Date, and if more than record has the same number show the record with the earliest date.

So out of the above records with the number = 2, the query will display the record:

Number Date
2 1/20/05

Any suggestions? Thanks again in advance

 

[PHV]

SELECT Number, Min([Date]) As EarliestDate
FROM MyTable
WHERE Number NOT IN (
SELECT Number FROM MyTable
WHERE Submitted = "Y"
)
GROUP BY Number

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886