Unanswered: Updating a table field from a query that uses SUM
Sorry for spamming, I just don't know how to append threads to include an attachment.
Included is the Database I am making for my cousins garage.
Heres the structure of my table (the columns in question)
Cost - Currency
Paid - Yes/No
Used - Yes/No
After a customer has paid the Paid tick box is ticked. When the user wishes to find out the balance they run a set of queries. The first query will SUM Cost WHERE Paid=True AND Used=False (The 'Used' column is in place so that the same entry is not added to the balance twice) and then take the SUM value and insert it into a table (destination will be a table called balance). The second query will set all of the entries used and set there 'Used' column to TRUE.
Don't do this. Don't try to store dynamic data in static fields, that's a bad, bad, bad idea.
In general, you should never store redundant data, or data that can be derived from already existing data. Anytime you want to get the "balance", you run the query. That's what queries are for. They exist so you don't have to store that number somewhere else.