Top Values Query

[acrocker3434]

Hi-
I have two tables "Patients" and "Office Visits". There is a one to many relationship (one patient has many office visits). I am trying to get a list of the most recent office visit for each patient but when I sort the office visits in descending order and use the top values query to retrieve the top 1 - it gives me the top 1 for the entire list. How can I write a query to give me just the most recent visit for each patient?

THANK YOU!

 

[ajetrumpet]

How can I write a query to give me just the most recent visit for each patient?

e.g. ---

SELECT patient, max(visitdate) as [Last Visit]
  FROM table
    GROUP BY patient

 

[acrocker3434]

great - thanks!

now, what if i want the 5 most recent visits? I am assuming I change the "max" part...but to what?

 

[ajetrumpet]

i just answered a post on this elsewhere. if you want a TOP, and ORDER BY and a GROUP BY clause all in the same query, you can't do it (easily). You will need to stack your queries. (make more than one). This is the worst feature of access. Stacked queries in access = one calculation or GUI click in excel. It's really too bad...IMO. =)


BTW, thank you for a useful posting. this one will be around for awhile to help others out.

 

[Kicker774]

So I'm attempting a similar query myself.

I'm finding the top 10 items in stock for a store in across 20-some departments.
TopValues query works flawlessly for 1 department but I need to find the top 10 across every dept in one fell swoop.

Enter:

http://support.microsoft.com/kb/153747/en-us

In my case I generated a table from a query that gives me everything on hand in every department:

SELECT ZOn_Hand.department, ZOn_Hand.item_number, ZOn_Hand.sku_code, ZOn_Hand.retail_ref, ZOn_Hand.Description, ZOn_Hand.ColorDesc, ZOn_Hand.Size, ZOn_Hand.Avail, ZOn_Hand.Orig_retail
FROM ZOn_Hand;

Per the article above I add in:

WHERE (((ZOn_Hand.avail) In (Select Top 10 ZOn_Hand.Avail From ZOn_Hand Where Department=ZOn_Hand.Department Order By zON_Hand.avail desc)))
ORDER BY ZOn_Hand.department, ZOn_Hand.Avail;

and I'm only getting the top 10 over all.

I'm assuming I need to adjsut something with my code with the department info because I'm not quite getting what it's doing with the "Where Department=ZOn_Hand.Department Order" part.

Where am I going wrong here?