Hi! I know this may not be the best forum for this query, but hey, I've had all queries answered to date so why not!
I have an SQL script that, run on Rapid SQL, gives me the result I expect. See script below:
select distinct a.or_id,l.line_id LINE, a.ax_cli CLI, l.ts_id TARIFF,t.ts_name NAME,l.lt_validfrom DATE
from line_tariff_structures l, order_line o, axe_line a, tariff_structures t
where a.or_id=o.or_id
and l.line_id=a.ax_id
and o.cs_id=22136
and t.ts_id=l.ts_id
and l.lt_validfrom in (select max(lt_validfrom) from line_tariff_structures where line_id=l.line_id)
I have designed what I thought to be an identical query in MS Access but it does not give me the same result (returns mulitple tariffs per line rather than merely the latest tariff associated with that line). MS Access query code below:
SELECT DISTINCTROW dbo_order_line.cs_id, dbo_axe_line.ax_cli, Max(dbo_line_tariff_structures.lt_validfrom) AS MaxOflt_validfrom, dbo_tariff_structures.ts_name
FROM (dbo_tariff_structures INNER JOIN (dbo_line_tariff_structures INNER JOIN dbo_axe_line ON dbo_line_tariff_structures.line_id = dbo_axe_line.ax_id) ON dbo_tariff_structures.ts_id = dbo_line_tariff_structures.ts_id) INNER JOIN dbo_order_line ON dbo_axe_line.or_id = dbo_order_line.or_id
GROUP BY dbo_order_line.cs_id, dbo_axe_line.ax_cli, dbo_tariff_structures.ts_name
HAVING (((dbo_order_line.cs_id)=22136));
Can anyone figure out the flaw in the MS Access code here. It's got me baffled - hope I haven't baffled you!
I have an SQL script that, run on Rapid SQL, gives me the result I expect. See script below:
select distinct a.or_id,l.line_id LINE, a.ax_cli CLI, l.ts_id TARIFF,t.ts_name NAME,l.lt_validfrom DATE
from line_tariff_structures l, order_line o, axe_line a, tariff_structures t
where a.or_id=o.or_id
and l.line_id=a.ax_id
and o.cs_id=22136
and t.ts_id=l.ts_id
and l.lt_validfrom in (select max(lt_validfrom) from line_tariff_structures where line_id=l.line_id)
I have designed what I thought to be an identical query in MS Access but it does not give me the same result (returns mulitple tariffs per line rather than merely the latest tariff associated with that line). MS Access query code below:
SELECT DISTINCTROW dbo_order_line.cs_id, dbo_axe_line.ax_cli, Max(dbo_line_tariff_structures.lt_validfrom) AS MaxOflt_validfrom, dbo_tariff_structures.ts_name
FROM (dbo_tariff_structures INNER JOIN (dbo_line_tariff_structures INNER JOIN dbo_axe_line ON dbo_line_tariff_structures.line_id = dbo_axe_line.ax_id) ON dbo_tariff_structures.ts_id = dbo_line_tariff_structures.ts_id) INNER JOIN dbo_order_line ON dbo_axe_line.or_id = dbo_order_line.or_id
GROUP BY dbo_order_line.cs_id, dbo_axe_line.ax_cli, dbo_tariff_structures.ts_name
HAVING (((dbo_order_line.cs_id)=22136));
Can anyone figure out the flaw in the MS Access code here. It's got me baffled - hope I haven't baffled you!