Will my table design cause future problems

Access/VBA newbie here and I have the following situation:
A research facility mixes elements(LotID), processes them,
evaluates the end product and then uses it in a part which
will also get tested.
The table structures I have so far:
tblComposition (created this way because a Lot# can be
made up of 1-? elements)
LotID
Element
ElementRatio

tblProcessing
LOtID
ProcessEquip
RunDate
%Source

tblEvaluation
LotID
ParticleSize
ECValue
FinalMAss

tblParts
Part#
LotID
Account
Performance

One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu at
a ratio of 60,20,20 and was used in PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.

Aaron, you will get nowhere with this question until you identify what is
the "primary key" of each table, and state exactly what thing or event is
identified by each row in the table.

For example:

tblClient - one row for each client known to the system.
ClientID (PK)
Forname
Surname
etc.

This might help:
http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC
(off for the day)


"Aaron" wrote in message
...
Access/VBA newbie here and I have the following situation:
A research facility mixes elements(LotID), processes them,
evaluates the end product and then uses it in a part which
will also get tested.
The table structures I have so far:
tblComposition (created this way because a Lot# can be
made up of 1-? elements)
LotID
Element
ElementRatio

tblProcessing
LOtID
ProcessEquip
RunDate
%Source

tblEvaluation
LotID
ParticleSize
ECValue
FinalMAss

tblParts
Part#
LotID
Account
Performance

One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu at
a ratio of 60,20,20 and was used in PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.

I added some key information, hopefully this along with
other descriptions helps. thanks.
-----Original Message-----
Aaron, you will get nowhere with this question until you
identify what is
the "primary key" of each table, and state exactly what
thing or event is
identified by each row in the table.

For example:

tblClient - one row for each client known to the system.
ClientID (PK)
Forname
Surname
etc.

This might help:
http://support.microsoft.com/support...cles/Q100139.A
SP

HTH,
TC
(off for the day)


"Aaron" wrote in
message
...
Access/VBA newbie here and I have the following
situation:
A research facility mixes elements(LotID), processes
them,
evaluates the end product and then uses it in a part
which
will also get tested.
The table structures I have so far:
tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source

tblEvaluation (characterization of LotID's after mfg)
LotID (FK to tblManufacturing,no duplicates)
ParticleSize
ECValue
FinalMAss

tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu
at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how
big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.


.

"Aaron" wrote in message
...

(snipped)

A research facility mixes elements(LotID), processes them,
evaluates the end product and then uses it in a part which
will also get tested.

So:
- each part has one (or several?) tests;
- each part comprises several end products;
- each end product comprises several elements.
Correct?


tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? What does one row in that table,
represent?



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source

What does one row in that table, represent?


tblEvaluation (characterization of LotID's after mfg)
LotID (FK to tblManufacturing,no duplicates)
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? What does one row in that table,
represent? (I don't understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of the entitites you
have mentioned so far: parts, tests, elements, end products, etc.)


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly described so far. Each row in
that table represents a single, unique Part. Each part is identified by a
unique Part#. Part# is the primary key of that table. We need the same level
of understanding of your other tables, before we can comment.

HTH,
TC



One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu at
a ratio of 60,20,20 and was used in PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.

-----Original Message-----

"Aaron" wrote in
message
...

(snipped)

A research facility mixes elements(LotID), processes
them,
evaluates the end product and then uses it in a part
which
will also get tested.

So:
- each part has one (or several?) tests? several types of
tests
- each part comprises several end products? The combined
elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? Actually I have a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The combined
elements from above are mixed with a source element to
produce the final combination which will be used in a part.


tblEvaluation (characterization of LotID's after mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? LotID is actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of
the entitites you
have mentioned so far: parts, tests, elements, end
products, etc.) The combined/processed elements are tested
(characterized) before being used in Parts. Each row will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly described
so far. Each row in
that table represents a single, unique Part. Each part is
identified by a
unique Part#. Part# is the primary key of that table. We
need the same level
of understanding of your other tables, before we can
comment.

HTH,
TC



One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of
Al,Si,Cu at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to
concatenate
the records in the tblComposition and I don't know
how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.


.

Hi AAron

Sorry, but I still don't clearly understand the data that you're dealing
with.

Let's get the data descriptions correct, before we proceed. This may seem
picky, but it is actually the most important step in "data modelling". A
clear understanding of the data entities must come first - then the actual
design of the tables.

Is this correct:

- The research facility handles ELEMENTS, END PRODUCTS, and PARTS.

- I assume that each ELEMENT has a unique ELEMENTID? If not, how do you
distinguish different ELEMENTs?

- Two or more ELEMENTS are mixed together. The result is called a LOT. Each
LOT has a unique LOTID. There is no case where two LOTs have the same LOTID.

- Each LOT is PROCESSED to form an END-PRODUCT.

- Is there only one LOT per END PRODUCT, or could you process several LOTs
into a sinbgle END PRODUCT?

- I assume that each END PRODUCT has an ENDPRODUCTID? If not, how do you
distinguish different END PRODUCTs?

- Each END PRODUCT is subject to one or more TESTS.

- I assume that each TEST has a unique TESTID. If not, how do you
distinguish different TESTSs?

- Could one TEST handle several END PRODUCTs simultaneously?

Aaron, you may feel I am going backwards with my advice to you! (We do not
seem to be getting down into the nitty gritty of that table structures, at
all.) But the reason for this, is that you have not yet clearly described
the data that you need to manage. So IMO, it is premature to comment on the
table structures.

Unfortunately I'll be busy for the next 2 days, & have little or no time on
the net. But I'm happy to get back to this as soon as I return. Or maybe
someone else will jump in & help.

Cheers,
TC


A research facility mixes elements(LotID), processes
them,
evaluates the end product and then uses it in a part
which
will also get tested.

So:
- each part has one (or several?) tests? several types of
tests
- each part comprises several end products? The combined
elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?



"Aaron" wrote in message
...

-----Original Message-----

"Aaron" wrote in
message
...

(snipped)

A research facility mixes elements(LotID), processes
them,
evaluates the end product and then uses it in a part
which
will also get tested.

So:
- each part has one (or several?) tests? several types of
tests
- each part comprises several end products? The combined
elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? Actually I have a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The combined
elements from above are mixed with a source element to
produce the final combination which will be used in a part.


tblEvaluation (characterization of LotID's after mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? LotID is actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of
the entitites you
have mentioned so far: parts, tests, elements, end
products, etc.) The combined/processed elements are tested
(characterized) before being used in Parts. Each row will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly described
so far. Each row in
that table represents a single, unique Part. Each part is
identified by a
unique Part#. Part# is the primary key of that table. We
need the same level
of understanding of your other tables, before we can
comment.

HTH,
TC



One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of
Al,Si,Cu at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to
concatenate
the records in the tblComposition and I don't know
how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.


.