Query for latest date based on ID from another table.

[HRakibul]

hello there,

I am facing a problem while using query option. I need to get a result based on the latest date it will show me the job status. the product id came from a different table. Here the access sample database is attached. I need the result shown in the image but the result is not coming as I expected.



Please help.

 

[isladogs]

This will give you the 2 records you require filtering for Job startus='Done'.
In this query, the job status field isn't displayed:

SELECT [Status cow].Tags, Max([Status cow].Date) AS MaxOfDate
FROM [Status cow]
WHERE ((([Status cow].[Job Status])='Done'))
GROUP BY [Status cow].Tags;

If you want to see that column as well use:

SELECT [Status cow].Tags, Max([Status cow].Date) AS MaxOfDate, [Status cow].[Job Status]
FROM [Status cow]
WHERE ((([Status cow].[Job Status])='Done'))
GROUP BY [Status cow].Tags, [Status cow].[Job Status];

 

[HRakibul]

Hello Dear,
Thanks for Reply.

Actually, i didn't ask for searching by the word "Done". I want to see single TAG's date wise last update. so according to that, I need to search for the latest date for individual TAG.

so finally it will show me the last updated Date for a single ID with Status.

Thanks

 

[isladogs]

In that case, if you don't need the Job Status column, this will work

SELECT [Status cow].Tags, Max([Status cow].Date) AS MaxOfDate
FROM [Status cow]
GROUP BY [Status cow].Tags;

If you do need that column, a subquery is required
First you use the above query (called queryMaxDate) to get the latest dates for each tag.
Then link that query to the original table using the Tag & MaxOfDate fields to get the Job Status value

SELECT queryMaxDate.Tags, queryMaxDate.MaxOfDate, [Status cow].[Job Status]
FROM queryMaxDate INNER JOIN [Status cow] ON (queryMaxDate.MaxOfDate = [Status cow].Date) AND [queryMaxDate.Tags = [Status cow].Tags);

2 other things:
1. Tag is a reserved word in Access and using 'Tags' as a field may cause you problems. Suggest e.g. CowTag
2. Recommend you get rid of spaces in all table & field names

 

[HRakibul]

Thanks for Reply!

can you please work on my database. I can't understand.

I have given what I need into the PNG and also given the present condition of my database.

please see my post at the beginning.

thanks

 

[isladogs]

Thanks for Reply!

can you please work on my database. I can't understand.

I have given what I need into the PNG and also given the present condition of my database.

please see my post at the beginning.

thanks

I just gave you the solution in my last post.
However, as requested, I've attached the updated version with the 2 queries listed in my last reply

The query 'qryMaxDateTagStatus' has the results you require

 

[HRakibul]

Thanks, Colin.

You have done well but I thought there would be some easy process.

it was not my original db. In my original DB the existing query is far more complex. it has some left join and some other complicacy.

So I am afraid it would be more complicated when I will do more query over the query.

moreover, this query will be linked to an excel file where other queries are dashboarded.

but how ever your effort is mind blowing. but if there is any short technique to do it within one query please let me know.

regards
Rakibul

 

[isladogs]

This was your original query

SELECT [Cow TAG].[Tag Name], Max([Status cow].Date) AS MaxOfDate, [Status cow].[Job Status]
FROM [Cow TAG] INNER JOIN [Status cow] ON [Cow TAG].ID = [Status cow].Tags
GROUP BY [Cow TAG].[Tag Name], [Status cow].[Job Status];

I didn't use it as it wasn't going to work for your required output

It is possible to do this in one query (which includes a subquery) but I thought you would find it easier to understand if I did it in 2 stages

For more information on subqueries, read this link:
http://allenbrowne.com/subquery-01.html