Selecting unique records from a dataset

[isladogs]

Ah - perhaps we're just at cross purposes. In the attributes table there are 2 records for ID=2259

Constituent ID	Constituent Specific Attributes Contact Preferences Import ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date	Constituent Specific Attributes Contact Preferences Comments
2259	00001-528-0000449513	Email opt out	02/11/2016	Bounced
2259	00001-528-0000046474	Email opt in

As one of these has no date it appears in my query results.
Similarly for other IDs with a null date record

So my query correctly does what I thought you described in post 1

BUT you want only those IDs where there are no duplicates AND then from that list filter again to only show those where the date field is NULL.
Not the same thing!

Yes, that does have 1175 records & can easily be done with one query

SELECT Attribute.[Constituent ID], Attribute_1.[Constituent Specific Attributes Contact Preferences Import ID], Attribute_1.[Constituent Specific Attributes Contact Preferences Description]FROM Attribute AS Attribute_1 INNER JOIN Attribute ON Attribute_1.[Constituent ID] = Attribute.[Constituent ID]
WHERE (((Attribute_1.[Constituent Specific Attributes Contact Preferences Date]) Is Null))
GROUP BY Attribute.[Constituent ID], Attribute_1.[Constituent Specific Attributes Contact Preferences Import ID], Attribute_1.[Constituent Specific Attributes Contact Preferences Description]
HAVING (((Count(Attribute.[Constituent ID]))=1));

NOTE: I still haven't looked at the alternative 3 step solution

[Antonio]

Ah yes this works now, but I don't understand why you had to join the datasets twice (rather than just once as I was trying to do)....it might be something to do with not being able to combine the Count for constituent ID with Date=Null (because the count, as a total works, because it returns one answer per constituent, but when combining with Dates, because you can have multiple dates per record, it doesnt like it)....anyway it works and its handy to know because I can cut the 3 queries down to 1 in future.


Thanks again Colin.

Antonio

[isladogs]

Ah yes this works now, but I don't understand why you had to join the datasets twice (rather than just once as I was trying to do)....it might be something to do with not being able to combine the Count for constituent ID with Date=Null (because the count, as a total works, because it returns one answer per constituent, but when combining with Dates, because you can have multiple dates per record, it doesnt like it)....anyway it works and its handy to know because I can cut the 3 queries down to 1 in future.


Thanks again Colin.

Antonio

Sounds like you are now interpreting this the way I did originally.
You only need to use the table once to identify IDs where there is only one record without a date (my original solution)

However you wanted to get all IDs where there were no duplicates AND then filter that list for those with no date.
Each part needs a separate copy of the table OR a separate query.

How are you getting on with what you called 'the big one'?
I haven't done any more to it and won't be able to do so for a couple of days at least