I feel like this should be so easy, but I can't seem to make it work.
I have a table called Current. The table includes an On/Off field called Display, and a field called "Name", which is usually the month of data in the row. I have an action to append the table to add a row with the name of "Average" at the bottom. There are 25 remaining fields which are the sales data for each of 25 possible reporting days in a month for every region (9 regions * 25 days = 225 additional fields)
The user can select or de-select a month's sales data by checking or un-checking the On/Off field in the table. This data is displayed via subform in a main form with a command button called "Recalculate". When Recalculate is clicked, it alters the data in the "Average" row with the new average of the months that are selected ON in the display field. So far I can get this to work with SQL in VBA, but it runs for all 225 sales fields individually and takes a bit of time.
I'd like to cut down on the time it takes to Recalculate, but I can't seem to wrap my head around how to do this in an Update query (or queries; perhaps one per region). The typical error I get is "Operation must use an updateable query". (The Current table is certainly updateable). Anyone have any suggestions?
ETA: I should have mentioned that the error "Too many fields defined" occurs just attempting to straight-up average all the fields at once, and I only want one "Average" row.
I have a table called Current. The table includes an On/Off field called Display, and a field called "Name", which is usually the month of data in the row. I have an action to append the table to add a row with the name of "Average" at the bottom. There are 25 remaining fields which are the sales data for each of 25 possible reporting days in a month for every region (9 regions * 25 days = 225 additional fields)
The user can select or de-select a month's sales data by checking or un-checking the On/Off field in the table. This data is displayed via subform in a main form with a command button called "Recalculate". When Recalculate is clicked, it alters the data in the "Average" row with the new average of the months that are selected ON in the display field. So far I can get this to work with SQL in VBA, but it runs for all 225 sales fields individually and takes a bit of time.
I'd like to cut down on the time it takes to Recalculate, but I can't seem to wrap my head around how to do this in an Update query (or queries; perhaps one per region). The typical error I get is "Operation must use an updateable query". (The Current table is certainly updateable). Anyone have any suggestions?
ETA: I should have mentioned that the error "Too many fields defined" occurs just attempting to straight-up average all the fields at once, and I only want one "Average" row.
Last edited:
