Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With Update Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following table with Charge records 101, and Payment records including Cash Records PCA and Check Records PCK. Thet are linked together by ID number

    I need help building an update query to update the Charge records with total Cash and Check totals from linked payment records

    Thanks, John

    <pre>
    ID Code Charge Cash Check
    1 101 20 0 0
    1 PCA 0 5 0
    1 PCK 0 0 10
    1 PCA 0 2 0
    1 PCK 0 0 3

    2 101 22 0 0
    2 PCA 0 7 0
    2 PCA 0 9 0
    2 PCK 0 0 6

    3 101 10 0 0
    3 PCA 0 10 0

    After Update Query charge records would look like this:

    ID Code Charge Cash Check
    1 101 20 7 13
    2 101 22 16 6
    3 101 10 10

    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Update Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You say 'update' - do you mean that you want to change the existing records with Code = 101? If so, should the records with other codes be deleted after the update?
    Why not calculate the aggregrate data in a totals query?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Update Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Yes I want to change the existing records with Code = 101 with total cash and total checks totals from linked payment records

    I do not want the payment records deleted

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Update Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Try the following, and take care not to run the query twice! (You're creating a dangerous situation)

    UPDATE tblTransactions SET [Charge] = DSum("Charge","tblTransactions","ID=" & [ID]), [Cash] = DSum("Cash","tblTransactions","ID=" & [ID]), [Check] = DSum("Check","tblTransactions","ID=" & [ID])
    WHERE [Code]="101"

    Replace tblTransactions with the name of the table. If it contains spaces or other 'strange' characters, put square brackets around the table name.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Update Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    It worked

    UPDATE tblTransactions SET [Cash] = DSum("Cash","tblTransactions","ID=" & [ID]), [Check] = DSum("Check","tblTransactions","ID=" & [ID])
    WHERE [Code]="101"

    Thanks, John

Posting Permissions

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