Need a query to establish the most recent of three type of records

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

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

The query is used to return the last price paid for an item amount existing Order, Quote and Invoices. (i.e. is can return up to 3 records or as few as none)
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.

WilbertWaterburyAuthor Commented:
Clarification: The last sentence of my question should have read:

The query is used to return the last price paid for an item among existing Order, Quote and Invoices. (i.e. is can return up to 3 records or as few as none)
0
Rey Obrero (Capricorn1)Commented:
select * from tableName
where [date] in (select max([date]) from tableName as A where ID=A.ID) and tablename.type in ("O","Q","I")
0
WilbertWaterburyAuthor Commented:
Thanks for the response.  In the first sentence of my description I said:

"a result set that contains the last dated record for an item for each of type O, Q and I "

my last sentence may have obscured that point, but I need to have the last type O, Q and I if they exist. I should be able to get 3 resulting records if there are all three types in the table.  Your solution returns no more than 1 record I believe.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

WilbertWaterburyAuthor Commented:
An example would be helpful I think:

ID Date   Item Type Amount
01 1/1/08 Ball I    100    
02 1/2/08 Ball I     50
03 1/1/08 Bone O    100
04 1/2/08 Bone O    200
05 1/5/08 Bell Q    300
06 1/3/08 Bell Q    200

Result should be
02 1/2/08 Ball I     50
04 1/2/08 Bone O    200
05 1/5/08 Bell Q    300

Showing the amounts for the most recent types
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might try a Union query:

SELECT Top 1 * FROM Table1 WHERE Type='I'
UNION
SELECT Top 1 * FROM Table1 WHERE Type='O'
UNION
SELECT Top 1 * FROM Table1 WHERE Type='Q' ORDER BY [Date] DESC

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
Rey Obrero (Capricorn1)Commented:
try this

select * from tableName
where [date] in (select max([date]) from tableName as A where ID=A.ID and type=A.type) and tablename.type in ("O","Q","I")
0
Rey Obrero (Capricorn1)Commented:
so, you don't need to get latest date per record and type

select * from tableName
where [date] in (select max([date]) from tableName as A where [type]=A.[type]) and tablename.[type] in ("O","Q","I")
0
WilbertWaterburyAuthor Commented:
capricorn1:
your second suggestion ran and ran and ran.  I let it run for a trillion bytes and the db is only 200 MB.

The actual query looks like this using your template:

SELECT *
FROM tblSO_SalesOrderHistory
WHERE tblSO_SalesOrderHistory.XtionDate In (select max([XtionDate]) from tblSO_SalesOrderHistory as A where SalesOrderNo=A.SalesOrderNo) AND tblSO_SalesOrderHistory.OrderStatus In ("O","Q","I");

Your last suggestion appears the same as the first returning only one record.
0
WilbertWaterburyAuthor Commented:
There's a part two to this question.  Please watch for it. Thanks a lot.
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.