select count duplicate values by id

[Access2013]

I am using Access 2010 on a Windows 7 laptop. I need a query to provide a list of ID's that have more than one occurrence of IDandDate combined but haven't been successful getting past syntax errors.

Using this table structure as an example:
ID - defined as text field
Date - defined as date/time field
TestValue

This legacy table contains a record for each test. The table should be unique based on the ID and Date combination but was never restricted to that rule. I am converting to a new table but need to identify the duplicate entries so they can be addressed by business folks.

I thought I knew SQL but have had no luck in this Access environment.

Thanks in advance for help with access 2010 query.

 

[plog]

First, 'Date' is a bad field name because it is a reserved word in Access. Most likely this is the cause of your syntax errors. I would rename it by prefixing it with what the data is for, i.e. 'TestDate'.

I need a query to provide a list of ID's that have more than one occurrence of IDandDate combined but haven't been successful getting past syntax errors.

To achieve that, and I do mean exactly that; you would need a sub query to determine all ID and Date permutations and how many times they appear. This is that sub-query:

SELECT ID, [Date], COUNT(ID) AS TestCount 
FROM YourTableNameHere
GROUP BY ID, [Date]
HAVING COUNT(ID)>1;

Replace 'YourTableNameHere' in the above code with the name of your table. Name that query subMultipleOccurences. Then use it as the basis of another query:

 SELECT ID FROM subMultipleOccurences GROUP BY ID;

That query will produce the exact results you asked for.