Mathematical problem

[Steven]

Hi,
im using access 2003 and need a hand. Ive got one table where by one of
the fields is a yes/no tick box. In another table ive got a second field
called 'Number of stock'. When this box is ticked in one table i wish the
number in the other to decrease by 1. Ive joined the two tables together in a
relational database but dont know where to go from there.

Any help ??

thanks

 

[Stefan Hoffmann]

hi Steven,

im using access 2003 and need a hand. Ive got one table where by one of
the fields is a yes/no tick box. In another table ive got a second field
called 'Number of stock'. When this box is ticked in one table i wish the
number in the other to decrease by 1. Ive joined the two tables together in a
relational database but dont know where to go from there.

Hmm, e.g.

Number of stock: 10
-Check tick
-Uncheck tick
-Check tick
-Uncheck tick
Number of stock: 8 ???

In this case you should add an event handler on your form on the CheckBox.

If the number should be 10, then you can simply use an addition:

SELECT A.NumberOfStock + B.Tick
FROM TableA A
INNER JOIN TableB B
ON A.Id = B.ID

A checked/true boolean value evaluates as -1 for arithmetics.

mfG
--> stefan <--

 

[Steven]

Woah sorry could you brake that down a bit simpler please i'm only 15 doing
GCSE ICT

Thanks

 

[Stefan Hoffmann]

hi Steven,

Woah sorry could you brake that down a bit simpler please i'm only 15 doing
GCSE ICT

Ok. I'll give it a try.

Case A:

Do you only need the use/display the decreased stock number when the
other field is checked (true) and otherwise use/display the saved stock
number?
As you said you have already the JOIN in a query, simply add a new column:

CorrectedValue:
A.NumberOfStock + B.BooleanField

You need to replace A and B with your table names and use the correct
field names.

Case B:
Do you need to decrease the stock value every time the CheckBox is set
from False to True then you need an event on your form to do that.

mfG
--> stefan <--

 

[John W. Vinson]

Hi,
im using access 2003 and need a hand. Ive got one table where by one of
the fields is a yes/no tick box. In another table ive got a second field
called 'Number of stock'. When this box is ticked in one table i wish the
number in the other to decrease by 1. Ive joined the two tables together in a
relational database but dont know where to go from there.

Actually, that's probably a Bad Idea.

Storing data in a table which can be calculated from other data in the table
is risky. Suppose you were to delete a few records from your table with the
checkbox? The "Number of stock" field in the other table would now be WRONG,
or perhaps it was a mistake to delete the records - in either case, the data
in the tables is inconsistant!

You're almost surely better off to calculate the Number of stock dynamically,
in a Totals query, whenever you need it - storing an initial stock value in
the table of items and subtracting the number of items sent out.

There are some sample inventory applications available: check out the
resources at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials