Help setting up relationship

[Garret]

Here's a bit of a sticky widget for an Access 2000 database.
I'm still pondering about how exactly to create the tables and
relationships between them. So I'll tell you what I need to do and the
wise sages that read this can aid me if they are able to.

There are Components with properties that go to each one. This would
most likely be a tblComponents table with Primary Key as ComponentNo.

The shipment that comes in has a ComponentNo (the actual items),
Vendor, DateReceived, ShipmentSize, and a few other fields that belong
to a shipment. Since multiple shipments of a particular Component are
ordered, a new table has to store these shipments.

When a shipment comes in, the Components have to be inspected. Each
Component has a list of Dimensions that must be inspected in each
shipment. The Dimensions needed to be inspected will almost always be
the same BUT on a rare occasion a Dimension might be added or changed
if the Component has been altered by the Vendor.

Although the Dimensions for each Component stay relatively the same,
characteristics about the inspections of the Dimensions of each
shipment must be stored. The Tool used to measure the Dimension, the #
of Components that pass the particular Dimension inspection and that do
not pass, and a field that stores user Remarks would all 'belong' to a
Dimension.

Thanks for any help, I can answer any questions that you might have.

 

[Jeff Boyce]

Garrett

You know what you mean by "Dimension", but I'm not clear whether you are
describing a physical dimension (height, length, depth) or a
possibly-physical characteristic (weight, diameter, tensile strength, ...)
or a "quality/characteristic" (e.g., a continuum along which the object is
rated).

Can one of your components have a single "dimension" or multiple dimensions?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Garret]

You know what you mean by "Dimension", but I'm not clear whether you are
describing a physical dimension (height, length, depth) or a
possibly-physical characteristic (weight, diameter, tensile strength, ...)
or a "quality/characteristic" (e.g., a continuum along which the object is
rated).

My apologies. Depending on the component, these dimensions can be
height, width, length, depth, diameter, slot length, thickness, etc. A
component could be a nut (not the edible kind), or a spherical ball
valve, or a screw, etc. All are usually dimensions that can be
measured in terms of a length in inches.

Can one of your components have a single "dimension" or multiple dimensions?

Every Component probably has at least 3 dimensions and could have about
up to 15.

 

[Jeff Boyce]

Garrett

The fact that one Component can have 1-to-many (in your case "3"-to-many)
dimensions tells me that you need to model this relationship in your data.

Consider having a Component table and a Dimension (the name of the
dimension -- e.g., height, slot number, ...) and a ComponentDimension table
that holds valid combinations of components and dimensions, along with the
"measurement" of that component's dimension's quality.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Garret]

The fact that one Component can have 1-to-many (in your case "3"-to-many)
dimensions tells me that you need to model this relationship in your data.

This isn't quite what I meant - I may have said something in a
confusing manner. Each Component has Dimensions but they are different
for each Component. However, a Component always has the same
Dimensions (with respect to change every so often). I'll try and give
a better explanation.

Components are widely different - as various as this example. Let's
say the Component is a dolphin. The dimensions that might be inspected
are teeth length, fin length, blowhole diameter, and tail width.
Another Component might be a Porcupine. These dimensions might be
spine length, spine thickness, head width, and eyeball diameter. There
are not the same dimensions for each Component, while each Component
always has the same dimensions. For this reason, it is necessary to
store the dimensions under each Component somewhere...

When a box of porcupines comes in, those four dimensions listed
previously always have to be inspected. Each shipment inspection has
to be recorded. Although the same dimensions are always used, the tool
used to measure that dimension will change, as well as the # that pass
or do not pass that particular dimension inspection (some porcupines
might have the correct spine length, others might not have the correct
spine thickness).

Consider having a Component table and a Dimension (the name of the
dimension -- e.g., height, slot number, ...) and a ComponentDimension table
that holds valid combinations of components and dimensions, along with the
"measurement" of that component's dimension's quality.

A dimension would never belong to more than one Component, so there is
no need for a table to hold "combinations of components and
dimensions".

