Results 1 to 9 of 9
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Data pass through queries - Sage Line 50 (Access 2000)

    I am using Access and extracting data from Sage Line 50.
    I have been trying to write a query to Sage that uses DISTINCT to remove duplicate records.
    This does NOT appear to work.

    Can it be done.
    I have an alternative at the moment where the Pass through query returns ALL rows and another query then removes duplicates.
    but from an academic point of view, does Sage support this query technique ?
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    It's a bit hard to answer your question without knowing what Sage uses as a data store. If they're using SQL Server or MySQL or something similar, you may have to query using the SQL language that goes with the database server. If a passthrough query works, then it's likely that the database server doesn't support the Jet SQL features you are trying to use. For instance, DISTINCT is not a recognized keyword in SQL Server, it's a Jet SQL keyword.
    Charlotte

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    I am sure you are right, it is not supported by Sage.
    Ill use a Group By query instead.
    On another note, I tried DISTINCT with SQL Server 7 and SQL Server 2000
    Running directly on a server stored procedure and also on a data pass through query.
    It worked for me.
    Andrew

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    <<On another note, I tried DISTINCT with SQL Server 7 and SQL Server 2000
    Running directly on a server stored procedure and also on a data pass through query.
    It worked for me>>


    As I recall, Jet uses both DISTINCTROW and DISTINCT, and SQL Server uses DISTINCT. DISTINCT eliminates duplicate rows (values in Access), while DISTINCTROW eliminates records that are duplicates in the underlying data source.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    You're right, Wendell. *I* knew what I meant! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24>
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    Well I had to look up the correct definitions in Help and Books on Line before I replied - has to do with age I think - that or too many of Mrs. Lovette's hot pies <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    What, "Priest, have a little priest ..."? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    Well done - we've started rehearsals as you can tell.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data pass through queries - Sage Line 50 (Access 2000)

    That was my absolute favorite number from Sweeney Todd! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Charlotte

Posting Permissions

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