More Help with SQL Statement for a Query

[Antonio]

Hi there,


I am attempting to do something which is probably relatively simple.

Within a single dataset, I need to select records according to certain criteria.

I'm afraid right now I cannot upload the full dataset. However here is an example of the data which should be enough for the purposes of determining the answer:

Attribute

ContactID	AttributeID	AttributeDate	AttributeCode
230	00001-528-0000536068	28/02/2017	Email opt in
233	00001-528-0000452185	15/01/2017	Email opt in
233	00001-528-0000337679	25/10/2016	Email opt in
233	00001-528-0000469117	15/01/2017	Email opt in
233	00001-528-0000678137	24/04/2018	Email opt out
233	00001-528-0000337680	25/10/2016	Postal opt in
233	00001-528-0000351040	03/11/2016	Postal opt in
233	00001-528-0000337681	25/10/2016	Telephone opt in
233	00001-528-0000351041	03/11/2016	Telephone opt in
233	00001-528-0000337610	29/06/2016	Email opt out
241	00001-528-0000568001	06/04/2017	Postal opt in
241	00001-528-0000568002	06/04/2017	Telephone opt in
241	00001-528-0000568000	06/04/2017	Email opt in

What I need to do is select records in this dataset where:
- for each contactID, select the most recent record (i.e. latest AttributeDate) from each 'channel', i.e. it could return up to 3 records per ContactID, from either Email AND Postal AND Telephone, all sorted by ContactID
bear in mind that some records could have the same date, so maybe it will need a combination of 'latest attributeDate' and 'highest AttributeID' to select the correct record

In other words, it should return this dataset:

ContactID	AttributeID	AttributeDate	AttributeCode
230	00001-528-0000536068	28/02/2017	Email opt in
233	00001-528-0000678137	24/04/2018	Email opt out
233	00001-528-0000351040	03/11/2016	Postal opt in
233	00001-528-0000351041	03/11/2016	Telephone opt in
241	00001-528-0000568001	06/04/2017	Postal opt in
241	00001-528-0000568002	06/04/2017	Telephone opt in
241	00001-528-0000568000	06/04/2017	Email opt in

My guess would be, but of course doesn't work:

SELECT Attribute.*
FROM Attribute
WHERE Attribute.ContactID IN (SELECT TOP 1 Attribute.ContactID
FROM Attribute


WHERE ((Left([AttributeCode],InStr([AttributeCode]," ")))=Left([AttributeCode],InStr([AttributeCode]," "))))
ORDER BY Attribute.AttributeDate ASC)
ORDER BY Attribute.ContactID;


Any help would be much appreciated!!

Antonio

[isladogs]

Typing on my tablet so I can't write code at the moment

Create a query using those 4 fields.
Now click Totals in the design ribbon to change it to an aggregate query
An extra Totals row appears in the query design window with each field showing Group By
Now change that to Max for the AttributeDate field using the dropdown.

That will hopefully give what you want

[Antonio]

I will give it a go when I get the the stage of needing that, and then let you know how I get on.

Thanks again Colin / ridders52.

Antonio

[isladogs]

This is what I meant earlier ... but not tested on your data

SELECT Attribute.ContactID, Attribute.[Contact Specific Attributes Contact Preferences Import ID] AS AttributeID, Max(Attribute.AttributeDate) AS LatestAttributeDate, Attribute.AttributeCode
FROM Attribute
GROUP BY Attribute.ContactID, Attribute.[Contact Specific Attributes Contact Preferences Import ID], Attribute.AttributeCode;

[Antonio]

Thanks for the code, which is what I was originally after as you know, however I have not got to this stage yet....once I do I will let you know how I get on.

However I have another imminent problem which I will post as a separate post....


Antonio

[Antonio]

Sorry for my delay in getting back to you ridders52 / Colin, but yes this does do roughly what I needed. Again thanks very much for your help.

I will now mark this post as solved.


Antonio

[isladogs]

Excellent. Keep up the good work