Selecting records with missing dates where multiple records exist

[Antonio]

Hi there,




One more problem I need a solution to:

I have uploaded another sample of data, and from this dataset, I need to select records where the 'Constituent ID' field is NOT unique (i.e. where there are 2 or more 'Constituent ID's) AND where at least one record has an empty date in the 'Constituent Specific Attributes Contact Preferences Date' field.

Also, for every 'Constituent ID' that meets this criteria, I want to output ALL the records with that 'Constituent ID' (e.g. if the dataset contains a Constituent ID that is duplicated 5 times, and 1 of the records has a missing date, this record would be selected, and all 5 records would be outputted).

A few examples of returned records from the uploaded dataset would be:

Constituent ID	Constituent Specific Attributes Contact Preferences Import ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date
2259	00001-528-0000449513	Email opt out	02/11/2016
2259	00001-528-0000046474	Email opt in
2637	00001-528-0000129918	Email opt in	05/04/2016
2637	00001-528-0000273247	Postal opt out
2637	00001-528-0000273248	Telephone opt out

Again, as I like to work in SQL, if anyone has any ideas, could you type the SQL statement in your solution?

Any help, as always, much appreciated.


Thanks,

Antonio

[Gicu]

Hi Antonio,
Have a look at the attached file (sorry I just saw you uploaded yours), look at the qryConstituentsWithMissingDate_AND_MultipleRecords query.
Cheers,
Vlad

[Gicu]

So Antonio, have you had time to look at my sample?

Vlad

[Antonio]

Hi Vlad,


Apologies for the late reply, as I explained in an earlier post, because I am doing work on behalf of a client and have deadlines to meet and expectations to manage, I need to sometimes 'prematurely' ask questions so that I can be ready when I actually need them!

But yesterday I implemented your solution and it works perfectly. I also understand why you split the queries into out, because in order to display all the fields from Constituent, you had to join this to your aggregate queries (because I get th e impression that its not easy (maybe impossible) to output the full data from an aggregate query).

It was very helpful, thanks very much, much appreciated.



Antonio

[Gicu]

You're welcome, glad you got it working!

Vlad

[Minty]

I also understand why you split the queries into out, because in order to display all the fields from Constituent, you had to join this to your aggregate queries (because I get th e impression that its not easy (maybe impossible) to output the full data from an aggregate query).

You often have to aggregate just the data you need , then rejoin to your Original data as otherwise the aggregate fails because of the additional data in the record.

This is particularly true if counting occurrences with datetime fields. Anything with a time element is likely to be a unique record, so if you include it in your grouping, you end up with many "Distinct" records.

[Antonio]

Yes you have confirmed my thinking, and more! I will bear that in mind, especially when handling datetime fields.

Thanks Minty.

Antonio