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

    query question (Access2000)

    SELECT tnum, id, cusip, isin, sedol, cpty, clr2, TType, td, settles, q, tc, tp, tai, tax, cancel, fund
    FROM qryTrade UNION SELECT tnum, id, cusip, isin, sedol, cpty, clr2, TType, td, settles, q, tc, tp, tai, tax, cancel, fund
    FROM qryTradeAmendments
    WHERE (((qryTrade.fund)=

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query question (Access2000)

    Check if fund exist in qryTrade and in qryTradeAmendments
    Francois

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

    Re: query question (Access2000)

    it sure does.
    SQL for qryTrade:
    SELECT Trade.tnum, Trade.id, Sec.cusip, Sec.isin, Sec.sedol, Trade.cpty, Trade.clr2, tblTradeTransactionType.TType, Trade.td, Trade.settles, Trade.q, Trade.tc, Trade.tp, Trade.tai, Trade.tax, Trade.cancel, Trade.fund
    FROM Sec INNER JOIN (tblTradeTransactionType INNER JOIN Trade ON tblTradeTransactionType.OrigTT = Trade.tt) ON Sec.id = Trade.id
    WHERE (((Trade.td)=#8/22/2001# Or (Trade.td)=#8/22/2001#));

    SQL for qryTradeAmendments:
    SELECT TradeAmendments.tnum, TradeAmendments.id, Sec.cusip, Sec.isin, Sec.sedol, TradeAmendments.cpty, TradeAmendments.clr2, tblTradeTransactionType.TType, TradeAmendments.td, TradeAmendments.settles, TradeAmendments.q, TradeAmendments.tc, TradeAmendments.tp, TradeAmendments.tai, TradeAmendments.tax, TradeAmendments.cancel, TradeAmendments.fund
    FROM ((tblTradeTransactionType INNER JOIN TradeAmendments ON tblTradeTransactionType.OrigTT = TradeAmendments.tt) INNER JOIN Sec ON TradeAmendments.id = Sec.id) INNER JOIN qryTrade ON (TradeAmendments.tnum = qryTrade.tnum) AND (Sec.id = qryTrade.id)
    WHERE (((TradeAmendments.td)=#8/22/2001#));

  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: query question (Access2000)

    On an unrelated note, how can qryTrade.fund equal "SFM_1" and "SFM_2"? (same question applies to qryTradeAmendments.fund)
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: query question (Access2000)

    it would pull RECORDS which are for fund SFM_1 and SFM_2, two different funds each record would have either SFM_1 or SFM_2 and I want this query to pull out either records for SFM_1 or SFM_2 or for both SFM_1 and SFM_2. Do I make any sense? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  6. #6
    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: query question (Access2000)

    Using
    (((qryTrade.fund)=
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: query question (Access2000)

    Nah, it doesn't help. I tried it but it doesn't make any difference.
    Thank you for trying.

  8. #8
    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: query question (Access2000)

    It wasn't actually intended to help with the prompting problem, I was just curious as to why you had the AND criteria in there! This should work for your UNION query:
    SELECT tnum, id, cusip, isin, sedol, cpty, clr2, TType, td, settles, q, tc, tp, tai, tax, cancel, fund
    FROM qryTrade WHERE ((qryTrade.fund) IN (
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: query question (Access2000)

    no, that doesn't work neither. now it's prompting me to enter parameter for SFM_1.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query question (Access2000)

    Seems you have forgot some quote around SFM_1
    Francois

  11. #11
    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: query question (Access2000)

    Curious. Are you running this from a query window or from code?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: query question (Access2000)

    From code. SQL view.

  13. #13
    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: query question (Access2000)

    Those are two different things. I take it you mean SQL view in a query window? In which case, are there definitely quotes around the "SFM_1" as Francois suggested? If you are running it from a code module, then try replacing the double quotes around "SFM_1" and "SFM_2" with single quotes (i.e. 'SFM_1' and 'SFM_2')
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: query question (Access2000)

    THANK YOU GUYS, it was the quotes, I didn't read Francois reply, I only read the last ones, stupid me <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    THANKS AGAIN for both of you.

Posting Permissions

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