Results 1 to 4 of 4
  1. #1
    Topher
    Guest

    Update from Subquery

    Hi there <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    I am looking for a way to calculate and display some info from a few different tables. The setup is as follows
    Table A has PO items and their information and Table B has a list of Receiving Info and can have multiplie receiving recs for each PO item. receiving info such as the qty received on what date.

    I want to be able to list all items within an entered PO# with the total Qty received but when i try to do this with a query i can't seem to get it right. i can do this with VBA but i'd rather do it with a query like this

    Select A.PO#, A.Description, Sum (B.Qty Recieved)

    is this possiblein access?? or does anyone have any ideas??

    cheers,
    Topher

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update from Subquery

    I think this is the relevant kb article:

    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q171/9/55.ASP>http://support.microsoft.com/support/kb/ar...s/Q171/9/55.ASP</A>

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

    Re: Update from Subquery

    Paul,

    I don't think that was the link you meant to post, or else you posted it in the wrong thread, since ISAMs aren't the issue here.

    Topher,

    The question here is how you want to construct the query. You can use either a join on the two tables in a group by query, or you can use a simple select query on the PO table with a subquery. Sometimes subqueries are fast, but not always, so you'll need to try out both to see which works best for you. Here are your alternatives:

    #1: Group By query
    <pre>SELECT A.PO#, A.Description, Sum(B.QtyReceived) As Qty
    FROM A LEFT JOIN B ON A.PO# = B.PO#
    GROUP BY A.PO#, A.Description;</pre>


    #2: Subquery
    <pre>SELECT A.PO#, A.Description,
    (SELECT Sum(B.QtyReceived)
    FROM B WHERE B.PO# = A.PO#) AS Qty
    FROM A </pre>

    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update from Subquery

    You're absolutely right, Charlotte, I posted to the wrong thread. I was being interrupted constantly that day so I must have hit a wrong button somewhere. I wonder if I can find the thread I was trying to reply to.

Posting Permissions

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