You are probably even more confused. I'd love to try my best to
explain if you are, for I really need to get this relationship set up.
I appreciate your initiative to help so far!

 

[Jeff Boyce]

Garret

So, a Component of type X can have 4 dimensions (D1, D2, D3, D4), and a
Component of type Y can have 3 dimensions (D5, D6, D7) and a type Z
Component can have 7 dimensions (are you sure that "height" only applies to
a single Component <G> - D1, D3, D6, D8, D9, D10, D11).

It seems to me (in my current fuzzy understanding) that you need a table
that lists the dimensions that are relevant to a Component of type X, those
that relate to type Y, etc.

Then you have actual instances of a Component of type X (for which you can
look up the relevant applicable dimensions in the previously mentioned
table), for which each of those (revelant) dimensions has an "as-measured"
value.

I'm envisioning:

tblComponent (a dolphin, a porcupine, a semi-truck, ... - generic, not
an instance of)

tblDimension (height, weight, blowhole diameter, poikiness, hauling
capacity, ...)

trelComponentDimension (which dimensions apply to which components)

trelComponentInstance (a specific dolphin, porcupine, ...)
InstanceID
?SerialNumber
?Manufacturer
... (other characteristics of THIS instance of the component)

trelInstanceDimensionMeasurement
InstanceID
DimensionID
MeasurementValue
... (other characteristics of THIS instance's & THIS dimension's
measured value)

Now, have I confused YOU?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Garret]

Garret

So, a Component of type X can have 4 dimensions (D1, D2, D3, D4), and a
Component of type Y can have 3 dimensions (D5, D6, D7) and a type Z
Component can have 7 dimensions (are you sure that "height" only applies to
a single Component <G> - D1, D3, D6, D8, D9, D10, D11).

No, not really. Perhaps I've misled you through all of my analogies.
It really doesn't matter what the Dimension IS. What's important is
the fact that every Component has specifically different Dimensions.
Words as simple as height, weight, length will never be Dimensions.
The system set up right now uses a wide array of numbers as dimension
descriptions in the same field. A component X and component Y might,
on a rare occasion, share a dimension with the same name, but its
likelihood is so small that it's not worth making a table containing
both.

However, I see the need to store these dimensions somewhere because the
same dimensions will be inspected for a Component for each shipment.
Rather than just having a list of them somewhere on paper for each
Component, it would be nice to store them somewhere so that when a
shipment is being inspected, the User can open the form, select the
Component that came in (I already created a "Search" form), and it's
dimensions will show up in a subform.
The tricky part is the fact that there has to be a record for each
dimension of each shipment, but ONLY one record. So a Component X
shipment comes in, and D1 is inspected with Tool1, has 10 that pass and
1 that fails, and D2 is inspected with Tool2 and has 9 that pass and 2
that fail. A week later, another shipment comes in and Component X has
D1 inspected with Tool3 and has 10 pass and 5 fail, and D2 is inspected
with Tool3 that has 14 pass and 1 fail.

It seems to me (in my current fuzzy understanding) that you need a table
that lists the dimensions that are relevant to a Component of type X, those
that relate to type Y, etc.

Then you have actual instances of a Component of type X (for which you can
look up the relevant applicable dimensions in the previously mentioned
table), for which each of those (revelant) dimensions has an "as-measured"
value.

I'm envisioning:

tblComponent (a dolphin, a porcupine, a semi-truck, ... - generic, not
an instance of)

tblDimension (height, weight, blowhole diameter, poikiness, hauling
capacity, ...)

trelComponentDimension (which dimensions apply to which components)

trelComponentInstance (a specific dolphin, porcupine, ...)
InstanceID
?SerialNumber
?Manufacturer
... (other characteristics of THIS instance of the component)

trelInstanceDimensionMeasurement
InstanceID
DimensionID
MeasurementValue
... (other characteristics of THIS instance's & THIS dimension's
measured value)

Now, have I confused YOU?!

