VBA V SQL Code Disparity

Shane

Board Regular
Joined
Apr 8, 2002
Messages
51
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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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?
 
Upvote 0
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!:mad:).
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top