Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Help (Access2000)

    Can anybody see any error in the following SQL code? What should I replace the bolded "Or" with? It gives me an error on that "Or"
    SELECT TradeAmendments.amendnum, TradeAmendments.tnum, Max(TradeAmendments.amended) AS amended, TradeAmendments.id, TradeAmendments.cpty, TradeAmendments.clr2, TradeAmendments.tt, TradeAmendments.td, TradeAmendments.settles, TradeAmendments.tccy, TradeAmendments.q, TradeAmendments.tc, TradeAmendments.tp, TradeAmendments.tai, TradeAmendments.tax, TradeAmendments.moneyspot, TradeAmendments.ctc, TradeAmendments.notes, TradeAmendments.cancel, TradeAmendments.fund, Trade.splitref
    FROM( (Trade LEFT JOIN TradeAmendments ON Trade.tnum = TradeAmendments.tnum) Or (RefMaxOfAmendedForSFMAmend LEFT JOIN TradeAmendments ON RefMaxOfAmendedForSFMAmend.MaxOfamended = TradeAmendments.amended))
    WHERE (((TradeAmendments.fund)="SFM_1" Or (TradeAmendments.fund)="SFM_2"))
    GROUP BY TradeAmendments.amendnum, TradeAmendments.tnum, TradeAmendments.id, TradeAmendments.cpty, TradeAmendments.clr2,TradeAmendments.tt, TradeAmendments.td, TradeAmendments.settles,TradeAmendments.tccy, TradeAmendments.q, TradeAmendments.tc, TradeAmendments.tp, TradeAmendments.tai, TradeAmendments.tax, TradeAmendments.moneyspot, TradeAmendments.ctc, TradeAmendments.notes, TradeAmendments.cancel, TradeAmendments.fund, TradeAmendments.splitref

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

    Re: SQL Help (Access2000)

    Try changing that phrase to this:

    (((TradeAmendments.fund)="SFM_1") Or ((TradeAmendments.fund)="SFM_2"))

    and see if it likes that better. If you still have problems, remove the parens from the entire WHERE clause and see if that clears it up. The parens aren't really necessary unless you need to group phrases within the WHERE clause, so it won't hurt anything if you take them on in this particular piece of SQL.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Help (Access2000)

    That is not the "Or" Im having problem with, this is the line which gives me problem:
    FROM( (Trade LEFT JOIN TradeAmendments ON Trade.tnum = TradeAmendments.tnum) Or (RefMaxOfAmendedForSFMAmend LEFT JOIN TradeAmendments ON RefMaxOfAmendedForSFMAmend.MaxOfamended = TradeAmendments.amended))

    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL Help (Access2000)

    What exactly are you trying to do with that? It doesn't make a lot of sense to me.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: SQL Help (Access2000)

    You can't do a conditional join, which is why you're having a problem.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Help (Access2000)

    Im trying to stop me from creating too many queries to do a task, but it seems like I have to have few queries to do what I want to do. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Thanx guys for trying to help.

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

    Re: SQL Help (Access2000)

    If you're creating SQL in code, you're still creating queries, you just aren't saving them. The problem is, that queries created on the fly don't get optimized, so they're always slower than the saved variety. If you aren't proficient in SQL itself, use the query grid to design your queries and then copy the SQL if you must and paste it into your code. You get into trouble when you try to tweak it directly.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Help (Access2000)

    Thanx Charlotte. I tried to minimise saving loads of quries to a task but it seems that the only way to do that task is to save few queries.
    Thanx
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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