Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA V SQL Code Disparity

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Have a good time......all the time"
    Ian Mac

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    "Have a good time......all the time"
    Ian Mac

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •