Update Query Not Working Properly !

[hamxa7]

Hi All ...
I've been working on a stock control database for a pharmacy as a school project and have been right on track till now...
I've made an update query to update the stock in the stock table by adding the quantity ordered from the order form using the unique serial number assigned to each medicine as the criterion... (I know this isn't the best practice...)
It works fine when there two or less records are present regarding a serial number in the order table... but when there are more than two records presents it gives the following warning :-
"You are about to update "n" number of rows" where n is the number of records present - 1 ...
But it should give the following warning "ALWAYS" :-
"You are about to update 1 row"
After it gives the warning it updates the stock but does it in the following manner :-


Qty Available(Stock Table) = [Qty Available (Stock Table) + Qty Ordered (Order Form)] * n (number of rows shown in the warning) ...
I don't know what to do...

A sample of my database has been attatched...
-PS : Don't tell me to convert it to VBA because I have been told by my teacher not to use it as I will lose marks...

[June7]

The UPDATE wants to update every record that has a balance greater than 0. Remove Medicine Order Table from the query. Not only is it interfering, it is not relevant.

Use SetWarnings action in macro if you want to prevent the warning popups.

Saving calculated data (data dependent on other data) is usually a bad idea. The data can get out of sync. Queries could determine balances with aggregate calculations. However, those queries can be somewhat complex. I do suggest that saving the order Total Price is unnecessary. This is a simple calculation that can be done when needed.

You have the medicine Serial Number as the primary key in Stock table but you have combobox on form that saves the medicine Name into Order table. Then you have code saving the Serial Number. Why not set ControlSource to SerialNumber and BoundColumn property to 1, not 2?

I understand saving the price into Order table because prices can change but will the medicine name ever change? Why duplicate it into the Order table? If the name was changed, would it make sense to have it reflected in all order records?

[hamxa7]

Thanks for the reply...
I have removed the medicine name combo-box from the form ... and am now using a combo-box to determine the serial number ...I have also removed the orders window from the the query design .. but what am I supposed to do know ???

[June7]

Everything should be working now.