sql statement to find missing records

[johnnysmith1]

I have sql statement in access database to find records -3 fields ID, name, and Date. i use

SELECT ID,COUNT(name)AS CNT
FROM Table1
WHERE (((Table1.Date)=[Enter Date]
GROUP BY ID
HAVING COUNT(name)>1

now I want to find records < 1 - not sure of how to start to find these-any ideas- There are supposed to be 30 records each week added - sometimes people add too many records ( the statement above helps- but if they did not add record- do not know how to figure that) Thank you,

[_Damian S_]

Because it's a single table, you are a little restricted in how you can do this... One way is to use the following: SELECT ID FROM Table1 WHERE ID not in( SELECT ID FROM Table1 WHERE ((Table1.Date)=[Enter Date] GROUP BY ID HAVING COUNT(name)>1 ) Naturally this assumes that your existing query is returning the details you are after... Note also that your original query is only returning duplicates... If you want to return records where there is a single entry, your original query needs: HAVING COUNT(name) >= 1

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! If you need a laugh, check out my Vodafone World of Difference application | If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

[johnnysmith1]

thank you