How to update a field in a table 5

[meliedis]

In a table i have the Balance numeric field. In a form I want to add x quatity to that Balance field and when I press the submit or refresh button I want to update the Balance.
Example:
Table:
BoxID, BaxName, Balance

Form:

Box 1 has a balance of 25 (boxes).
I want to add 5 more boxes.
Total or new Balance would be 30.

Submit or update or refresh balance.

Box 1 has a balance of 30 now

If i want to take 10 boxes then calculate
and the new balance would be 20.

Please help I have nothing to start it :-(

 

[fneily]

Have you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Balance is a calculated field. You don't keep calculated fields in a table.
Also, other questions arise such as do you want to keep track of the increases/decreases?

I'd have two tables.
tblBox
BoxID Primary Key
BoxName
BoxDescription
etc.

tblTransaction
TransID Primary Key
BoxID Foreign Key
Quantity
Date

The tblTransaction would start out with one record showing the amount at creation time for each box. Quantity would then be a positive or negative amount.
To find the balance, you'd create a query connecting tblBox and tblTransaction. Bring down BoxName and Quantity, and in the QBE pane, click the Total button to add a Total row. Under BoxName it will say Group By. Under Quantity, select Sum from the dropdown. Run it and you'll have the balances of all the Boxes. Or you can filter under the BoxName.
This query can then be run from a button on a form, by itself, output displayed in a textbox, etc.

 

[meliedis]

Can you show me how I can do it from a query then run it from a button on a form... that's what I want.

I already have a query that goes like this:

SELECT Part.PartID, Part.ModelID, Part.PartNumber, Part.Balance, Part.Add, Part.Delete, Part.Output
FROM Part;

And in the Form I have this:

Board Name__________
PartNumeber_______________
Balance: 25
Add: ________ (this filed is for the user to enter the desire quantity of boxes the want to add)

Total: _______ the final result of the new balance

and then a Button the updates the original balance with the new one.


Please advise

 

[JBinQLD]

fneily will slate me for not forcing home the point of normalisation, but since you're insistant fella, doesn't this work?

me!total=me!balance+me!add

seems fine here mate, JB

 

[fneily]

JBinQLD - if they're already presented with the correct way, then I can't slate anyone answering their query per se. I've done it myself because they refused to normalize. "You can lead a horse to water.....

 

[Remou]

What is sad is that the problems pile up, until one day you end up painted into an "un-normailized" corner, with vast amounts of work to get out.

 

[JBinQLD]

Good points fneily and remou, but I try to make a point of ONLY chipping in a quick fix if the OP appears to desire just a quick fix having been pointed in the right direction by good fellas such as yourselves.

I'll hush up more if you prefer but I DO try to give people the right way first, and if they dont get it I don't want to become a teacher so I try to give them a fix rather than think of them pulling hair in frusration.

Happy weekend all! JB

 

[meliedis]

that's great!!! you are all such good fellas.

thanks

next time i'll try to sound less stupid.

 

[JBinQLD]

mel..

Not stupid at all. Don't condone yourself while learning.

Remember the very common proverbs such as "Just because it works, doesn't mean it's right", "Many ways to skin a cat", "It's stupid but it works means it's not stupid"

Happy Dayz, JB

 

[JBinQLD]

Re: post above, meant criticise, not condone! Ooops!