Haha, a bit. It seems to me that you are on the right track with
"Instances". See the example I listed above for this concept. I'm
having a hard time making sense of your tables though, and there still
remains the data of Vendor, DateReceived, ShipmentSize, etc.
information about the Shipment.

I hope I still have you at this point . Thanks.

 

[Jeff Boyce]

If you have to measure each shipment, and each component in each shipment,
don't you need to have multiple instances of any given component?


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Garret]

If you have to measure each shipment, and each component in each shipment,
don't you need to have multiple instances of any given component?

I suppose you do, yes. I am just having a hard time following. The
database needs to store each shipment, and how well the components did
in the inspection, so they can be looked back on. A Component always
stays the same, and the dimensions of it always stay the same. An
"instance" could be a shipment, because the shipping information is
different (different Vendor, date, size), and the information of the
dimension inspections is different.

On the user form for a purchase inspection, the dimensions should be
listed, and a section where they can enter the number that pass or
don't pass and what tool was used. A One-many cannot exist between
dimensions and this information because then multiple records of this
information would show on the form, and there should only be one
"instance" of this information per shipment. This information has to
be tied with the shipment record.

Thanks.

 

[Jeff Boyce]

Garret

my comments in-line...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I suppose you do, yes. I am just having a hard time following. The
database needs to store each shipment, and how well the components did
in the inspection, so they can be looked back on. A Component always
stays the same, and the dimensions of it always stay the same.

I assume you mean "a dolphin is always a dolphin, never a porcupine, and a
dolphin always has a blowhole diameter".

An
"instance" could be a shipment, because the shipping information is
different (different Vendor, date, size), and the information of the
dimension inspections is different.

So the next dolphin (or each of the 100 dolphins) is an instance, and for
each, you will want to measure blowhole diameter.

On the user form for a purchase inspection, the dimensions should be
listed, and a section where they can enter the number that pass or
don't pass and what tool was used.

The "number that pass" ... does this mean only how many dolphins have
blowhole diameters, or is there a valid range of blowhole diameters, within
which any given dolphin's blowhole must fit in order for that dolphin to
"pass"?

A One-many cannot exist between
dimensions and this information because then multiple records of this
information would show on the form,

You have confused tables with forms. You can have 1000 records in your
table, but you can restrict the form to display only one of them.

and there should only be one
"instance" of this information per shipment.

If you have more than one dolphin in the shipment, each shipped dolphin is a
single "instance" of "dolphin" (the Component).

 

[Garret]

I assume you mean "a dolphin is always a dolphin, never a porcupine, and a
dolphin always has a blowhole diameter".
Exactly.


So the next dolphin (or each of the 100 dolphins) is an instance, and for
each, you will want to measure blowhole diameter.

Also true.

The "number that pass" ... does this mean only how many dolphins have
blowhole diameters, or is there a valid range of blowhole diameters, within
which any given dolphin's blowhole must fit in order for that dolphin to
"pass"?

Sorry about not being specific enough. Yes, you are correct though.
When each shipment is inspected when it comes in, the component's
dimensions in the shipment are checked. To "pass", there is a certain
known tolerance (ex: +/-.005 inches) that the dimension must fit.
Logically, some components might pass certain dimension tests and fail
on others. But there is only one "test" record per dimension, per
shipment, of how many fail and pass.

You have confused tables with forms. You can have 1000 records in your
table, but you can restrict the form to display only one of them.

Perhaps there is a way to prevent the user from making more than one
record per dimension per shipment, as I described in my "test" above.

If you have more than one dolphin in the shipment, each shipped dolphin is a
single "instance" of "dolphin" (the Component).

There doesn't have to be instances of Components, just instances of
shipments. It's not necessary to store Dolphin the tblComponents for
every single Dolphin in the shipment. Is that what you mean?

Again, thanks for your help so far. You really should be getting paid
for listening to all of my troubles.

 

[Jeff Boyce]

Garret

