Pulling Most Recent Records for Each Company

[rheide]

Hello All,
I've searched and searched and can't find anything but it might be because I'm not sure what to search for.

Here is what I'm trying to do. (I'm simplifying this to make it easier to discuss)
I have a table that looks like this:
Company Date Sales
1 5/5/07 $1,200.00
1 6/5/07 $4,200.00
1 7/5/07 $2,200.00
1 8/5/07 $4,300.00
2 5/5/07 $1,200.00
2 6/5/07 $4,200.00
2 7/5/07 $2,200.00
2 8/5/07 $9,200.00
2 9/5/07 $1,200.00

I need to run a query that grabs the last 3 months for each company.

So the correct output from the above table would be:
Company Date Sales
1 6/5/07 $4,200.00
1 7/5/07 $2,200.00
1 8/5/07 $4,300.00
2 7/5/07 $2,200.00
2 8/5/07 $9,200.00
2 9/5/07 $1,200.00

Any help would be appreciated. I'm sure this has been asked in the past so even if you could help me with a keyword to search for, I would be greatful.

Thanks in advance,
Randy

 

[boblarson]

First of all, it is very, very bad to have a field named with an Access reserved key word (i.e. DATE). If at all possible you should rename this field ASAP otherwise you may find yourself having problems, including when trying to pull this data.

Also, are you wanting the last 3 FULL months, or just 3 months back from today?

 

[neileg]

If you only have one record per month you could use a Top Values query.

 

[rheide]

Hello All,
Per the comment about the DATE field, I just made that up, my table is completely different, I was just using this as an example (I won't use it next time though, good point).

Each month we add a new record. So for July, it may be July 3, 2007, and for August it might be August 5, 2007 (different for all companies). They are all around the same day give or take a few.

I just need the latest 3 records for each company.

Does that help any?

Thanks in advance and have a great day,
Randy

 

[ByteMyzer]

You might try something like:

SELECT T1.*
FROM MyTable AS T1
WHERE T1.Date IN
 (SELECT TOP 3 T2.Date
  FROM MyTable AS T2
  WHERE T2.Company = T1.Company
  ORDER BY T2.Date DESC
 )
;