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

    HARD Query question (Access2000)

    Is it very hard to pull a value from a query into another query and use that value to pull records from a table? PLEASE PLEASE PLEASE tell me how I could do this?
    This is my situation; I have two tables, TBL1 containing original records, TBL2 containing the amended records. TBL1 & TBL2 are linked via Tnum. In QRY1 I

  2. #2
    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: HARD Query question (Access2000)

    Hi,
    From what you describe, I think all you want to do is base QRY2 on TBL2 and QRY1, with those 2 linked by Tnum. That way you will only get records from TBL2 where the Tnum appears in QRY1.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: HARD Query question (Access2000)

    I tried to linking QRY1 to TBL2 in QRY1 but it still doesn't help. I'm confused.

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

    Hi,
    I presume that's a typo and you did the link in QRY2?
    Does your query grid should look something like the attached?
    Attached Images Attached Images
    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: HARD Query question (Access2000)

    ya it does look like that. It doesn't give me any error message when I run it, neither does it select any records from the table. I am VERY sure that I have some amended records in the table.

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

    Have you checked that:
    1. QRY1 is returning data?
    2. There aren't any additional criteria in QRY2?
    3. There are amended records which match your date criteria in QRY1?
    I ran a quick test just before posting and it worked as expected in my test db.
    Hope that helps.
    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: HARD Query question (Access2000)

    Ya, QRY1 is returning data and there are amended records in the table for that date. Im sending you the SQL code for both my qeuries.
    QRY1:
    SELECT Trade.tnum, IIf([fund]="SFM_1","QP-01","QP-02") AS Account, 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, Nz(IIf(([TType]="Buy" Or [TType]="L" Or [TType]="Buy Cover" Or [TType]="BC"),(([q]*[tp])+[tc]+[tai]+[tax]),(([q]*[tp])-[tc]-[tai]-[tax]))) AS NetAmount, 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)=#9/5/2001#) AND ((Trade.fund)="SFM_1" Or (Trade.fund)="SFM_2")) OR (((Trade.td)=#9/5/2001#) AND ((Trade.fund)="SFM_1" And (Trade.fund)="SFM_2"));

    QRY2:
    SELECT TSFM.tnum, IIf([TradeAmendments.fund]="SFM_1","QP-01","QP-02") AS Account, TradeAmendments.id, Null AS [LOCAL], TradeAmendments.cpty, TradeAmendments.clr2, TradeAmendments.td, TradeAmendments.settles, TradeAmendments.q, TradeAmendments.tc, TradeAmendments.tp, TradeAmendments.tai, TradeAmendments.tax, ([TradeAmendments.cancel]="False"," ","x") AS CANCELLED
    FROM TradeAmendments INNER JOIN TSFM ON TradeAmendments.tnum = TSFM.tnum;

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

    Hi,
    I'm assuming that QRY2 should have read:
    iif([TradeAmendments.cancel]="False"," ","x") AS CANCELLED
    rather than just
    ([TradeAmendments.cancel]="False"," ","x") AS CANCELLED?
    If you look at the amendment records for that date, do their Tnums definitely appear in QRY1? If so, try deleting QRY1 from the QRY2 definition and check if QRY2 produces records OK. If it does, would it be possible to post a sample of your database as I can't see anything obviously wrong with what you've posted.
    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: HARD Query question (Access2000)

    ya, the tnums appear in QRY1. I did what you wanted me to do and QRY2 produces records ok but as soon as i relink it with QRY1 it shows empty feilds. my database is too big to put it up on this forum so i'll try to send it to your personal e-mail account, if you have one that is.

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

    Re: HARD Query question (Access2000)

    Hey Rory,
    Guess what? for some strange reason, it works now. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    Thank you sooooooooooooo much, I don't know What I would've doone without you. <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    This is for you <img src=/S/trophy.gif border=0 alt=trophy 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
  •