update query help needed

[techexpressinc]

I am trying to update counts in a table. But I can not get the update to run. Attached is a snapshot. Thx for any help. Russ

code -
UPDATE [WeekBill Of Draft Billing query-cnt-families-per-agency2] INNER JOIN [Report-Family-Cnt] ON [WeekBill Of Draft Billing query-cnt-families-per-agency2].Address = [Report-Family-Cnt].Agency SET [Report-Family-Cnt].FamilyCount = [WeekBill Of Draft Billing query-cnt-families-per-agency2.CountOfFAMILY NUMBER-CR]
WHERE ((([Report-Family-Cnt].Agency)=[WeekBill Of Draft Billing query-cnt-families-per-agency2.Address]));

 

[Galaxiom]

[WeekBill Of Draft Billing query-cnt-families-per-agency2].[Address]

It appears you have left out the square brackets marked in red.

However you definitely need to rationalise what is the worst name I have ever seen for an object.
Firstly shorten it. Secondly leave out the spaces. Thirdly don't use special characters like the minus sign. Some people use underscores but I try to avoid them too.

Use name styles like DraftBilling.

It also appears you are intending to store the count of records as a value in a table. This is not normally done but calculated as required unless the calculation is very time consuming.

 

[techexpressinc]

Re: "Operation must use updateable query" - ongoing

Thank you for the good advice on the names. I have updated the names. The update query is still failing with the error message: "Operation must use updateable query".

The query that calculates the numbers that goes into the update query runs good.

I have attached a new snapshot.

Russ at Scaninc.org

 

[Galaxiom]

Queries that included aggregates (AKA Totals) are not updateable.
Write the aggregate values to another table then update your final table form there.

 

[techexpressinc]

GREAT advice - I am working without a manual intervention. I build the counts into a table. Thx.

SELECT WeekBillDraftCntFamiliesPart2Qry.Agency, WeekBillDraftCntFamiliesPart2Qry.FamilyCntNumber INTO weekbillfamilycnts
FROM WeekBillDraftCntFamiliesPart2Qry;