4 more recent notes

[eddy]

Hi, I have a a table that keeps notes about the status of an invoice. It can have 1 or a lot of notes per invoice. I need it to just display the last 4 notes for each invoice. I used TOP 4 but it only shows the top 4 notes and note the top 4 notes for each invoice. Can anyone help?

 

[pbaldy]

Does this help?

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

 

[eddy]

I thought it would be useful but it still returns only 4 for me instead of 4 for each invoice

This is what I used.

SELECT CSNotesDetail.*
FROM CSNotesDetail
WHERE (((CSNotesDetail.ID) In (Select Top 4 [ID] From CSNotesDetail Where [ID]=[CSNotesDetail].[ID] Order By [ID] Desc)));

 

[pbaldy]

I would think invoice number would come into it somewhere. Can you post a sample db?

 

[georgedwilkinson]

You won't be able to use "IN" that way. Assuming the main form is correctly linked to the subform with the notes, you can just put your "top 4" in the query, I believe.

 

[pbaldy]

In what way George? I use that format in a couple of instances where I need the top x by group. It works fine.

 

[georgedwilkinson]

In what way George? I use that format in a couple of instances where I need the top x by group. It works fine.

The format is fine.

My assumption is that "ID" is a unique field (I guess it's probably a bad idea for me to assume). If he uses the code

WHERE (((CSNotesDetail.ID) In (Select Top 4 [ID] From CSNotesDetail Where [ID]=[CSNotesDetail].[ID] Order By [ID] Desc)));

and ID is a unique field in the notes table, my assumption is that he will only get one result back.

I tried this out with a mock db. I got further (under my assumptions) by putting the invoice number in the subquery, which gives me 4 results (sorted on the unique ID field).

Unfortunately, I am not good enough to figure out how to answer the real question. I've tried several combinations in a mock db and it eludes me in the time I have available.

 

[pbaldy]

Gotcha. I'm not that good either, which is why I asked for a sample db. I just put this type of thing into a vehicle maintenance db, but the OP's structure is certainly different, so posting that wouldn't help them.

 

[raskew]

Hi -

Here is a sample query based on Northwind's Products table. It returns the top 5 records for each category (e.g. Beverages, Condiments, etc.) based on units in stock. It's been tested and it works.

SELECT DISTINCT a.CategoryID, a.ProductName, a.UnitsInStock
FROM Products AS a 
LEFT JOIN Products ON a.CategoryID = Products.CategoryID
WHERE (((Products.UnitsInStock) In (SELECT
    Top 5 [UnitsInStock] 
FROM
   Products 
WHERE
   [CategoryID]= a.[CategoryID] 
ORDER BY
   [UnitsInStock] Desc)))
ORDER BY a.CategoryID, Products.UnitsInStock DESC;

You might try copying it to a new query, replacing table, field names, top N to conform to your needs.

HTH - Bob