(again, in-line...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Also true.


Sorry about not being specific enough. Yes, you are correct though.
When each shipment is inspected when it comes in, the component's
dimensions in the shipment are checked. To "pass", there is a certain
known tolerance (ex: +/-.005 inches) that the dimension must fit.
Logically, some components might pass certain dimension tests and fail
on others. But there is only one "test" record per dimension, per
shipment, of how many fail and pass.

So, you are NOT keeping the actual blowhole diameter for Component # 12345,
or for any of the 100 dolphins (component #12345 - #12445), but only the
number 93, the number of dolphins whose blowhole diameter "passed" your
test?

Perhaps there is a way to prevent the user from making more than one
record per dimension per shipment, as I described in my "test" above.

I'm confused again. I thought a "shipment" could contain one or main
instances of a component. So you are only interested in what percentage of
the shipment passes, not which individual components in the shipment passed?

There doesn't have to be instances of Components, just instances of
shipments. It's not necessary to store Dolphin the tblComponents for
every single Dolphin in the shipment. Is that what you mean?

That's what I'm trying to envision. Perhaps you could use a series of
statements like the following to describe the entities and relationships...

A Component is an idealized example.
A Component can have one-to-many Dimensions on which it will be tested.
A Component's Dimensions have both a title and a range/tolerance
measurement.
A Shipment can have one-to-many Component Instances.
A Shipment either passes or fails, based on whether the Component
Instances pass/fail the test of their Dimensions/tolerances.
No individual Component in a Shipment is marked as passes/fails, only
the entire Shipment.

Please feel free to add to/delete/modify these statements to reflect your
situation.

Again, thanks for your help so far. You really should be getting paid
for listening to all of my troubles.

We may get to the point where you'll need to locate someone you can pay to
help further.

 

[Garret]

Sorry for my delayed response. I just moved so it took some time
settling in.

Lets clear some things up. A shipment only contains one type of
Component. Any other components that come in will be treated as a
different shipment.
A REAL dimension as would appear in the database would be more like
XYZ104. On the physical blueprint for the component, this code number
corresponds to a section of the component. So Component ABCD9 might
have dimensions XYZ134, XYY934, AB7772, etc. That is what a dimension
is but I didn't want to confuse you so I used simple terms like
blowhole diameter and length.

So again, for example, a box of Component ABCD9 comes in (containing
100). 100 pass the XYZ134 dimension test (by achieving the desired
tolerance of +-.005 inches(this number is on the physical blueprint
which is looked at during the testing, so this number is not necessary
to store), 98 pass the XYY934 dimension test, and 91 pass the AB7772
dimension test. This data should be tracable to/by this particular
shipment, which should be tracable to/by this particular component.

That's what I'm trying to envision. Perhaps you could use a series of
statements like the following to describe the entities and relationships...

A Component is an idealized example.
A Component can have one-to-many Dimensions on which it will be tested.
A Component's Dimensions have both a title and a range/tolerance
measurement.
A Shipment can have one-to-many Component Instances.

Yes, either this or a Component has a one-many Shipment Instances.

A Shipment either passes or fails, based on whether the Component
Instances pass/fail the test of their Dimensions/tolerances.

No, the entire shipment would never pass or fail. Only particular
dimension tests would pass or fail. See my example at the beginning of
this post. The # that pass or fail only needs to be logged for
historical reasons and to contact the seller if too many fail in too
many shipments, and so the company can tell them exactly which
dimension that they need to adjust in future purchases.

Hopefully you get this, I know its been over a week now.

 

[Jeff Boyce]

Garret

So you are saying that you'll go ahead and "build" with a component that
doesn't pass (one of the "n" in a shipment that didn't meet tolerance)
testing, but you'll reject a shipment if too many don't meet tolerance?

So, back to the top, you have:
Shipments (each shipment has only one Component)
Dimensions (each component is "measured" on one or more dimensions)
Component (or Shipment) x Dimension test results (e.g., 93% met
tolerance)

It is still that last item that bothers me... I suppose we can blame it on
my status as a recovering statistics instructor. If I gave you a chance to
buy a car, but told you that one of the components in the car was from a
batch in which only 50% of the pieces met desired tolerance, would you want
it? You have no way to know how FAR out of tolerance a piece is (or maybe
you are saying that you only KEEP the ones that pass?).

Help me get back on track. What do you need to report on to satisfy the
folks who are asking questions about the components?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Garret]

Haha, no. Of course the components that don't meet tolerance will not
be used to make other products, they will be sent back or discarded.
This information is basically to let the company keep track of which
vendors have been giving us "good" shipments where most or all the
components are perfect. If shipments from a particular vendor have a
ton of components that keep failing the tolerance tests...its time to
talk to the vendor or switch vendors.

I don't really need a field that calculates the percentage but that
isn't hard to do after its all made I suppose.

Alright so when a shipment of 100 ABC components comes in, an employee
sits down and measures dimensions 1A, 2B, 3C for each of the 100
components. First the employee takes the specific tool used to measure
1A and tests each one of them. This tool must be recorded. After all
100 tests are completed, the # that pass and # that fail must be
recorded. This repeats for 2B and 3C. All this is stored under this
shipment, which is stored under this component, so that a "history" can
be observed by locating a component and looking at all the shipments
that have been ordered of it.

 

[Jeff Boyce]

Garret

It sounds like you've laid out all the (major) data elements you need:

Component
ComponentShipment (a "many" table, related to Component)
ShipmentReceivedDate
Quantity
ComponentShipmentDimension (a "many" table, related to
ComponentShipment)
Dimension"#"
DimensionMeasuringTool
PercentPassingToleranceTest

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Garret]

