table question

K

karim

Hello All,
I was wondering is it possible in Access 07 to have 3 tables, info.
goes in 2 and the 3rd one would add or subtract the difference in the other
2? I was looking in the tool bar but I couldn't figure it out. thanks for any
help...
 
D

Douglas J. Steele

Tables in Access cannot perform calculations.

You don't need a third table. Create a query that does the calculations, and
use the query wherever you would otherwise have used the table.
 
J

Jeff Boyce

Karim

Unlike a spreadsheet, in which you could do what you described, Access is a
relational database. Rather than save calculated values (as in the third
table you mentioned), use queries to calculate the values "on-the-fly", as
needed.

By the way, if you would be considering storing one set of values in table1,
and another (equivalent) set of values in table2, you are still committing
spreadsheet on Access.

If you'll post a more specific description of the kind of data you are
working with back here to the newsgroups, folks here may be able to offer
more specific suggestions for a design that would allow Access to better use
its relationally-oriented features & functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

karim

Thanks for the fast replay. so how can I get the qry to get 1 field from each
of the 2 tables and make a calculation?
 
K

karim

Thanks jeff and here are some more details.

At my work, there are 2 departments. 1 produces and the 2nd uses this
product. so to have an acurate fast inventory, I would like each of the 2
depts. enter what they use in a table and what they produce in another table
and then the qry gives me the total product on the floor.
hope this helps.
 
J

Jeff Boyce

Karim

Could you provide a bit more specific description of the actual fields you
envision in those tables?

If the only difference between the two tables is whether the product is
produced or used, you don't need two tables!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

karim

Jeff,
You are right, I think it can be done in 1 table. But would it have any
effect on the table if 2 users from 2 diferent depts are entering data at the
same time?
anyway, the fields are: User, Block ID, Lot #, shift, Date, Block used,
Block produced. these are the fields I had in 2 tables, but now I think 1
table should do it. but again, how can I get the qry to get how many blocks
were produced and used from each block id? so lets say block id # 30777 had
60 blocks produced and 30 used. so the qry displays block id # 30777, block
remains: 30

thanks.
 
J

John W. Vinson

Jeff,
You are right, I think it can be done in 1 table. But would it have any
effect on the table if 2 users from 2 diferent depts are entering data at the
same time?
anyway, the fields are: User, Block ID, Lot #, shift, Date, Block used,
Block produced. these are the fields I had in 2 tables, but now I think 1
table should do it. but again, how can I get the qry to get how many blocks
were produced and used from each block id? so lets say block id # 30777 had
60 blocks produced and 30 used. so the qry displays block id # 30777, block
remains: 30
Click to expand...

Like most inventory applications, you may want to consider a two-table
structure: Blocks related one to many to Transactions. No quantity field would
be stored in the Blocks table; instead when a batch of 60 items is produced
you'ld add a record with +60 for that block to the transactions table; when 30
get used you'ld add another record with a quantity of -30. A totals query
summing all the transactions for that block would show how many are left (60 -
30 = 30).
 
Top