Another MAX(Date) query issue

[clueless1]

Hi there,

I've tried 2 different ways to do this, (below) but cant get to work.

I have one table [Activity-Opp-Data-Wenda] which is populated with OpportunityID - this is unique. I have another Table, (Activities) that contains Activity data: ActivityID (unique), OpportunityID, (not unique in this table) ActivityDate and Status - not unique.

Each Opportunity can have many Activities. I'd like to get the most recent, (ActivityDate) and the ActivityID from the Activities Table for each OpportunityID, WHERE the Status = 'Completed' - into [Activity-Opp-Data-Wenda].

UPDATE [Activity-Opp-Data-Wenda] INNER JOIN Activities ON [Activity-Opp-Data-Wenda].OpportunityID = Activities.OpportunityID SET [Activity-Opp-Data-Wenda].LastActivityID = Activities.ActivityID, [Activity-Opp-Data-Wenda].LastActivityDate = Activities.ActivityDate
WHERE Activities.OpportunityID & Activities.ActivityID & Activities.ActivityDate in (SELECT Activities.OpportunityID & Activities.ActivityID & MAX(Activities.ActivityDate) from Activities WHERE Activities.Status="Completed" GROUP BY  Activities.OpportunityID,Activities.ActivityID);

UPDATE [Activity-Opp-Data-Wenda]
INNER JOIN (SELECT OpportunityID, ActivityID, MAX(ActivityDate) AS MaxDate FROM Activities WHERE Status="Completed" GROUP BY OpportunityID) AS [Max] ON ([Activity-Opp-Data-Wenda].OpportunityID = Max.OpportunityID) AND (Activities.ActivityDate = Max.MaxDate) 
SET [Activity-Opp-Data-Wenda].LastActivityID = Activities.ActivityID, [Activity-Opp-Data-Wenda].LastActivityDate = Activities.ActivityDate

Thank you for any time spent looking at this, you're a life saver

 

[Micron]

See if my proposed solution for another post will help you. It seems what you want to do is quite similar, but I have not heard back from that poster if it worked or not. I can say that I tested it before posting my answer, so it worked for me.

http://www.mrexcel.com/forum/microsoft-access/907831-query-latest-record-based-date-field.html

 

[clueless1]

Thank you for your reply Micron. I took a look at your post and when I performed the query:
SELECT [tblEngagement].[Risk Exempt] FROM [tblEngagement]
WHERE ((([tblEngagement].[Last Updated])=(SELECT Max([tblEngagement].[Last Updated]) AS
MaxOfDate_Submitted FROM [tblEngagement])));
Modified for my table:

SELECT [Activities].[opportunityid] FROM activities
WHERE ((([activities].[activitydate])=(SELECT Max([activities].[activitydate]) AS 
MaxOfDate FROM activities)));

This just gave me the 'opportunityID' wit the most recent 'activitydate' - which was clearly a user error: '01/01/4501'.

NB: So I'm hopefully clear, the table 'Activites' has 'OpportunityID' - this will be duplicated as each Opportunity can have many Tasks. The ActivityID in the table 'Activites' will be unique. The table thats being updated & is poorly named: '[Activity-Opp-Data-Wenda]' will only contain unique 'OpportunityID's.

My query:

UPDATE [Activity-Opp-Data-Wenda] INNER JOIN Activities ON [Activity-Opp-Data-Wenda].OpportunityID = Activities.OpportunityID SET [Activity-Opp-Data-Wenda].LastActivityID = Activities.ActivityID, [Activity-Opp-Data-Wenda].LastActivityDate = Activities.ActivityDate
WHERE Activities.OpportunityID & Activities.ActivityID & Activities.ActivityDate in (SELECT Activities.OpportunityID & Activities.ActivityID & MAX(Activities.ActivityDate) from Activities WHERE Activities.Status="Completed" GROUP BY
Activities.OpportunityID, Activities.ActivityID);

Seems to work most of the time but not always. E.g.

OpportunityID=006w000000fY0ns, the most recent ActivityID in the table Activities that has a Status of 'Completed' is dated 04/11/2015, the one the query is pulling through is dated 11/09/2015.

Thanks again for your time.

 

[Micron]

Modified for my table:

SELECT [Activities].[opportunityid] FROM activities
WHERE ((([activities].[activitydate])=(SELECT Max([activities].[activitydate]) AS 
MaxOfDate FROM activities)));

This just gave me the 'opportunityID' wit the most recent 'activitydate' - which was clearly a user error: '01/01/4501'.
This demonstrates the necessity to use the power of the form to control data entry - esp. wrt dates. It looks like you have the syntax that should work, if you control the input and perhaps one other thing.**

NB: So I'm hopefully clear, the table 'Activites' has 'OpportunityID' - this will be duplicated as each Opportunity can have many Tasks. The ActivityID in the table 'Activites' will be unique. The table thats being updated & is poorly named: '[Activity-Opp-Data-Wenda]' will only contain unique 'OpportunityID's. Agreed and understood.

Seems to work most of the time but not always. E.g.
OpportunityID=006w000000fY0ns, the most recent ActivityID in the table Activities that has a Status of 'Completed' is dated 04/11/2015, the one the query is pulling through is dated 11/09/2015. **Likely your system date setting doesn't jive with how you are interpreting dates. In my system, 11/09/2015 IS later than 04/11/2015 (Nov. 9, 2015 IS more recent than Mar. 11 2015). You must be thinking dd/mm/yyyy format but your system setting (and mine) is mm/dd/yyyy.

Thanks again for your time.

My responses in blue.

 

[clueless1]

My responses in blue.

Thanks Micron but its not the date settings. I checked them in my PC and in Access - all dd/mm/yyyy.

+ in the Activities table I changed the date of a Completed Activity for this same Opportunity ID: 006w000000fY0ns to 12/09/2015, re-ran the query - it still picked the Activity with the date of 11/09/2015:

UPDATE [Activity-Opp-Data-Wenda] INNER JOIN Activities ON [Activity-Opp-Data-Wenda].OpportunityID = Activities.OpportunityID SET [Activity-Opp-Data-Wenda].LastActivityID = Activities.ActivityID, [Activity-Opp-Data-Wenda].LastActivityDate = Activities.ActivityDate
WHERE Activities.OpportunityID & Activities.ActivityID & Activities.ActivityDate in (SELECT Activities.OpportunityID & Activities.ActivityID & MAX(Activities.ActivityDate) from Activities WHERE Activities.Status="Completed" GROUP BY
Activities.OpportunityID, Activities.ActivityID);

I'm sure the errors staring me in the face, but I cant see it

 

[xenou]

I would suggest posting some test data.

Also, you appear to be concatenating values and using them in comparisons. It seems like that is unnecessary and could be a source of errors. If you do use dates as part of a text string and you want to do comparisons using those strings you must put the dates in yyyymmdd format so that they will order properly. Otherwise 12/02/2015 will be greater than 12/01/2016 which is incorrect (but 20161201 is greater than 20151202, and this is what you want). But I would suggest re-writing the query using the fields without concatenation.

 

[Micron]

I don't know if you're using this sql in code or if it's in a query. In vba, you must use US English date format mm/dd/yyyy. I think this can hold true even if it's a query that calls a function to determine the criteria. The interpreter will handle the date in that format.

not the date settings. I checked them in my PC and in Access - all dd/mm/yyyy.

I presume you're thinking you are controlling the date itself, but you're only controlling the format in a table perhaps. A date is a serial number; you can format it to display in either US or not, but the value remains the same. Try viewing the values as serial dates to see if the ones you think are out of order are actually in the correct order.

 

[clueless1]

Its ok, I found the problem - it was because I was grouping on 'ActivityID' as well as 'OpportunityID' - I didnt need to group by ActivityID or include it in the statement below:

+ Thanks for all your assistance Micron & xenou - Merry Christmas!

SELECT Activities.OpportunityID & Activities.ActivityID & MAX(Activities.ActivityDate) from Activities WHERE Activities.Status="Completed" GROUP BY
Activities.OpportunityID, Activities.ActivityID);