Need a query to establish the most recent of three types of records for every item for every customer

This is an extension of this answered question http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23150750.html#a20860782 

I need to retrieve a result set that contains the last dated record for every item for every customer for each of type O, Q and I records. I'm using Access and a SQL response or Access Query would be acceptable.  The "for every item for each customer" is the new part.

Consider the table has an ID, Date, Customer, Item  Type and Price fields. The result set must contain the most recent dated record for each customer for every item for all records type O, Q and I and must include the Amount field with each.

The query is used to return the last price paid (amount) for all items for all customers amoung existing Order, Quote and Invoices. (i.e. is can return up to 3 records per item per customer or as few as none)

Here's an example:
ID Date   Customer Item Type Amount
01 1/1/08 00000001 Ball I    100    
02 1/2/08 00000001 Ball I     50
03 1/3/08 00000001 Ball Q    200
04 1/4/08 00000001 Ball Q    250
05 1/1/08 00000001 Bone O    100
06 1/2/08 00000001 Bone O    200
07 1/3/08 00000001 Bone I    250
08 1/4/08 00000001 Bone Q    325
09 1/5/08 00000001 Bell Q    300
10 1/3/08 00000001 Bell Q    200
11 1/1/08 00000002 Ball I    100    
12 1/2/08 00000002 Ball I     50
13 1/3/08 00000002 Ball Q    200
14 1/4/08 00000002 Ball Q    250
15 1/1/08 00000002 Bone O    100
16 1/2/08 00000002 Bone O    200
17 1/3/08 00000002 Bone I    250
18 1/4/08 00000002 Bone Q    325
19 1/5/08 00000002 Bell Q    300
20 1/3/08 00000002 Bell Q    200

Result should be not more han 3 records per Customer per Item:
Customer   00000001
02 1/2/08 00000001 Ball I     50
04 1/4/08 00000001 Ball Q    250

06 1/2/08 00000001 Bone O    200
07 1/3/08 00000001 Bone I    250
08 1/4/08 00000001 Bone Q    325

10 1/3/08 00000001 Bell Q    200

Customer   00000002
12 1/2/08 00000001 Ball I     50
14 1/4/08 00000001 Ball Q    250

16 1/2/08 00000001 Bone O    200
17 1/3/08 00000001 Bone I    250
18 1/4/08 00000001 Bone Q    325

20 1/3/08 00000001 Bell Q    200
WilbertWaterburyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
First Set up a Query
 called custMaxIQOItemDate

select customer
      ,item
      ,type
      ,Max(date) as "date"
 From YourTable
 Where type in ('I','Q','O')
 group by Customer,item  


then setup another query as

Select A.Customer
      ,A.Date
      ,A.Item
      ,A.Type
      ,A.Amount
  From YourTable as A
 Innner Join custMaxIQOItemDate as B
    on A.Customer=B.customer
   and A.item=b.item
   and A.type=b.type
   and A.date=b.date
 Order By A.Customer,A.Item,A.Type,A.Date Desc
0
WilbertWaterburyAuthor Commented:
Thanks LFS for your quick response.

Here's what happens...

Your second query requires that the Type field be present in the first query.  
After adding Type as a grouped field to the first query ("Group By" is the only wait it can be added) the results from the second query is only one record which is the most recent Dated record of the entire table.  This is also the same result of the first query when run by itself.
0
Rey Obrero (Capricorn1)Commented:
try this, change TableX with actual name of table

select *
from TableX
where tablex.[date] in (select max([date]) from tableX as A where tableX.Customer=A.customer and tablex.[Item]=A.[item]) and tablex.type in ("O","Q","I")
0
Rey Obrero (Capricorn1)Commented:
slight revision

select *
from TableX
where tablex.[date] in (select max([date]) from tableX as A where tableX.Customer=A.customer and tablex.[Item]=A.[item] and tablex.[type]=A.[type]) and tablex.type in ("O","Q","I")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WilbertWaterburyAuthor Commented:
Your slight revision was the answer I was looking for - it just runs a long time (1.5 hours).  Just tell me one thing - how did you learn SQL os well and how do I optimize this?

I've uploaded to my Web site the demonstration database that shows this answer. ( http://www.connexsoftware.com/EE_Question.mdb )

Now I just have to figure out what to do if two items are sold, ordered or quoted on the same day?#!@#!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.