Two Tables in One Form

[Guest]

Hi,
I have two tables.
One is controled on a form, the other is just a table.
I'd like to be able to copy whatever is in 'AMOUNT' on the form to the AMOUNT
field in the other table. The form has unique values on it,
ie. CODE1 AMOUNT
CODE2 AMOUNT
I need to keep both tables, but just basically put the value for amount in
one table into the other using the CODE part as unique criteria. Hope it's
not too confusing. Thanks

 

[Jeff Boyce]

If your first table already has Code1 and Amount1, why do you think you need
to put Amount1 into table2? You might need to do that if you were working
with spreadsheets, but Access is a relational database. In most cases, you
should be able to put CODE1 in table2 and use a query to join the two tables
and see Amount1 -- no need to store it redundantly.

Or if your business situation/need is different, please describe a bit more
so we can offer more specific suggestions.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Thanks,
Well it's basically that I want to use the form to add amounts to the second
table, but keep the first table for a record of what and how many were added
that time.

 

[Jeff Boyce]

I'm sorry, but I'm not following...

I asked about the "why", not the "how". You've described "how" you've
decided to solve a problem, but not what the underlying business need is for
having the same value in two different places.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Right,
The form is to store values in a table showing the amount of each item is
added. The second table contains the overall amount, so the amount in the
first needs adding to the total in the second.
Let's say item 1 has an amount of 2, we add 3 in the form, that then adds an
extra 3 onto the total amount in a seperate table. So I've got an overall
amount in one table, and just the amount that I've added that time in a
seperate one.

 

[Guest]

Ok, I'll give it a go.
Thanks!!

If I'm understanding what you are trying to do, you have "detail" records
and you want a "sum" value.

That's what queries are for! Create a new query against your detail table,
click the "Totals" toolbar button, and select "Sum" instead of "GroupBy" for
the aggregation.

Good luck! (and no need for a second table!)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

If I'm understanding what you are trying to do, you have "detail" records
and you want a "sum" value.

That's what queries are for! Create a new query against your detail table,
click the "Totals" toolbar button, and select "Sum" instead of "GroupBy" for
the aggregation.

Good luck! (and no need for a second table!)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/