Delete query not = Select Query

[scottshiv]

I have a table that I run a select query against it and then I run the same query as a delete query against it. What I am try to do is separate out the records that join with the SSN table from the main database and then delete them from the main database. Then I have two tables one contains the match records and the other with the match records eliminated. The query is matching records with the same SSN whose date of service (FDOS) occurred during their eligibility period (beginning Eligibility and Ending Eligibility)

When I run the query as a select query, it selects 3,118 records. When I run that query as a delete query it deletes 3,115 records. I am confused. Why doesn't the delete query delete 3,118 records? Core Count and Pre-Process are the same table. I copy Pre-Process to Core Count so that I still have a table that contains all of the records. If someone has a better idea on how to do this process, let me know. Thank you.


Delete Query

DELETE DISTINCTROW [Core Count P1].*, [Core Count P1].FDOS
FROM [Core Count P1] INNER JOIN [WHP SSN Undup] ON [Core Count P1].SSN = [WHP SSN Undup].SSN
WHERE ((([Core Count P1].FDOS) Between [Beginning Eligibility] And [Ending Eligibility]));

Select Query

SELECT [Elig Pre-Process].*
FROM [Elig Pre-Process] INNER JOIN [WHP SSN Undup] ON [Elig Pre-Process].SSN = [WHP SSN Undup].SSN
WHERE ((([Elig Pre-Process].FDOS) Between [Beginning Eligibility] And [Ending Eligibility]));

 

[randy700]

If I understand right, you ultimately have 2 tables that contain the same data, one of them also having additional records?
Why not keep all records in one table and add another field to indicate status? No need for duplication or deletion of data this way.


Randy