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)
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)
select * from tableName
where [date] in (select max([date]) from tableName as A where ID=A.ID) and tablename.type in ("O","Q","I")
where [date] in (select max([date]) from tableName as A where ID=A.ID) and tablename.type in ("O","Q","I")
ASKER
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.
"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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
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")
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")
select * from tableName
where [date] in (select max([date]) from tableName as A where [type]=A.[type]) and tablename.[type] in ("O","Q","I")
ASKER
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.Xt
Your last suggestion appears the same as the first returning only one record.
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.Xt
Your last suggestion appears the same as the first returning only one record.
ASKER
There's a part two to this question. Please watch for it. Thanks a lot.
ASKER
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)