Relationship showing too much info

[Ron A.]

As most of you probably know from previous posts, I am creating a parts
relational database. So far with some great help from this discussion group
it is working well except for a glitch in my relationships. I have created
one to many relationships between the partID and PartID (foreign key) in the
Model table to relate many parts for 1 model type. The issue is that when
viewing the parts table I click on the subdatasheet and it is the models
related to the partID wich is fine, but the model subdatasheet show another
subdatasheet wich turns out to be parts. It is a subdatasheet with the field
from the parts table, but the data is blank. Why is a parts subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key
[ComapanyName]
[ContactName]
[ContactTitle]
[Address]
[Phone]

Categoriestbl:
[CategoryID] <-- primary key
[CategoryName]

[PartID] from Partstbl has a one-to-many with [PartID] in Modeltbl (join
type 2).
[SupplierID] from Supplierstbl has a one-to-many with [SupplierID] in
Partstbl (join type 2). [CategoryID] from Categoriestbl has one-to-many with
[CategoryID] in Partstbl (join type 3 and no referential integrity).

Hope this explains enough. Any help is greatly appreciated.

 

['69 Camaro]

Hi, Ron.

If I understand your question correctly, the subdatasheet in the Partstbl
shows the related records in the Modeltbl table, and when that subdatasheet's
records are selected, they show records in the Partstbl table, and so on,
recursively.

The reason for this is that the table on the many side has the table on the
one side selected for the Subdatasheet Name Property. If you change this
property to [None] (recommended) or [Auto], this phenomenon should disappear.
Open the table in Design View, right-click the Title Bar and select
Properties on the pop-up menu to open the Properties dialog window and select
the Subdatasheet Name Property and change it from Table.Partstbl to either
[None] (preferable) or [Auto]. Save the table and open it in Datasheet View
and it shouldn't have any plus signs on the left of the record selectors.

While subdatasheets can be convenient, they can cause problems and impede
database performance, so most experts recommend turning this option off (set
it to [None]). Please see Allen Browne's (MVP) Web site for more information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has available
under the "Tables: SubdatasheetName" section on this Web page to turn off
this default property for all tables in the database at once. And while
you're on that Web page, the code under the "Fields: Allow Zero Length"
section should probably be copied, pasted, and run on this database to fix
this ill-advised default property as well, and then this property turned back
on manually only for the very rare occasions when it is needed. Check out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

As most of you probably know from previous posts, I am creating a parts
relational database. So far with some great help from this discussion group
it is working well except for a glitch in my relationships. I have created
one to many relationships between the partID and PartID (foreign key) in the
Model table to relate many parts for 1 model type. The issue is that when
viewing the parts table I click on the subdatasheet and it is the models
related to the partID wich is fine, but the model subdatasheet show another
subdatasheet wich turns out to be parts. It is a subdatasheet with the field
from the parts table, but the data is blank. Why is a parts subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key
[ComapanyName]
[ContactName]
[ContactTitle]
[Address]
[Phone]

Categoriestbl:
[CategoryID] <-- primary key
[CategoryName]

[PartID] from Partstbl has a one-to-many with [PartID] in Modeltbl (join
type 2).
[SupplierID] from Supplierstbl has a one-to-many with [SupplierID] in
Partstbl (join type 2). [CategoryID] from Categoriestbl has one-to-many with
[CategoryID] in Partstbl (join type 3 and no referential integrity).

Hope this explains enough. Any help is greatly appreciated.

 

[Ron A.]

'69 Camaro,

Great info, it explained alot. Once I made the changes to correct the
subdatasheet problem I then started putting in vehicle part infomation and
realized that I had not designed for different suppliers part numbers. I
have one part related to different vehicle models, but not multiple suppliers
and their different part numbers for the same part. Can anyone with the info
I provided about my database earlier in this post help me out? I don't want
to have to reenter each part info for each different supplier(ie... Napa,
Redline and Ford).
--
Aloha,
Ron A.

Hi, Ron.

If I understand your question correctly, the subdatasheet in the Partstbl
shows the related records in the Modeltbl table, and when that subdatasheet's
records are selected, they show records in the Partstbl table, and so on,
recursively.

The reason for this is that the table on the many side has the table on the
one side selected for the Subdatasheet Name Property. If you change this
property to [None] (recommended) or [Auto], this phenomenon should disappear.
Open the table in Design View, right-click the Title Bar and select
Properties on the pop-up menu to open the Properties dialog window and select
the Subdatasheet Name Property and change it from Table.Partstbl to either
[None] (preferable) or [Auto]. Save the table and open it in Datasheet View
and it shouldn't have any plus signs on the left of the record selectors.

While subdatasheets can be convenient, they can cause problems and impede
database performance, so most experts recommend turning this option off (set
it to [None]). Please see Allen Browne's (MVP) Web site for more information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has available
under the "Tables: SubdatasheetName" section on this Web page to turn off
this default property for all tables in the database at once. And while
you're on that Web page, the code under the "Fields: Allow Zero Length"
section should probably be copied, pasted, and run on this database to fix
this ill-advised default property as well, and then this property turned back
on manually only for the very rare occasions when it is needed. Check out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

As most of you probably know from previous posts, I am creating a parts
relational database. So far with some great help from this discussion group
it is working well except for a glitch in my relationships. I have created
one to many relationships between the partID and PartID (foreign key) in the
Model table to relate many parts for 1 model type. The issue is that when
viewing the parts table I click on the subdatasheet and it is the models
related to the partID wich is fine, but the model subdatasheet show another
subdatasheet wich turns out to be parts. It is a subdatasheet with the field
from the parts table, but the data is blank. Why is a parts subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key
[ComapanyName]
[ContactName]
[ContactTitle]
[Address]
[Phone]

Categoriestbl:
[CategoryID] <-- primary key
[CategoryName]

[PartID] from Partstbl has a one-to-many with [PartID] in Modeltbl (join
type 2).
[SupplierID] from Supplierstbl has a one-to-many with [SupplierID] in
Partstbl (join type 2). [CategoryID] from Categoriestbl has one-to-many with
[CategoryID] in Partstbl (join type 3 and no referential integrity).

Hope this explains enough. Any help is greatly appreciated.

 

['69 Camaro]

Hi, Ron.

I recommend breaking the PartsTbl table into two tables, one for the parts,
one for the supplier parts, and adding a third table for the equivalent
parts. For example:

Partstbl:
[partID] <-- primary key
[PartName]
[NSN]
[CategoryID] <-- foreign key from Categories table
[Remarks]

SupplierParts:
SPID, AutoNumber, primary key
[PartNo]
[SupplierID] <-- foreign key from Suppliers table
[UnitPrice]

EquivParts:
EPID, AutoNumber, primary key
[partID] <-- foreign key from Partstbl table
SPID<-- foreign key from SupplierParts table
PrefSupRank (preferred supplier ranking)

The EquivParts table would store the PrefSupRank in addtion to the PartID
and the SPID, because you always want the best price for the part. Here's
how it would work:

Partstbl:
partID PartName NSN CategoryID Remarks
16 Head gasket, 4.3L Eng. blah 8 blah blah
23 Hose clamp, 2" blah 3 blah blah

EquivParts:
EPID partID SPID PrefSupRank
1 16 43 2
2 16 72 3
3 16 109 1
4 23 256 1
5 23 387 2

In the above scenario, three suppliers can provide the head gasket, and the
supplier who supplies SPID 109 gives the best price, since it's ranked at #1
for this particular part. Likewise, the supplier who supplies SPID 43 gives
the next best price. Two suppliers can provide the hose clamp, and the
supplier who supplies SPID 256 gives the best price.

When the actual part is purchased/installed on the vehicle/whatever you need
this part info for, the EPID will be recorded in the database, not the
PartID, so that the actual supplier and unit price can be derived, and later
on, trends can be determined about the preferred supplier, such as one
supplier has the best price for alternators, but rarely seems to have them
in stock, so in the interest of saving time (read: $$$), this supplier
isn't the first supplier to check for alternators, which means this ranking
will change to a higher number.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

'69 Camaro,

Great info, it explained alot. Once I made the changes to correct the
subdatasheet problem I then started putting in vehicle part infomation and
realized that I had not designed for different suppliers part numbers. I
have one part related to different vehicle models, but not multiple
suppliers
and their different part numbers for the same part. Can anyone with the
info
I provided about my database earlier in this post help me out? I don't
want
to have to reenter each part info for each different supplier(ie... Napa,
Redline and Ford).
--
Aloha,
Ron A.

Hi, Ron.

If I understand your question correctly, the subdatasheet in the Partstbl
shows the related records in the Modeltbl table, and when that
subdatasheet's
records are selected, they show records in the Partstbl table, and so on,
recursively.

The reason for this is that the table on the many side has the table on
the
one side selected for the Subdatasheet Name Property. If you change this
property to [None] (recommended) or [Auto], this phenomenon should
disappear.
Open the table in Design View, right-click the Title Bar and select
Properties on the pop-up menu to open the Properties dialog window and
select
the Subdatasheet Name Property and change it from Table.Partstbl to
either
[None] (preferable) or [Auto]. Save the table and open it in Datasheet
View
and it shouldn't have any plus signs on the left of the record selectors.

While subdatasheets can be convenient, they can cause problems and impede
database performance, so most experts recommend turning this option off
(set
it to [None]). Please see Allen Browne's (MVP) Web site for more
information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has available
under the "Tables: SubdatasheetName" section on this Web page to turn
off
this default property for all tables in the database at once. And while
you're on that Web page, the code under the "Fields: Allow Zero Length"
section should probably be copied, pasted, and run on this database to
fix
this ill-advised default property as well, and then this property turned
back
on manually only for the very rare occasions when it is needed. Check
out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.

As most of you probably know from previous posts, I am creating a parts
relational database. So far with some great help from this discussion
group
it is working well except for a glitch in my relationships. I have
created
one to many relationships between the partID and PartID (foreign key)
in the
Model table to relate many parts for 1 model type. The issue is that
when
viewing the parts table I click on the subdatasheet and it is the
models
related to the partID wich is fine, but the model subdatasheet show
another
subdatasheet wich turns out to be parts. It is a subdatasheet with the
field
from the parts table, but the data is blank. Why is a parts
subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key
[ComapanyName]
[ContactName]
[ContactTitle]
[Address]
[Phone]

Categoriestbl:
[CategoryID] <-- primary key
[CategoryName]

[PartID] from Partstbl has a one-to-many with [PartID] in Modeltbl
(join
type 2).
[SupplierID] from Supplierstbl has a one-to-many with [SupplierID] in
Partstbl (join type 2). [CategoryID] from Categoriestbl has
one-to-many with
[CategoryID] in Partstbl (join type 3 and no referential integrity).

Hope this explains enough. Any help is greatly appreciated.

 

[Ron A.]

Hi Gunny,

I can see where you are going with the tables you have suggested, but I am
weary of my knowledge of Access to put it all together. I have created the
tables you suggested and I noticed that you did not mention the models table
or ID anywhere. The way it is set up now is to show 1 part to many models.
For example, 1 light bulb from Napa is applicable to many makes and models.
Also, could you explain how the relationships will work and what join types
to use? How do I accomplish the preferred ranking? Is it input by access or
is it done manually. Are the columns in the equivPartstbl just going to be
numbers or can they contain the part number and suppliers name? Thanks so
much for your help.
--
Aloha,
Ron A.

Hi, Ron.

I recommend breaking the PartsTbl table into two tables, one for the parts,
one for the supplier parts, and adding a third table for the equivalent
parts. For example:

Partstbl:
[partID] <-- primary key
[PartName]
[NSN]
[CategoryID] <-- foreign key from Categories table
[Remarks]

SupplierParts:
SPID, AutoNumber, primary key
[PartNo]
[SupplierID] <-- foreign key from Suppliers table
[UnitPrice]

EquivParts:
EPID, AutoNumber, primary key
[partID] <-- foreign key from Partstbl table
SPID<-- foreign key from SupplierParts table
PrefSupRank (preferred supplier ranking)

The EquivParts table would store the PrefSupRank in addtion to the PartID
and the SPID, because you always want the best price for the part. Here's
how it would work:

Partstbl:
partID PartName NSN CategoryID Remarks
16 Head gasket, 4.3L Eng. blah 8 blah blah
23 Hose clamp, 2" blah 3 blah blah

EquivParts:
EPID partID SPID PrefSupRank
1 16 43 2
2 16 72 3
3 16 109 1
4 23 256 1
5 23 387 2

In the above scenario, three suppliers can provide the head gasket, and the
supplier who supplies SPID 109 gives the best price, since it's ranked at #1
for this particular part. Likewise, the supplier who supplies SPID 43 gives
the next best price. Two suppliers can provide the hose clamp, and the
supplier who supplies SPID 256 gives the best price.

When the actual part is purchased/installed on the vehicle/whatever you need
this part info for, the EPID will be recorded in the database, not the
PartID, so that the actual supplier and unit price can be derived, and later
on, trends can be determined about the preferred supplier, such as one
supplier has the best price for alternators, but rarely seems to have them
in stock, so in the interest of saving time (read: $$$), this supplier
isn't the first supplier to check for alternators, which means this ranking
will change to a higher number.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

'69 Camaro,

Great info, it explained alot. Once I made the changes to correct the
subdatasheet problem I then started putting in vehicle part infomation and
realized that I had not designed for different suppliers part numbers. I
have one part related to different vehicle models, but not multiple
suppliers
and their different part numbers for the same part. Can anyone with the
info
I provided about my database earlier in this post help me out? I don't
want
to have to reenter each part info for each different supplier(ie... Napa,
Redline and Ford).
--
Aloha,
Ron A.

Hi, Ron.

If I understand your question correctly, the subdatasheet in the Partstbl
shows the related records in the Modeltbl table, and when that
subdatasheet's
records are selected, they show records in the Partstbl table, and so on,
recursively.

The reason for this is that the table on the many side has the table on
the
one side selected for the Subdatasheet Name Property. If you change this
property to [None] (recommended) or [Auto], this phenomenon should
disappear.
Open the table in Design View, right-click the Title Bar and select
Properties on the pop-up menu to open the Properties dialog window and
select
the Subdatasheet Name Property and change it from Table.Partstbl to
either
[None] (preferable) or [Auto]. Save the table and open it in Datasheet
View
and it shouldn't have any plus signs on the left of the record selectors.

While subdatasheets can be convenient, they can cause problems and impede
database performance, so most experts recommend turning this option off
(set
it to [None]). Please see Allen Browne's (MVP) Web site for more
information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has available
under the "Tables: SubdatasheetName" section on this Web page to turn
off
this default property for all tables in the database at once. And while
you're on that Web page, the code under the "Fields: Allow Zero Length"
section should probably be copied, pasted, and run on this database to
fix
this ill-advised default property as well, and then this property turned
back
on manually only for the very rare occasions when it is needed. Check
out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

As most of you probably know from previous posts, I am creating a parts
relational database. So far with some great help from this discussion
group
it is working well except for a glitch in my relationships. I have
created
one to many relationships between the partID and PartID (foreign key)
in the
Model table to relate many parts for 1 model type. The issue is that
when
viewing the parts table I click on the subdatasheet and it is the
models
related to the partID wich is fine, but the model subdatasheet show
another
subdatasheet wich turns out to be parts. It is a subdatasheet with the
field
from the parts table, but the data is blank. Why is a parts
subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key
[ComapanyName]
[ContactName]
[ContactTitle]
[Address]
[Phone]

Categoriestbl:
[CategoryID] <-- primary key
[CategoryName]

[PartID] from Partstbl has a one-to-many with [PartID] in Modeltbl
(join
type 2).
[SupplierID] from Supplierstbl has a one-to-many with [SupplierID] in
Partstbl (join type 2). [CategoryID] from Categoriestbl has
one-to-many with
[CategoryID] in Partstbl (join type 3 and no referential integrity).

Hope this explains enough. Any help is greatly appreciated.

 

[mnature]

Ron -

I am jumping into the middle of all of this, having quickly scanned through
your original posts, so if this doesn't seem relevant, please just ignore it.

You have tables that list part names, manufacturers, cars, etc. What you
need to do is create a single table that can pull from all of these other
tables. You may have a part name that applies to numerous cars (and models
of those cars). You will need a table that contains all the different ways
that you can see a particular part (which means it will be a very very large
table when you are through).

So, for each particular part, there will be multiple lines for which cars
the part can fit into. So, you will have a Parts Table that lists each
specific part once, and a Models Table that lists models of cars. Then you
will have a Parts&Models Table, that combines the key ID's from the Parts
Table and the Models Table to show how many parts and models match. Once the
Parts&Models Table is done, you can use Forms and Reports to sort and filter
the information as you need to see it.

You will use relationships between these tables as one-to-many, with the one
coming from the Parts Table to the many in the Parts&Models Table, and one
coming from the Models Table to the many in the Parts&Models Table.

The way to think of it is that you only want to enter information once, and
after that just use relationships to combine the information. The real
information is the part name/number. The relational part is using the key ID
of the name/number to point to places in a different table where the
information is linked to something else.

 

[mnature]

To further illustrate:

PartsTable:
PartID [key]
PartName
PartNo
NSN
CategoryID [link to CategoryID in CategoryTable]
UnitPrice
Remarks

CategoryTable:
CategoryID [key]
CategoryName

SuppliersTable:
SupplierID [key]
SupplyName
ContactName
ContactTitle
SupplierAddress
SupplierPhone

PartsSuppliersTable:
PartsSupplierID [key]
SupplierID [linked to SupplierID in SuppliersTable]
PartID [linked to PartID in PartsTable]

ManufacturerTable:
ManufacturerID [key]
ManufacturerName

ModelTable:
ModelID [key]
ModelYear
Make
ModelName
ManufacturerID [linked to ManufacturerID in ManufacturerTable]

PartsForModelsTable:
PartsModelID [key]
PartID [linked to PartID in PartsTable]
ModelID [linked to ModelID in ModelTable]

You will use the PartsSuppliersTable to link together various parts and
their suppliers, and the PartsForModelsTable to link together various parts
with various models of cars. The work of sorting this all out is done with
your forms and reports. This way, you only enter a manufacturers name once,
a model name once, a supplier once, a category once. Let the database do the
work for you.

 

['69 Camaro]

Hi, Ron.

I noticed that you did not mention the models table
or ID anywhere.

All the other tables remain the same as your original design. I merely
suggested splitting the Partstbl into two tables and adding a third to
identify different suppliers' equivalent parts.

The way it is set up now is to show 1 part to many models.

You may still use this design, since the PartID is still used as the foreign
key in the Modeltbl table.

How do I accomplish the preferred ranking?

This is subjective on your part. At first, you may want to use the cheapest
supplier for any given part, but there are other business reasons for
preferring a different supplier for certain parts. The preferred ranking
isn't something that needs to be completed immediately. The preferred
ranking can have 1's assigned until you have the time to fill it out to make
the parts-ordering operation more cost-effiecient.

Is it input by access or
is it done manually.

Initially, I'd recommend using an update query to find the lowest price of
any given equivalent part, which assigns a 1 to the lowest, a 2 to the next
lowest, and so on. However, you may prefer to do it manually.

Are the columns in the equivPartstbl just going to be
numbers

Yes. These are the primary key, the foreign keys, and a numeric ranking.

or can they contain the part number and suppliers name?

No. The part number and the supplier's name are located in the
SupplierParts table, which the foreign key, SPID, points to.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Hi Gunny,

I can see where you are going with the tables you have suggested, but I am
weary of my knowledge of Access to put it all together. I have created
the
tables you suggested and I noticed that you did not mention the models
table
or ID anywhere. The way it is set up now is to show 1 part to many
models.
For example, 1 light bulb from Napa is applicable to many makes and
models.
Also, could you explain how the relationships will work and what join
types
to use? How do I accomplish the preferred ranking? Is it input by access
or
is it done manually. Are the columns in the equivPartstbl just going to
be
numbers or can they contain the part number and suppliers name? Thanks so
much for your help.
--
Aloha,
Ron A.

Hi, Ron.

I recommend breaking the PartsTbl table into two tables, one for the
parts,
one for the supplier parts, and adding a third table for the equivalent
parts. For example:

Partstbl:
[partID] <-- primary key
[PartName]
[NSN]
[CategoryID] <-- foreign key from Categories table
[Remarks]

SupplierParts:
SPID, AutoNumber, primary key
[PartNo]
[SupplierID] <-- foreign key from Suppliers table
[UnitPrice]

EquivParts:
EPID, AutoNumber, primary key
[partID] <-- foreign key from Partstbl table
SPID<-- foreign key from SupplierParts table
PrefSupRank (preferred supplier ranking)

The EquivParts table would store the PrefSupRank in addtion to the PartID
and the SPID, because you always want the best price for the part.
Here's
how it would work:

Partstbl:
partID PartName NSN CategoryID Remarks
16 Head gasket, 4.3L Eng. blah 8 blah blah
23 Hose clamp, 2" blah 3 blah blah

EquivParts:
EPID partID SPID PrefSupRank
1 16 43 2
2 16 72 3
3 16 109 1
4 23 256 1
5 23 387 2

In the above scenario, three suppliers can provide the head gasket, and
the
supplier who supplies SPID 109 gives the best price, since it's ranked at
#1
for this particular part. Likewise, the supplier who supplies SPID 43
gives
the next best price. Two suppliers can provide the hose clamp, and the
supplier who supplies SPID 256 gives the best price.

When the actual part is purchased/installed on the vehicle/whatever you
need
this part info for, the EPID will be recorded in the database, not the
PartID, so that the actual supplier and unit price can be derived, and
later
on, trends can be determined about the preferred supplier, such as one
supplier has the best price for alternators, but rarely seems to have
them
in stock, so in the interest of saving time (read: $$$), this supplier
isn't the first supplier to check for alternators, which means this
ranking
will change to a higher number.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

'69 Camaro,

Great info, it explained alot. Once I made the changes to correct the
subdatasheet problem I then started putting in vehicle part infomation
and
realized that I had not designed for different suppliers part numbers.
I
have one part related to different vehicle models, but not multiple
suppliers
and their different part numbers for the same part. Can anyone with
the
info
I provided about my database earlier in this post help me out? I don't
want
to have to reenter each part info for each different supplier(ie...
Napa,
Redline and Ford).
--
Aloha,
Ron A.


:

Hi, Ron.

If I understand your question correctly, the subdatasheet in the
Partstbl
shows the related records in the Modeltbl table, and when that
subdatasheet's
records are selected, they show records in the Partstbl table, and so
on,
recursively.

The reason for this is that the table on the many side has the table
on
the
one side selected for the Subdatasheet Name Property. If you change
this
property to [None] (recommended) or [Auto], this phenomenon should
disappear.
Open the table in Design View, right-click the Title Bar and select
Properties on the pop-up menu to open the Properties dialog window and
select
the Subdatasheet Name Property and change it from Table.Partstbl to
either
[None] (preferable) or [Auto]. Save the table and open it in
Datasheet
View
and it shouldn't have any plus signs on the left of the record
selectors.

While subdatasheets can be convenient, they can cause problems and
impede
database performance, so most experts recommend turning this option
off
(set
it to [None]). Please see Allen Browne's (MVP) Web site for more
information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has
available
under the "Tables: SubdatasheetName" section on this Web page to turn
off
this default property for all tables in the database at once. And
while
you're on that Web page, the code under the "Fields: Allow Zero
Length"
section should probably be copied, pasted, and run on this database to
fix
this ill-advised default property as well, and then this property
turned
back
on manually only for the very rare occasions when it is needed. Check
out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember
that
questions answered the quickest are often from those who have a
history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

As most of you probably know from previous posts, I am creating a
parts
relational database. So far with some great help from this
discussion
group
it is working well except for a glitch in my relationships. I have
created
one to many relationships between the partID and PartID (foreign
key)
in the
Model table to relate many parts for 1 model type. The issue is
that
when
viewing the parts table I click on the subdatasheet and it is the
models
related to the partID wich is fine, but the model subdatasheet show
another
subdatasheet wich turns out to be parts. It is a subdatasheet with
the
field
from the parts table, but the data is blank. Why is a parts
subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key
[ComapanyName]
[ContactName]
[ContactTitle]
[Address]
[Phone]

Categoriestbl:
[CategoryID] <-- primary key
[CategoryName]

[PartID] from Partstbl has a one-to-many with [PartID] in Modeltbl
(join
type 2).
[SupplierID] from Supplierstbl has a one-to-many with [SupplierID]
in
Partstbl (join type 2). [CategoryID] from Categoriestbl has
one-to-many with
[CategoryID] in Partstbl (join type 3 and no referential integrity).

Hope this explains enough. Any help is greatly appreciated.

 

[Ron A.]

Wow, both of you are reading my mind. I will forge through the great advice
and see what comes out. I am sure there will be more posts to come as I
progress. Thanks again for all the help.
--
Aloha,
Ron A.

Hi, Ron.

I noticed that you did not mention the models table
or ID anywhere.

All the other tables remain the same as your original design. I merely
suggested splitting the Partstbl into two tables and adding a third to
identify different suppliers' equivalent parts.

The way it is set up now is to show 1 part to many models.

You may still use this design, since the PartID is still used as the foreign
key in the Modeltbl table.

How do I accomplish the preferred ranking?

This is subjective on your part. At first, you may want to use the cheapest
supplier for any given part, but there are other business reasons for
preferring a different supplier for certain parts. The preferred ranking
isn't something that needs to be completed immediately. The preferred
ranking can have 1's assigned until you have the time to fill it out to make
the parts-ordering operation more cost-effiecient.

Is it input by access or
is it done manually.

Initially, I'd recommend using an update query to find the lowest price of
any given equivalent part, which assigns a 1 to the lowest, a 2 to the next
lowest, and so on. However, you may prefer to do it manually.

Are the columns in the equivPartstbl just going to be
numbers

Yes. These are the primary key, the foreign keys, and a numeric ranking.

or can they contain the part number and suppliers name?

No. The part number and the supplier's name are located in the
SupplierParts table, which the foreign key, SPID, points to.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Hi Gunny,

I can see where you are going with the tables you have suggested, but I am
weary of my knowledge of Access to put it all together. I have created
the
tables you suggested and I noticed that you did not mention the models
table
or ID anywhere. The way it is set up now is to show 1 part to many
models.
For example, 1 light bulb from Napa is applicable to many makes and
models.
Also, could you explain how the relationships will work and what join
types
to use? How do I accomplish the preferred ranking? Is it input by access
or
is it done manually. Are the columns in the equivPartstbl just going to
be
numbers or can they contain the part number and suppliers name? Thanks so
much for your help.
--
Aloha,
Ron A.

Hi, Ron.

I recommend breaking the PartsTbl table into two tables, one for the
parts,
one for the supplier parts, and adding a third table for the equivalent
parts. For example:

Partstbl:
[partID] <-- primary key
[PartName]
[NSN]
[CategoryID] <-- foreign key from Categories table
[Remarks]

SupplierParts:
SPID, AutoNumber, primary key
[PartNo]
[SupplierID] <-- foreign key from Suppliers table
[UnitPrice]

EquivParts:
EPID, AutoNumber, primary key
[partID] <-- foreign key from Partstbl table
SPID<-- foreign key from SupplierParts table
PrefSupRank (preferred supplier ranking)

The EquivParts table would store the PrefSupRank in addtion to the PartID
and the SPID, because you always want the best price for the part.
Here's
how it would work:

Partstbl:
partID PartName NSN CategoryID Remarks
16 Head gasket, 4.3L Eng. blah 8 blah blah
23 Hose clamp, 2" blah 3 blah blah

EquivParts:
EPID partID SPID PrefSupRank
1 16 43 2
2 16 72 3
3 16 109 1
4 23 256 1
5 23 387 2

In the above scenario, three suppliers can provide the head gasket, and
the
supplier who supplies SPID 109 gives the best price, since it's ranked at
#1
for this particular part. Likewise, the supplier who supplies SPID 43
gives
the next best price. Two suppliers can provide the hose clamp, and the
supplier who supplies SPID 256 gives the best price.

When the actual part is purchased/installed on the vehicle/whatever you
need
this part info for, the EPID will be recorded in the database, not the
PartID, so that the actual supplier and unit price can be derived, and
later
on, trends can be determined about the preferred supplier, such as one
supplier has the best price for alternators, but rarely seems to have
them
in stock, so in the interest of saving time (read: $$$), this supplier
isn't the first supplier to check for alternators, which means this
ranking
will change to a higher number.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


'69 Camaro,

Great info, it explained alot. Once I made the changes to correct the
subdatasheet problem I then started putting in vehicle part infomation
and
realized that I had not designed for different suppliers part numbers.
I
have one part related to different vehicle models, but not multiple
suppliers
and their different part numbers for the same part. Can anyone with
the
info
I provided about my database earlier in this post help me out? I don't
want
to have to reenter each part info for each different supplier(ie...
Napa,
Redline and Ford).
--
Aloha,
Ron A.


:

Hi, Ron.

If I understand your question correctly, the subdatasheet in the
Partstbl
shows the related records in the Modeltbl table, and when that
subdatasheet's
records are selected, they show records in the Partstbl table, and so
on,
recursively.

The reason for this is that the table on the many side has the table
on
the
one side selected for the Subdatasheet Name Property. If you change
this
property to [None] (recommended) or [Auto], this phenomenon should
disappear.
Open the table in Design View, right-click the Title Bar and select
Properties on the pop-up menu to open the Properties dialog window and
select
the Subdatasheet Name Property and change it from Table.Partstbl to
either
[None] (preferable) or [Auto]. Save the table and open it in
Datasheet
View
and it shouldn't have any plus signs on the left of the record
selectors.

While subdatasheets can be convenient, they can cause problems and
impede
database performance, so most experts recommend turning this option
off
(set
it to [None]). Please see Allen Browne's (MVP) Web site for more
information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has
available
under the "Tables: SubdatasheetName" section on this Web page to turn
off
this default property for all tables in the database at once. And
while
you're on that Web page, the code under the "Fields: Allow Zero
Length"
section should probably be copied, pasted, and run on this database to
fix
this ill-advised default property as well, and then this property
turned
back
on manually only for the very rare occasions when it is needed. Check
out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember
that
questions answered the quickest are often from those who have a
history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

As most of you probably know from previous posts, I am creating a
parts
relational database. So far with some great help from this
discussion
group
it is working well except for a glitch in my relationships. I have
created
one to many relationships between the partID and PartID (foreign
key)
in the
Model table to relate many parts for 1 model type. The issue is
that
when
viewing the parts table I click on the subdatasheet and it is the
models
related to the partID wich is fine, but the model subdatasheet show
another
subdatasheet wich turns out to be parts. It is a subdatasheet with
the
field
from the parts table, but the data is blank. Why is a parts
subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key

 

['69 Camaro]

You're welcome. Good luck.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Wow, both of you are reading my mind. I will forge through the great
advice
and see what comes out. I am sure there will be more posts to come as I
progress. Thanks again for all the help.
--
Aloha,
Ron A.

Hi, Ron.

I noticed that you did not mention the models table
or ID anywhere.

All the other tables remain the same as your original design. I merely
suggested splitting the Partstbl into two tables and adding a third to
identify different suppliers' equivalent parts.

The way it is set up now is to show 1 part to many models.

You may still use this design, since the PartID is still used as the
foreign
key in the Modeltbl table.

How do I accomplish the preferred ranking?

This is subjective on your part. At first, you may want to use the
cheapest
supplier for any given part, but there are other business reasons for
preferring a different supplier for certain parts. The preferred ranking
isn't something that needs to be completed immediately. The preferred
ranking can have 1's assigned until you have the time to fill it out to
make
the parts-ordering operation more cost-effiecient.

Is it input by access or
is it done manually.

Initially, I'd recommend using an update query to find the lowest price
of
any given equivalent part, which assigns a 1 to the lowest, a 2 to the
next
lowest, and so on. However, you may prefer to do it manually.

Are the columns in the equivPartstbl just going to be
numbers

Yes. These are the primary key, the foreign keys, and a numeric ranking.

or can they contain the part number and suppliers name?

No. The part number and the supplier's name are located in the
SupplierParts table, which the foreign key, SPID, points to.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Hi Gunny,

I can see where you are going with the tables you have suggested, but I
am
weary of my knowledge of Access to put it all together. I have created
the
tables you suggested and I noticed that you did not mention the models
table
or ID anywhere. The way it is set up now is to show 1 part to many
models.
For example, 1 light bulb from Napa is applicable to many makes and
models.
Also, could you explain how the relationships will work and what join
types
to use? How do I accomplish the preferred ranking? Is it input by
access
or
is it done manually. Are the columns in the equivPartstbl just going
to
be
numbers or can they contain the part number and suppliers name? Thanks
so
much for your help.
--
Aloha,
Ron A.


:

Hi, Ron.

I recommend breaking the PartsTbl table into two tables, one for the
parts,
one for the supplier parts, and adding a third table for the
equivalent
parts. For example:

Partstbl:
[partID] <-- primary key
[PartName]
[NSN]
[CategoryID] <-- foreign key from Categories table
[Remarks]

SupplierParts:
SPID, AutoNumber, primary key
[PartNo]
[SupplierID] <-- foreign key from Suppliers table
[UnitPrice]

EquivParts:
EPID, AutoNumber, primary key
[partID] <-- foreign key from Partstbl table
SPID<-- foreign key from SupplierParts table
PrefSupRank (preferred supplier ranking)

The EquivParts table would store the PrefSupRank in addtion to the
PartID
and the SPID, because you always want the best price for the part.
Here's
how it would work:

Partstbl:
partID PartName NSN CategoryID Remarks
16 Head gasket, 4.3L Eng. blah 8 blah blah
23 Hose clamp, 2" blah 3 blah
blah

EquivParts:
EPID partID SPID PrefSupRank
1 16 43 2
2 16 72 3
3 16 109 1
4 23 256 1
5 23 387 2

In the above scenario, three suppliers can provide the head gasket,
and
the
supplier who supplies SPID 109 gives the best price, since it's ranked
at
#1
for this particular part. Likewise, the supplier who supplies SPID 43
gives
the next best price. Two suppliers can provide the hose clamp, and
the
supplier who supplies SPID 256 gives the best price.

When the actual part is purchased/installed on the vehicle/whatever
you
need
this part info for, the EPID will be recorded in the database, not the
PartID, so that the actual supplier and unit price can be derived, and
later
on, trends can be determined about the preferred supplier, such as one
supplier has the best price for alternators, but rarely seems to have
them
in stock, so in the interest of saving time (read: $$$), this
supplier
isn't the first supplier to check for alternators, which means this
ranking
will change to a higher number.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


'69 Camaro,

Great info, it explained alot. Once I made the changes to correct
the
subdatasheet problem I then started putting in vehicle part
infomation
and
realized that I had not designed for different suppliers part
numbers.
I
have one part related to different vehicle models, but not multiple
suppliers
and their different part numbers for the same part. Can anyone with
the
info
I provided about my database earlier in this post help me out? I
don't
want
to have to reenter each part info for each different supplier(ie...
Napa,
Redline and Ford).
--
Aloha,
Ron A.


:

Hi, Ron.

If I understand your question correctly, the subdatasheet in the
Partstbl
shows the related records in the Modeltbl table, and when that
subdatasheet's
records are selected, they show records in the Partstbl table, and
so
on,
recursively.

The reason for this is that the table on the many side has the
table
on
the
one side selected for the Subdatasheet Name Property. If you
change
this
property to [None] (recommended) or [Auto], this phenomenon should
disappear.
Open the table in Design View, right-click the Title Bar and
select
Properties on the pop-up menu to open the Properties dialog window
and
select
the Subdatasheet Name Property and change it from Table.Partstbl to
either
[None] (preferable) or [Auto]. Save the table and open it in
Datasheet
View
and it shouldn't have any plus signs on the left of the record
selectors.

While subdatasheets can be convenient, they can cause problems and
impede
database performance, so most experts recommend turning this option
off
(set
it to [None]). Please see Allen Browne's (MVP) Web site for more
information:

http://allenbrowne.com/bug-09.html

I'd recommend that you copy, paste, and run the code Allen has
available
under the "Tables: SubdatasheetName" section on this Web page to
turn
off
this default property for all tables in the database at once. And
while
you're on that Web page, the code under the "Fields: Allow Zero
Length"
section should probably be copied, pasted, and run on this database
to
fix
this ill-advised default property as well, and then this property
turned
back
on manually only for the very rare occasions when it is needed.
Check
out
the other great tips Allen has provided on his Web site.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember
that
questions answered the quickest are often from those who have a
history
of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

As most of you probably know from previous posts, I am creating a
parts
relational database. So far with some great help from this
discussion
group
it is working well except for a glitch in my relationships. I
have
created
one to many relationships between the partID and PartID (foreign
key)
in the
Model table to relate many parts for 1 model type. The issue is
that
when
viewing the parts table I click on the subdatasheet and it is the
models
related to the partID wich is fine, but the model subdatasheet
show
another
subdatasheet wich turns out to be parts. It is a subdatasheet
with
the
field
from the parts table, but the data is blank. Why is a parts
subdatasheet
being shown? The tables and relationship are as follows:

Partstbl:
[partID] <-- primary key
[PartName]
[PartNo]
[NSN]
[SupplierID] <-- foreign key from Suppliers table
[CategoryID] <-- foreign key from Categories table
[UnitPrice]
[Remarks]

Modeltbl:
[ModelID] <-- primary key
[ModelYear]
[Make]
[Model]
[Manufacturer]
[PartID] <-- foreign key from Parts table

Supplierstbl:
[SupplierID] <-- primary key

 

[Ron A.]

I have the tables and relationships setup, but I am having trouble designing
a form for inputing the info. I am not sure what setup would work best. Do
you have any suggestions?