How to have a Select Query like Sql Server Cross Apply query in MS-Access?

[Paramu1973]

HI, I have 3 tables. I wish to select the latest Unit Rate from those tables....like SQL Cross Apply.Is it possible from Ms-Access? The below SQL-Query Select the latest unit_price, price_date from three tables..... Itemmst, Pomst, Grinmst Actually the query checks those three tables and will select the greatest Price_date and Unit_Rate

Select i.itm_code,i.itm_description,i.unit_measure,t.unit_rate,t.price_date from itemmst i cross apply ( select top 1 unit_rate,price_date from( select im.unit_rate,''as price_date,0 as ord from itemmst im where im.itm_code=i.itm_code and im.unit_rate is not null union all select unit_price as unit_rate,po_date as price_date,1 as ord from pomst where pomst.itm_code =i.itm_code and pomst.unit_price is not null union all select grinmst.unit_rate,grinmst.grn_date,1 from grinmst where grinmst.itm_code =i.itm_code and grinmst.unit_rate is not null )r order by price_date desc,ord desc )t

Output :- 1. Item1, Pencil, Nos, 25.00, 26/03/2015 => from Grimst 2. Item2, Pen, Nos, 22.00, 21/03/2015 => from Pomst 3. Item3, Paper, Nos, 65.00, 01/04/2014 => from Itemst Thanks:thumbsup:

[Afzaal Ahmad Zeeshan]

Short answer would be, yes you can use SQL-based commands for data in MS-Access, because MS-Access can also work with SQL commands. Please read this[^] for more.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~