Querying Most recent for different categories of object.

[pelicanstuff]

Hi,

I'm having a bit of trouble visualising how I would go about constructing a query that does the following:

I have a table 'passports' and a table 'persons', where each person can have more than one passport, as you would expect. The persons I am dealing with mainly have dual nationality. Obviously it is very easy to display, say, all of the US passports or all of the passports for a particular person.

What I want to be able to do is to display the most recent passport issued for a person by each country that has ever issued that person a passport, which is proving logically frustrating for me (as you can probably tell by my garbled language).

If anyone could point me in the correct direction, I would be very grateful.

Many thanks.

[jaxjagfan]

Hi,

I'm having a bit of trouble visualising how I would go about constructing a query that does the following:

I have a table 'passports' and a table 'persons', where each person can have more than one passport, as you would expect. The persons I am dealing with mainly have dual nationality. Obviously it is very easy to display, say, all of the US passports or all of the passports for a particular person.

What I want to be able to do is to display the most recent passport issued for a person by each country that has ever issued that person a passport, which is proving logically frustrating for me (as you can probably tell by my garbled language).

If anyone could point me in the correct direction, I would be very grateful.

Many thanks.

Your query should look similar to this:

Select Person, Country, Max(IssueDate) as MaxDate
From "yourtables_and _joins"
Group By Person, Country, Max(IssueDate)

[pelicanstuff]

Many thanks - this worked very well.

[pelicanstuff]

How about if I wanted the five most recent for each person?... This seems a little trickier.

[jaxjagfan]

How about if I wanted the five most recent for each person?... This seems a little trickier.

Select Top 5 Person, Country, Max(IssueDate) as MaxDate
From "yourtables_and _joins"
Group By Person, Country

Would give you just the first 5 records. Select Top 5 Percent .... Basically counts the records in the result and returns first 5 percent of the records.

You really need to define "5 most recent WHAT". If you are looking at Sales Counts by month and you want the last 5 months

Select Person, Country, Month(IssueDate ) as Month, Count(Sales) as SalesCnt
From "yourtables_and _joins"
Where IssueDate >= DateAdd("m",-5,IssueDate)
Group By Person, Country, Month(IssueDate )

HTH :D

[pelicanstuff]

Many thanks... in the end I actually found another way of doing this with a bit of lateral thinking. Can't remember it offhand right now as I'm at a different computer.