How do i update a table by another table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FranciscoG - 26 Mar 2005 02:05 GMT

I am in the process of building a db for my manufacturing plant. The Database
will help in determining employee efficiency. it is almost done until i ran
into a small problem

there are 4 managers with 3 different "cells" each that they are in charge
of. Each cell has a number of SKU's associated with them. I created a table
for each different cell in which it contains that cell's SKU as well as the
designated times for different process. Each table structure is identical, It
has "Sku", "Clean Time" "assembly time" "Finish Time". This allows each
manager to update their tables when a new SKU is added to a cell. I want to
Have a Master Table which will list all the SKU for the Facility as well as
the associated times and I would like it to update when a manager updates his
or her Cell's table. I am not that good with Access and i find it easier to
work with one table (Master Table) in order to do any calculations that need
to be done it will also minimize the amount of queries i need to run in order
to extract information. Is it possible to update a table by another or
combine all 12 tables into one and still maintain them seperate to avoid
confusion amongst the managers? or is there a way to create a Form that would
update Both Tables (Cell table and Master table) at the same time since each
field is identical.

Thank you all in advance for any and all help.

Reply to this Message

PC Datasheet - 26 Mar 2005 03:18 GMT

Down the road you are going to regret this design of your tables. You are
already seeing the consequences by the difficulty of having to update one
table from another table. Down the road you are going to see loss of data
integrity as well as difficulty in displaying your data in the way you want
on the screen and reporting on your data in the way you want. That being
said, here is my answer to your question.

To minimize your problems, always create the original record in the Master
table and use an autonumber for the primary key. By doing that, you create a
primary key for each record that will never be duplicated. You can then make
a copy of new records in your cell tables. When you copy the record to the
cell tables, be sure to include the primary key from the master table. The
cell table will have its own primary key so you need a field in the cell
table named MasterTablePK to store the primary key value from the master
table. Once the record is in the cell table, your managers can add their
data to the record. When you need to update the master table, create a query
that includes your master table and the cell table. Join the two tables on
the primary key of the master table and the MasterTablePK field in the cell
table. Only include the fields in the master table you need to update in the
query. Change the query to an update query. In each field in the query where
it says Update To, put this expression using your actual field names:
[Celltable].[MyCellTableFieldName]
When you run this query, the fields in the Master Table in the query will be
updated to the values in the equivalent fields in the cell table.

Again, I strongly encourage you to reconsider the design of your database
now since you are still in the process of building your database. If you
need help with designing the correct table structure for your database,
contact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> I am in the process of building a db for my manufacturing plant. The Database
> will help in determining employee efficiency. it is almost done until i ran
[quoted text clipped - 18 lines]
>
> Thank you all in advance for any and all help.

Reply to this Message

StopThisAdvertising - 26 Mar 2005 11:48 GMT

> Again, I strongly encourage you to reconsider the design of your database
> now since you are still in the process of building your database. If you
[quoted text clipped - 3 lines]
> --
> PC Datasheet

Beware, I bet he wants money for his help.
This guy seems to be just specializing in advertising and job-hunting in the groups here.
Check out:
http://www.google.com/groups?as_q=contact-me&as_epq=email%20address%20&as_uautho
rs=PC%20Datasheet%20&as_scoring=d&lr=&hl=nl

Arno R

Reply to this Message

PC Datasheet - 26 Mar 2005 16:29 GMT

You are a real jerk!!! Go crawl back into your hole and slurp on sauerkraut.

Here's the part of my post that you knowingly did not quote ---

Down the road you are going to regret this design of your tables. You are
already seeing the consequences by the difficulty of having to update one
table from another table. Down the road you are going to see loss of data
integrity as well as difficulty in displaying your data in the way you want
on the screen and reporting on your data in the way you want. That being
said, here is my answer to your question.

To minimize your problems, always create the original record in the Master
table and use an autonumber for the primary key. By doing that, you create a
primary key for each record that will never be duplicated. You can then make
a copy of new records in your cell tables. When you copy the record to the
cell tables, be sure to include the primary key from the master table. The
cell table will have its own primary key so you need a field in the cell
table named MasterTablePK to store the primary key value from the master
table. Once the record is in the cell table, your managers can add their
data to the record. When you need to update the master table, create a query
that includes your master table and the cell table. Join the two tables on
the primary key of the master table and the MasterTablePK field in the cell
table. Only include the fields in the master table you need to update in the
query. Change the query to an update query. In each field in the query where
it says Update To, put this expression using your actual field names:
[Celltable].[MyCellTableFieldName]
When you run this query, the fields in the Master Table in the query will be
updated to the values in the equivalent fields in the cell table.

"PC Datasheet" <nospam@nospam.spam> schreef in bericht
news:xD31e.8843$S46.6008@newsread3.news.atl.earthlink.net...

Beware, I bet he wants money for his help.
This guy seems to be just specializing in advertising and job-hunting in the
groups here.
Check out:
http://www.google.com/groups?as_q=contact-me&as_epq=email%20address%20&as_ua
uthors=PC%20Datasheet%20&as_scoring=d&lr=&hl=nl

Arno R

Reply to this Message

StopThisAdvertising - 26 Mar 2005 17:31 GMT

> You are a real jerk!!! Go crawl back into your hole and slurp on sauerkraut.
>
> Here's the part of my post that you knowingly did not quote ---
> <snip answer>

Getting offensive again Steve? Getting annoyed ?
Whether or not you answer a question or not, advertising is not appropriate.
So I will quote your advertising. Your ongoing advertising that's my point !

-- You abuse this group and other groups for advertising and job-hunting over and over again:
http://www.google.com/groups?as_q=contact-me&as_epq=email%20address%20&as_uautho
rs=PC%20Datasheet%20&as_scoring=d&lr=&hl=nl

-- You hide your identity while asking questions:
http://groups.google.com/groups?hl=nl&lr=&threadm=GI7zb.1644%24Qd6.1325%40newsre
ad1.news.atl.earthlink.net&rnum=6&prev=/groups%3Fhl%3Dnl%26lr%3D%26q%3Dhamonroe%
40%26btnG%3DZoeken%26meta%3D

As this 'Heather' you asked many questions. Even more as 'Kristine':
http://groups.google.com/groups?q=kschonder%40bellsouth.net&hl=nl&lr=&group=comp
.databases.ms-access&sa=G&scoring=d

You are pathetic !
Need more examples ?
Arno R

Reply to this Message