Query to find last date value

[Hatye]

Hi,

I have a table named "Lisensoversikt" where I have the following items.
Second column shows product number:

I would like to create a query to return "Aktivitet", "Antall lisenser" og "Dato fra" with the highest date-value. In the example above I would have seen ID 2 = 125 since 26.10.2017 is newer than 25.10.2017.


I have created this query:

The SQL returns the following code:

SELECT Lisensoversikt.Aktivitet, dbo_Aktivitet.AKTIVITET, Lisensoversikt.[Antall lisenser], Lisensoversikt.[Dato fra]
FROM Lisensoversikt INNER JOIN dbo_Aktivitet ON Lisensoversikt.Aktivitet = dbo_Aktivitet.AKTIVITETSNR
ORDER BY Lisensoversikt.Aktivitet;

How can I write this to only show the "Aktivitet" with highest date-value?

 

[Joe4]

You would use an Aggregate Query to get that, similarly to what is shown here: https://www.techonthenet.com/access/queries/max_query2007.php
Once you get a query that returns the highest date value, you can then create a new query, joining your original table(s) with this query, joining on the date field, and then returning any fields that you want.

 

[Hatye]

Hi Joe4 and thank you for your reply!
That kind of works, but there might be a problem if we have the same date with several products, I guess?

 

[Joe4]

That kind of works, but there might be a problem if we have the same date with several products, I guess?

It sounds like the issue isn't with the query, but rather with the situation. If you have multiple products with the same highest date value, what do you want to happen?

 

[Hatye]

It sounds like the issue isn't with the query, but rather with the situation. If you have multiple products with the same highest date value, what do you want to happen?

I would like to see the highest date on each product.

This is what I am trying to accomplish:
We have 5 products that are licensed and I want to see how many licenses we have of each product and which date the number of license has been changed.

 

[Joe4]

Then in the original aggregate query where you are returning the max date, simply add the Product field (and leave the phrase "Group By" under it).
This will return each product and the max date.

If you need other fields, simply join this back to the original table/query on BOTH the product and date field.