[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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)
0
WilbertWaterbury
Asked:
WilbertWaterbury
  • 5
  • 3
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now