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

  • Question

  • 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 for the helps


    U.PARANTHAMAN

    Tuesday, March 31, 2015 2:13 AM

Answers

  • Hi,

    let's use a simple example. I have no Access right no, I'll just give you an idea of how to change CROSS APPLY by a simple JOIN. I've done it in SSMS but it should be reproducible in Access. What I have as a sample data:

    declare @t table(itm_code nvarchar(10), itm_description nvarchar(10), 
    unit_measure nvarchar(3), unit_rate float, price_date datetime)
    
    insert into @t values('Item1', 'Pencil', 'Nos', 25.00, '2015-03-26')
    insert into @t values('Item1', 'Pencil', 'Nos', 24.00, '2015-03-25')
    insert into @t values('Item1', 'Pencil', 'Nos', 23.00, '2015-03-24')
    
    insert into @t values('Item2', 'Pen', 'Nos', 22.00, '2015-03-21')
    insert into @t values('Item2', 'Pen', 'Nos', 21.00, '2015-03-20')
    insert into @t values('Item2', 'Pen', 'Nos', 20.00, '2015-03-19')
    
    insert into @t values('Item3', 'Paper', 'Nos', 65.00, '2014-04-14')
    insert into @t values('Item3', 'Paper', 'Nos', 64.00, '2014-04-13')
    insert into @t values('Item3', 'Paper', 'Nos', 63.00, '2014-04-12')
    
    

    And that's the resulting query:

    select t.itm_code, t.itm_description, t.unit_measure, t.unit_rate, t.price_date
    from @t as t
    inner join (
    	select t1.itm_code, max(t1.price_date) as last_date
    	from @t as t1
    	group by t1.itm_code) as q1
    on q1.itm_code = t.itm_code and q1.last_date = t.price_date


    Andrey V Artemyev | Saint-Petersburg, Russia

    • Marked as answer by Paramu Wednesday, April 1, 2015 1:33 AM
    Tuesday, March 31, 2015 11:57 AM