SQL query to eliminate dupes and records by date 1

[chris3942]

I have a table with the following fields: [date], [first name], [last name], [address], [city], [state] and [zip]. I want to select only records that have data in the [first name] and [last name] fields, to eliminate duplicates based only on the [address] field and also eliminate any records that are earlier than 1/1/2000 and finally sort by zip code. I've come up with the following SQL code to select only records with data in [first name] and [last name], eliminate the duplicates based on the [address] field and sort on [zip] but the eliminate dates prior to 01/01/2000 portion fails to eliminate any records earlier than 01/01/2000. Can anyone complete the puzzle?

SELECT [DATE], [FIRST NAME], [LAST NAME], [ADDRESS],[CITY], [STATE], [ZIP] FROM MSEW2 AS C WHERE C.[REC #] NOT IN
(
SELECT A.[REC #]
FROM MSEW2 AS A, MSEW2 AS B
WHERE (A.[REC #] < B.[REC #]) AND
(A.[ADDRESS] = B.[ADDRESS]) AND
(A.[DATE] > 01/01/2000) AND
(B.[DATE] > 01/01/2000)
)

AND [CITY] IS NOT NULL AND [STATE] IS NOT NULL

ORDER BY ZIP

 

[PHV]

Replace this:
> 01/01/2000
By this:
>=#01/01/2000#
And add this in the main where clause:
AND [DATE]>=#01/01/2000#

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

 

[chris3942]

PH,

Thanks for the quick tip. Didn't quite understand exactly what you wanted me to do but I played around with your suggestion and finally came up with the following code which works. I removed the two references to [DATE] from within the parenthesis and placed your suggested line down with the "ORDER BY ZIP". I assume this is what you meant for me to do.

Thanks again.

Chris3942



SELECT [DATE], [FIRST NAME], [LAST NAME], [ADDRESS],[CITY], [STATE], [ZIP] FROM MSEW2 AS C WHERE C.[REC #] NOT IN
(

SELECT A.[REC #]
FROM MSEW2 AS A, MSEW2 AS B
WHERE (A.[REC #] < B.[REC #]) AND
(A.[ADDRESS] = B.[ADDRESS])

)

AND [CITY] IS NOT NULL AND [STATE] IS NOT NULL

AND [DATE]>=#01/01/2000#

ORDER BY ZIP