VBA V SQL Code Disparity

[Shane]

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!

[Ian Mac]

On 2002-04-16 05:05, Shane wrote:
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!

did you design it in Access?

Try putting it directly in the SQL view as it is.

[Shane]

Hi Ian. Yes I designed the MS Access query using the query "design view" window. I did as you suggested copyning the raw SQL code into the "SQL view" of MS Access but to no avail. I believe Access uses a slightly modified form of SQL. Any other ideas?

[Ian Mac]

On 2002-04-16 09:06, Shane wrote:
Hi Ian. Yes I designed the MS Access query using the query "design view" window. I did as you suggested copyning the raw SQL code into the "SQL view" of MS Access but to no avail. I believe Access uses a slightly modified form of SQL. Any other ideas?

Access shouldn't use slightly modified SQL (it does in terms of it's help and support), it does use MS Query ideals when building a 'Designed' Query, Hence all the (((((()))()))()()'s (annoying!).
I'm just wonding if when you used your SQL in 'SQL' you get any errors (if so what?).

BUT!. I don't think we should continue this, on this board, you can e-mail me with any further questions if you want. I'm not the greatest at SQL but I'm willing to give it a shot. also via e-mail I'll send you some other places to look.

til then, how's the craic?