Selecting records where ALL records with the same ID have blank dates

[Antonio]

Hi there,


This is an easy one I imagine.



I need to be able to select either the whole set of Constituent ID's or one (the first, the last, doesnt matter which) where the ENTIRE set of Constituent IDs have no dates in the following data table:

Constituent ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date
2259	Email opt in
2259	Email opt out	02/11/2016
3000	Telephone opt out
3000	Email opt out
6218	Email opt out	20/04/2018
6218	SMS opt in
6218	Email opt out	01/08/2017
7728	Postal opt in
7728	Email opt out
7728	Email opt out

So it should return this dataset:

Constituent ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date
3000	Telephone opt out
3000	Email opt out
7728	Postal opt in
7728	Email opt out
7728	Email opt out

As always, any help really appreciated.


Thanks,

Antonio

[ranman256]

make 3 queries:
Q1: count recs for each user
select [Constituent ID] , count([Constituent ID]) RecCount from table

Q2:count those with a blanks
select [Constituent ID] , count([Constituent ID]) as NullCount from table where [Preferences Date] is null

Q3: grab the ones with ALL blank
select Q2.[Constituent ID] from Q2,Q1 where Q1.RecCount = Q2.NullCount

open Q3.

[isladogs]

Ranman replied long before I uploaded but as I'd already done it (by EXACTLY the same method) here are my 3 queries:

1. qryCountIDTotals

SELECT Attribute.[Constituent ID], Count(Attribute.[Constituent ID]) AS TotalCountFROM Attribute
GROUP BY Attribute.[Constituent ID];

2. qryCountIDNullDate

SELECT Attribute.[Constituent ID], Count(Attribute.[Constituent ID]) AS TotalCount
FROM Attribute
WHERE (((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Null))
GROUP BY Attribute.[Constituent ID];

3. Combining the above as qryConstituentIDAllDatesNull

SELECT qryCountIDTotals.[Constituent ID], Attribute.[Constituent Specific Attributes Contact Preferences Description], Attribute.[Constituent Specific Attributes Contact Preferences Date]FROM (qryCountIDTotals INNER JOIN qryCountIDNullDate ON (qryCountIDTotals.TotalCount = qryCountIDNullDate.TotalCount) AND (qryCountIDTotals.[Constituent ID] = qryCountIDNullDate.[Constituent ID])) INNER JOIN Attribute ON qryCountIDNullDate.[Constituent ID] = Attribute.[Constituent ID];

BTW in the dataset I had previously, there is no ID=3000 record so I can't replicate your results

[Antonio]

Yes as I thought I had added the wrong tables and incorrectly linked them in my 3rd query.

I have corrected using your approach.

Most importantly I understand how this works. By inner joining ConstituentID from both tables AND the ConstituentID Count with the NullDates Count, it will not only select records where the constituentID matches in both tables but also where the counts match, e.g. a fake example constituent who has 3 IDs and 2 Null dates, will meet the 1st criteria, but not the second (3 not matching with 2); whereas a record with 4 IDs and 4 Null dates will match on the 1st and 2nd (4 matching 4) - thats how you get round that problem.

Once again, thank you very much Colin.

And many thanks to Ranman256.

Much appreciated help from both of you.


Antonio