Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Summing across tables (2k)

    My challenge this evening involves two simple tables and an update query. I have the following two tables with the fields below each table name.

    tblCosts
    fkSum
    strType
    curAmount

    tblSummary
    pkey
    curCards
    curMachines


    For each pkey in tblSummary that has records linked on fkSum in tblCosts, I need to update the tblSummary.curCards with the sum of tblCosts.curAmount where tblCost.strType="Blackjack", In english, I need to take the sum of the amounts in tblCosts and update tblSummary with the results.

    In a attempt to accomplish this I created the below select query:

    SELECT Sum(tblCost.curAmount) AS SumOfcurAmount, tbl.fkSum
    FROM tblCost INNER JOIN tblSummary ON tblCost.fkSum = tblSummary.pkey
    WHERE (((tblCost.strType)="Black Jack"))
    GROUP BY tblCost.fkSum;

    and saved it as sqSumCostsCards. This query does in fact provide me with the correct dollar amount for each "key" record. I then use the above query in the below query.


    UPDATE tblSummary INNER JOIN sqSumCostsCards ON tblSummary.pkey = sqSumCostCards.fkSum SET tblSummary .curCards = [sqSumCostsCards ]![SumOfcurAmount];

    When I run the second query, I get a message stating that "Operation must use an updatable query"

    Is there a better approach? What am I missing?

    Thanks in advance for your assistance.

    Ken

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

    Re: Summing across tables (2k)

    I'll start with the standard remark: why do you want to store derived information in a table? You already have a query to calculate it. The results of the query will always be up-to-date.

    If you really need to store the summary info, you don't need tblSummary in sqSumCostsCards. Just

    SELECT Sum(curAmount) AS SumOfcurAmount, fkSum
    FROM tblCost
    WHERE strType="Black Jack"
    GROUP BY fkSum;

    is sufficient.

    Since a Totals query is never updatable, you can't create an update query with a join to a Totals query. You can use DLookup to retrieve the value from the query instead:

    UPDATE tblSummary SET tblSummary.curCards = DLookUp("SumOfcuramount","sqSumCostsCards","fkSum= " & [pkey]);

    Note: this assumes that pkey is numeric; if it is text, the where condition in DLookup becomes

    "fkSum=" & Chr(34) & [pkey] & Chr(34)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Summing across tables (2k)

    Hans,

    RE: Why?
    This is a one time deal. Going from an old system to a new one where they will no longer be capturing individual details, and only the lump sums will be provided in the future. (An interdepartmental deal so to speak)

    Thanks for the help. You are awesome!

    Ken

Posting Permissions

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