Selecting grouped records where certain criteria is met

[Antonio]

Hi there,


I am attempting something but am stuck mainly at the point of selecting the records where they have the same date.

I have uploaded the dataset I am working on to make sure there are no misunderstandings and to make explanation easier.

I am trying to select records within the 'Attribute' dataset where the same Constituent ID has an:

(Constituent...Description = 'Email opt in' AND Constituent...Description = 'Email opt out' AND both Constituent...Descriptions have the SAME date) OR
(Constituent...Description = 'Postal opt in' AND Constituent...Description = 'Postal opt out' AND both Constituent...Descriptions have the SAME date) OR
(Constituent...Description = 'Telephone opt in' AND Constituent...Description = 'Telephone opt out' AND both Constituent...Descriptions have the SAME date) OR
(Constituent...Description = 'SMS opt in' AND Constituent...Description = 'SMS opt out' AND both Constituent...Descriptions have the SAME date) OR

I want to export all the Constituent IDs from these selected records.

I dont know if any records exist that meet this criteria so dont have any examples, but thats what I am trying to find out!

A fake example (i.e. this does not occur in the dataset, but just to demonstrate the principle):

Constituent ID	Constituent Specific Attributes Contact Preferences Description	Constituent Specific Attributes Contact Preferences Date
1	Email opt in	01/01/2000
1	Email opt out	01/01/2000
1	Telephone opt in
1	SMS opt out	01/06/2015
2	Telephone opt out	01/10/2017
2	Telephone opt in	01/10/2017

Again, any help will be much appreciated.


Antonio

[isladogs]

The way this is done is to add the attribute table to the query designer twice joining by ID and Date fields

SELECT Attribute.[Constituent ID], Attribute.[Constituent Specific Attributes Contact Preferences Description], Attribute_1.[Constituent Specific Attributes Contact Preferences Description], Attribute.[Constituent Specific Attributes Contact Preferences Date]FROM Attribute INNER JOIN Attribute AS Attribute_1 ON (Attribute.[Constituent Specific Attributes Contact Preferences Date] = Attribute_1.[Constituent Specific Attributes Contact Preferences Date]) AND (Attribute.[Constituent ID] = Attribute_1.[Constituent ID])
WHERE (((Attribute.[Constituent Specific Attributes Contact Preferences Description])="EMail opt in") AND ((Attribute_1.[Constituent Specific Attributes Contact Preferences Description])="Email opt out") AND ((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Not Null)) OR (((Attribute.[Constituent Specific Attributes Contact Preferences Description])="Postal opt in") AND ((Attribute_1.[Constituent Specific Attributes Contact Preferences Description])="Postal opt out") AND ((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Not Null)) OR (((Attribute.[Constituent Specific Attributes Contact Preferences Description])="Telephone opt in") AND ((Attribute_1.[Constituent Specific Attributes Contact Preferences Description])="Telephone opt out") AND ((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Not Null)) OR (((Attribute.[Constituent Specific Attributes Contact Preferences Description])="SMS opt in") AND ((Attribute_1.[Constituent Specific Attributes Contact Preferences Description])="SMS opt out") AND ((Attribute.[Constituent Specific Attributes Contact Preferences Date]) Is Not Null));

You'll be pleased to know there are no results for this query

[Antonio]

Ah yes, that works perfectly!

Again, another VERY useful principle to learn which I will adapt in future use for similar situations. Before doing this exercise, I had always selected records using fairly basic SQL BETWEEN datasets; this is the 1st exercise where I have had to select data based upon a SINGLE dataset - you get around this problem by effectively 'duplicating' the dataset to allow comparisons between the same fields.

Again, thanks very much Colin, really appreciated.

At this rate I might actually be in a position to answer other peoples questions soon


Antonio