Summing across tables (2k)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote