Updatable query

[Starscream]

Hello

I have a table with several criteria whose percentage I want to update every time I want to remove / add a client
Here’s my table

Criteria / Client / Percentage / Quantity
C0001 CLI0100 50% 20
C0001 CLI0200 50% 20
C0002 CLI0050 100% 35
C0003 CLI0042 33% 4
C0003 CLI0015 33% 4
C0003 CLI0400 33% 4
…

i want to create an “Update” Query that should update the “Percentage” field every time i add or remove a client (in accordance with the criteria). For instance, if i remove CLI0100 from C0001, the Query should automatically calculate C0001's percentage to 100%, since I now have that criteria (C0001) with only one Client (CLI0200), with Quantity = Total (for that criteria)
I tried to create this Update query while creating another query that could give me the total amount for each criteria (Sum), so that I could divide Quantity / SUM Quantity to give me the new Percentage. Unfortunately, every time I try to run this query the access says that “operation must use an updateable query”.


I tried to look for help on the web and almost every page says that I cant run this if I have a query with SUM. Is that correct?
Without resorting to SQL, how can I run this update query? Is there a way?

Thanks in advance
Bruno

[Robeen]

I think you should get your percentages from queries - rather than updating the Table the way you are suggesting.
You should have only 'static' data [data which is not dependent on other data in the Table for its value] - in your Table.
Then - you could calculate your percentages with your Query[s].

What you are wanting to have is a 'Calculated Field' in your Table - which is not advisable from the perspective of database design.
It is possible to update the Table the way you suggest - but I would do that using VBA Code with embedded SQL to Update the Table . . . and it is really not necessary.

Let us know if you need more help/suggestions.

[Starscream]

I agree with you from that perspective as well, but i have to do it this way, since "percentage" is also an input (and the most important one - "quantity" will be used to simulate only")
Thanks!

[Robeen]

1.

Without resorting to SQL, how can I run this update query? Is there a way?

An update query IS SQL.
Did you mean to say 'without resorting to VBA code'?

2.
Are you making changing in your Table [Eg: Deleting records] using a Form? Or are you making changes directly IN the Table?

3.
If you MUST have the value[s] in the Percentage Column changed each time a Client is either added or removed, then I would suggest you put some VBA Code in a Form - behind the command button that deletes rows of data.

The VBA Code would do the following:
1. Delete the row of data that you want to get rid of.
2. Determine how many rows of data with the Criteria that you just deleted still remain in the Table [using an aggregate function in a SQL Statement in the Code].
3. Calculate the Percentage based on the number of rows remaining for that Criteria [1 Row = 100%, 2 Rows = 50%, 3 Rows = 33 1/3% . . .].
4. For each of the remaining rows with the same Criteria as the one just deleted - insert the percentage from step 3 into the Percentage column.

Hope this helps.