Garret

It sounds like you've laid out all the (major) data elements you need:

Component
ComponentShipment (a "many" table, related to Component)
ShipmentReceivedDate
Quantity
ComponentShipmentDimension (a "many" table, related to
ComponentShipment)
Dimension"#"
DimensionMeasuringTool
PercentPassingToleranceTest

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

Jeff, this looks great but it somewhat brings me back to the beginning.
See with this, the user would have to re-enter the dimensions in for
each shipment. Since the dimensions are always the same every time
(for each Component), it is somewhat of a hassle to have the user
re-enter them. This is why I wanted a dimension table that held the
child records of a Component. This is what I had originally, but I did
not know how to make "multiple instances" of these dimensions per
shipment...See what I mean?
If this is not possible to do in Access then I just guess I have to
work with the tables you wrote above.

 

[Jeff Boyce]

Garret

Those weren't intended to represent tables (well, at least, not all of
them).

If you create a lookup table that holds ValidComponentDimension, i.e., valid
combinations of Component and Dimension, you can use this to find out which
measurements are required.

I can imagine a form with a subform with a combo box for Dimension (only
those that are valid for the component) and a "PercentPassed" field. The
user selects a Component on the main form, the subform combo box only has
valid Dimensions for that Component, and the user does the measuring,
selects the Dimension, and enters the percentage.

Does that get back to what you need to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Garret]

Yes, that seems to be a good idea. I would have liked to have
dimensions as a continuous subform so that each dimension can be seen
and entered in, but combo boxes could work too. We seem to be doing
well now.
So to do all this, what tables will I ultimately need?
A report will also need to be printed of any given shipment of a
Component and its dimensions and how the dimensions did on the tests.
Sort of like:

Shipment #454646 (optional to show)
Received: 6/3/06
Component: ACH234
Dimension X: ------- data ------- (Tool measured/# pass/#
fail)
Dimension Y: ----------data-------------
Dimension Z: ----------data----------

Signature__________________________

This is probably easy once the tables are set up, right?

 

[Jeff Boyce]

Garret

As my previous response (or previous, previous) suggested, you seem to have
the tables (i.e., "entities") pretty well covered.

I may be reading too much into your last post, but it sounds like you expect
to use a table to do your report. If so, don't! Use a query to return the
data you want in your report from the tables (entities) you have.

Regards

Jeff Boyce
Microsoft Office/Access MVP