single or multiple tables

Here is my situation. I already have a database with a list of all the parts our facility manufactures: PartID, PartName, PartNumber, etc.

I need to create a seperate database for data collection. Each part will be collected on a daily basis. Each collection will also contain the date, time, and operator. However, the number of points collected for each part and the minimum values for these points will be different. My question is this, what would be the best / most efficient method for setting up my tables? I want to link to the exsisting parts table in the other database, and need to establish the number of points and minimums for each point before data can be collected. Should I create seperate tables for each part, or one table with the maximum number of points for all parts? Thanks for your help in advance.

AdmSteck,

It is very difficult to advise on the basis of the information you have
given. Maybe it would help if you could post back with some more
details, with examples. In particular, why do you need a separate
database rather than tables within the existing database, and what is
the meaning of "points collected for each part"?

Another comment, not directly related to your immediate question...
Doesn't the PartNumber uniquely identify each part? Therefore, you
probably do not need the PartID field at all?

--
Steve Schapel, Microsoft Access MVP


AdmSteck wrote:
Here is my situation. I already have a database with a list of all
the parts our facility manufactures: PartID, PartName, PartNumber,
etc.

I need to create a seperate database for data collection. Each part
will be collected on a daily basis. Each collection will also
contain the date, time, and operator. However, the number of points
collected for each part and the minimum values for these points will
be different. My question is this, what would be the best / most
efficient method for setting up my tables? I want to link to the
exsisting parts table in the other database, and need to establish
the number of points and minimums for each point before data can be
collected. Should I create seperate tables for each part, or one
table with the maximum number of points for all parts? Thanks for
your help in advance.

I want to use a seperate database because the db with the part information is used as a shared db since the part information is common to several other db's I have written. Also, the part numbers can be the same on rare occurrences, thus the partid.
Our facility produces spot welded parts. Each part has different minimum sizes for each weld and a different amount of welds. I am trying to set up a db to record the weld sizes (data points). Each entry would contain the following fields: ID, date, time, operator, weld1, weld2, weld3, etc....
My question is if it would be better / more efficient to have a seperate table for each part with the correct number of weld fields for that part, or add a partID lookup field and have one table with the same number of welds as the largest part (I think it's around 38). If I use the latter, there would be several empty fields for all the parts with fewer than 38 welds (some only have 1). I was planning on creating a table that would contain all the minimum values for each part, or adding it to the exsisting parts db. I would like to keep it seperate to make it easier to update.
I hope this clarifies things a bit. Thanks.

"Steve Schapel" wrote:

AdmSteck,

It is very difficult to advise on the basis of the information you have
given. Maybe it would help if you could post back with some more
details, with examples. In particular, why do you need a separate
database rather than tables within the existing database, and what is
the meaning of "points collected for each part"?

Another comment, not directly related to your immediate question...
Doesn't the PartNumber uniquely identify each part? Therefore, you
probably do not need the PartID field at all?

--
Steve Schapel, Microsoft Access MVP


AdmSteck wrote:
Here is my situation. I already have a database with a list of all
the parts our facility manufactures: PartID, PartName, PartNumber,
etc.

I need to create a seperate database for data collection. Each part
will be collected on a daily basis. Each collection will also
contain the date, time, and operator. However, the number of points
collected for each part and the minimum values for these points will
be different. My question is this, what would be the best / most
efficient method for setting up my tables? I want to link to the
exsisting parts table in the other database, and need to establish
the number of points and minimums for each point before data can be
collected. Should I create seperate tables for each part, or one
table with the maximum number of points for all parts? Thanks for
your help in advance.

AdmSteck,

Thanks for the clarification. The best way to handle this is with a
table structure like this...

Table: Production
ProductionID
ProductionDateTime
Operator
PartID

Table: ProductionWelds
ProductionWeldsID
ProductionID
WeldNumber
WeldSize

You will also, I presume, have a table listing the Parts definitions,
and another table with the minimum weld sizes for the different welds on
each part.

--
Steve Schapel, Microsoft Access MVP


AdmSteck wrote:
I want to use a seperate database because the db with the part
information is used as a shared db since the part information is
common to several other db's I have written. Also, the part numbers
can be the same on rare occurrences, thus the partid. Our facility
produces spot welded parts. Each part has different minimum sizes
for each weld and a different amount of welds. I am trying to set up
a db to record the weld sizes (data points). Each entry would
contain the following fields: ID, date, time, operator, weld1,
weld2, weld3, etc.... My question is if it would be better / more
efficient to have a seperate table for each part with the correct
number of weld fields for that part, or add a partID lookup field and
have one table with the same number of welds as the largest part (I
think it's around 38). If I use the latter, there would be several
empty fields for all the parts with fewer than 38 welds (some only
have 1). I was planning on creating a table that would contain all
the minimum values for each part, or adding it to the exsisting parts
db. I would like to keep it seperate to make it easier to update. I
hope this clarifies things a bit. Thanks.

Ok, the table layout makes sense and seems easy enough, but how would I create a form to show text boxes for each weld number for the operator to enter the data?

"Steve Schapel" wrote:

AdmSteck,

Thanks for the clarification. The best way to handle this is with a
table structure like this...

Table: Production
ProductionID
ProductionDateTime
Operator
PartID

Table: ProductionWelds
ProductionWeldsID
ProductionID
WeldNumber
WeldSize

You will also, I presume, have a table listing the Parts definitions,
and another table with the minimum weld sizes for the different welds on
each part.

--
Steve Schapel, Microsoft Access MVP


AdmSteck wrote:
I want to use a seperate database because the db with the part
information is used as a shared db since the part information is
common to several other db's I have written. Also, the part numbers
can be the same on rare occurrences, thus the partid. Our facility
produces spot welded parts. Each part has different minimum sizes
for each weld and a different amount of welds. I am trying to set up
a db to record the weld sizes (data points). Each entry would
contain the following fields: ID, date, time, operator, weld1,
weld2, weld3, etc.... My question is if it would be better / more
efficient to have a seperate table for each part with the correct
number of weld fields for that part, or add a partID lookup field and
have one table with the same number of welds as the largest part (I
think it's around 38). If I use the latter, there would be several
empty fields for all the parts with fewer than 38 welds (some only
have 1). I was planning on creating a table that would contain all
the minimum values for each part, or adding it to the exsisting parts
db. I would like to keep it seperate to make it easier to update. I
hope this clarifies things a bit. Thanks.

AdmSteck,

Probably the "standard" way to do this would be a continuous view
subform, with each record showing textboxes for the WeldNumber and
WeldSize fields. The operator just enters the data straight into there,
for each weld. It may be applicable to set it up so that an Append
Query is run, to add a complete set of records to the ProductionWelds
table for all required welds for any given Part. That would mean the
subform would immediately show with all records created, and the
WeldNumbers already entered, and the operator then just completes the
WeldSize data.

--
Steve Schapel, Microsoft Access MVP


AdmSteck wrote:
Ok, the table layout makes sense and seems easy enough, but how would
I create a form to show text boxes for each weld number for the
operator to enter the data?