02-19-14, 18:10 #1Registered User
- Join Date
- Feb 2014
Unanswered: Getting a field to update based on a calculation
I am a novice user so please bear with me. I have a database with a table of students and a table of payments for a school trip (simple). I am trying to update the fully paid field in my student table by using an update query based on a sum that calculates how much each student has paid. I am getting an error of the application must use an updateable query. After a bit of research i have found that i cannot do this in access so i've tried to think of a few ways around it.
I have a form to collect payments based on studentID (combo box linked to query to show only those who havent paid the full amount of £400) and then a vairety of calculations - one called txtTotalPaid which works out for me the total paid - it does this correctly on the form so is there a way i could use this text box on the form to update the field in the student table???
Do i need to calculate the sum and store it in a table first??? If so how would i go about doing this??
Any help will be gratefully received
02-20-14, 03:53 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
no the sum is derived data, so normally you'd calculate the balance in a query. one of the main reasons for doing this is that if you store the balance then soemone can edit the balance directly. if you calculate the balance when required then your balancer reflects the data in the system at present. whether its relevant to you to your project is a moot point.
not storing derived data is one of the cardinal rules in a realtional database, but like all cardinal rules it depends, you can overrule or ignore such rules if you so decide for performance or other reasons.
you can do this in Access
the reason for 'I am getting an error of the application must use an updateable query.' in your query is that in order to update a row the query must have the means of uniquely identifying the row to be updated, usually that means the primary key for the row.
what Id suggest you do consider an alternative approach, fire of a query that updates the balance AFTER you have updated the row.
place some code in the detail AFTER UPDATE event which calculates the new balance.
the code calls an updtae statement
strSQL = "update mytable set mybalance = sum(transactions) where myprimarykey = 'blah'"
heck I'd push that into a function and call it in the forms on current event (so you alwasy have the correct balance when the row is dispalyed, AND i'd do it after updtae so you always store the most current value, assuming that you MUST store the balance. personlly I wouldn'tI'd rather be riding on the Tiger 800 or the Norton
02-20-14, 07:18 #3Registered User
- Join Date
- Feb 2014
Thanks for that but i can only update the fully paid field once i have added together all the instalements and only then if they >=400 can i update the row?
Im doing a task set by an exam board and i dont normally teach Access - hence im out of my depth. The task asks:
A form is needed so that Charles can add details of payments.
(i) Create a query that will identify the students who have not fully paid for their trip.
(ii) Create a data entry form for Charles to use to add payments.
Charles should be able to:
select the StudentID from a dropdown box, see the total amount already paid, enter the payment amount.
The StudentID dropdown box should use the query created in part (i) as its source.
(iii) Generate the new PaymentID, which is always one number higher than the last, and display this on the form.
(iv) Generate and display the new balance.
(v) The automated method of storing and updating should include:
saving the payment details
updating the FullyPaid field, if appropriate
displaying a message to Charles when the save has been carried out.
I have managed to do everything except the updating the fully paid field
I dont know code so if possible im better through macros / queries if thats possible.