Still designing, need help with Many-to-Many

Tables
---------------
tblItems: itemId (PK), & other item fields
tblVendors: vndId (PK), & other vendor fields
tblVitem: vitemId (PK), vitemReference (FK to itemId), vitemVendor (FK to
vndId)

I made the tblVitem by using the CTRL key and highlighting it's PK as well
as the new fields that are FK to their respective tables. I believe I read
it was called a Joined Table.

tblItems is a 1 to many to tblVitem, and tblVendors is a 1 to many to
tblvitem; butI am not sure a joined tblVitem is what I needed now.

What I need is way to have an item have many vendors, and a vendor to have
many items; for which I think is a many to many relationship with a Joined
Table.

1) Am I right in using a Joined table as I post above the correct way?



The reason I bring it up is that each vendor may have a different internal
Vendor Item Number for each item they sell. So I thought the primary key
for tblVitem would be a good place to hold that number while referencing the
Vendor for it and our internal item number of the part to purchase.

But when I view it the relationship window, it doesn't look right to me for
some reason. When I get some books it might make more sense, or I have it
wrong in the diagram. :-)


The goal here is to have an item table with details about the item, a vendor
table with details about the vendor, and then relate these 2 in such a way
that any vendor may have any part and vice versa. This is where I am stuck
at.

I am placing an order for a couple of books today now that I have decided on
which books I want, but I would still like to understand the above while I
wait for the books to arrive.


Tim

I made the tblVitem by using the CTRL key and highlighting it's PK as well
as the new fields that are FK to their respective tables. I believe I
read
it was called a Joined Table.

i'm afraid i don't understand the above, but your table design looks correct
to me; that's the standard model to express a many-to-many relationship:
two parent tables that each have a one-to-many relationship with the same
child (linking) table.

The reason I bring it up is that each vendor may have a different internal
Vendor Item Number for each item they sell. So I thought the primary key
for tblVitem would be a good place to hold that number while referencing
the
Vendor for it and our internal item number of the part to purchase.

suggest you consider the ramifications of using each vendor's item code as
the primary key in tblVitem. what happens when the vendor changes their
code, for instance? perhaps not a problem at all, perhaps not a big problem,
or perhaps major. an alternative is to use an autonumber for the primary key
field of tblVitem, and add an additional field to hold the vendor's item
number for each record. you'll need to think it through and decide which is
best. (if your company assigns an internal item number to *each* item from
*each* vendor, i'd be much more inclined to use that as the primary key in
tblVitems - if i decided not to use an autonumber.)

hth


"Parts Manager" wrote in message
...

Tables
---------------
tblItems: itemId (PK), & other item fields
tblVendors: vndId (PK), & other vendor fields
tblVitem: vitemId (PK), vitemReference (FK to itemId), vitemVendor (FK to
vndId)

I made the tblVitem by using the CTRL key and highlighting it's PK as well
as the new fields that are FK to their respective tables. I believe I
read
it was called a Joined Table.

tblItems is a 1 to many to tblVitem, and tblVendors is a 1 to many to
tblvitem; butI am not sure a joined tblVitem is what I needed now.

What I need is way to have an item have many vendors, and a vendor to have
many items; for which I think is a many to many relationship with a Joined
Table.

1) Am I right in using a Joined table as I post above the correct way?



The reason I bring it up is that each vendor may have a different internal
Vendor Item Number for each item they sell. So I thought the primary key
for tblVitem would be a good place to hold that number while referencing
the
Vendor for it and our internal item number of the part to purchase.

But when I view it the relationship window, it doesn't look right to me
for
some reason. When I get some books it might make more sense, or I have it
wrong in the diagram. :-)


The goal here is to have an item table with details about the item, a
vendor
table with details about the vendor, and then relate these 2 in such a way
that any vendor may have any part and vice versa. This is where I am
stuck
at.

I am placing an order for a couple of books today now that I have decided
on
which books I want, but I would still like to understand the above while I
wait for the books to arrive.


Tim

Tim,

Building on Tina's comments:

Table tblVitem is indeed the way to go, no doubt about that. I've seen a
few different names for this type of table, referring to "joining",
"resolving" etc. Not aware of a standard one.

Now, on the PK for this table: using the vendor's item number doesn't
look a very good idea to me either; what guarantee is there that two
different vendors will never use the same item number? None, I suppose.
Therefore, I would suggest that you use a separate field in tblVitem for
the vendor item number, so it can handle duplicates. Once the vendor and
the item are selected in your order form, the vendor item number can be
looked up univocally. Also, instead of using an autonumber (or whatever)
field as the PK for tblVitem, I would use a composite PK comprising
vitemReference and vitemVendor, so among other things it excludes
duplicates. I see no use for a separate PK field.

HTH,
Nikos

Parts Manager wrote:
Tables
---------------
tblItems: itemId (PK), & other item fields
tblVendors: vndId (PK), & other vendor fields
tblVitem: vitemId (PK), vitemReference (FK to itemId), vitemVendor (FK to
vndId)

I made the tblVitem by using the CTRL key and highlighting it's PK as well
as the new fields that are FK to their respective tables. I believe I read
it was called a Joined Table.

tblItems is a 1 to many to tblVitem, and tblVendors is a 1 to many to
tblvitem; butI am not sure a joined tblVitem is what I needed now.

What I need is way to have an item have many vendors, and a vendor to have
many items; for which I think is a many to many relationship with a Joined
Table.

1) Am I right in using a Joined table as I post above the correct way?



The reason I bring it up is that each vendor may have a different internal
Vendor Item Number for each item they sell. So I thought the primary key
for tblVitem would be a good place to hold that number while referencing the
Vendor for it and our internal item number of the part to purchase.

But when I view it the relationship window, it doesn't look right to me for
some reason. When I get some books it might make more sense, or I have it
wrong in the diagram. :-)


The goal here is to have an item table with details about the item, a vendor
table with details about the vendor, and then relate these 2 in such a way
that any vendor may have any part and vice versa. This is where I am stuck
at.

I am placing an order for a couple of books today now that I have decided on
which books I want, but I would still like to understand the above while I
wait for the books to arrive.


Tim

"tina" wrote
suggest you consider the ramifications of using each vendor's item code as
the primary key in tblVitem. what happens when the vendor changes their
code, for instance? perhaps not a problem at all, perhaps not a big
problem,
or perhaps major. an alternative is to use an autonumber for the primary
key
field of tblVitem, and add an additional field to hold the vendor's item
number for each record. you'll need to think it through and decide which
is
best. (if your company assigns an internal item number to *each* item from
*each* vendor, i'd be much more inclined to use that as the primary key in
tblVitems - if i decided not to use an autonumber.)

Yes, I am thinking about it now. :-)

We create 1 item number for any 1 MFG number. That MFG item number can be
purchased by many vendors. Had not considered using a separate internal
item number for each vendor it could be purchased from as then it would be
duplicating data, I think. I will double check the layout I have so far to
see if there is anything to doing it that way. I am reviewing my tables
today and see if there are ways I am not accounting for. I have been
looking at some sample database models and seeing if they can make sense to
what we do here.

Thanks for the heads up on a vendor changing their internal item number. I
have in the past created new item numbers for changes and reference the new
item number in the old item's description; See Part # XXX-XXX-XXX so that
the item still has previous history tracking of similar part.

Tim

"Nikos Yannacopoulos"
Table tblVitem is indeed the way to go, no doubt about that. I've seen a
few different names for this type of table, referring to "joining",
"resolving" etc. Not aware of a standard one.

Good, then I am heading in the right direction.


Now, on the PK for this table: using the vendor's item number doesn't look
a very good idea to me either; what guarantee is there that two different
vendors will never use the same item number? None, I suppose.

You are correct. After creating the table and reviewing it, I realized that
exact same thing. There are some vendors who will use the MFG Item Number
as their own number, so this is common. But then there are those vendors
who have their own internal numbers too. :-)

So I will need another solution to that problem.


Therefore, I would suggest that you use a separate field in tblVitem for
the vendor item number, so it can handle duplicates. Once the vendor and
the item are selected in your order form, the vendor item number can be
looked up univocally. Also, instead of using an autonumber (or whatever)
field as the PK for tblVitem, I would use a composite PK comprising
vitemReference and vitemVendor, so among other things it excludes
duplicates. I see no use for a separate PK field.

Yes, after stumbling upon what you mention of 2 vendors using the same item
number (mfg number usually), it occurred to me that maybe utilizing a vendor
prefix of some sort would help this.

You and Tina both have kept me along the path and gave me
options/suggestions for improvements. I thank you both and head back to the
tables and diagrams. I read designing is the staple to a good database, so
I am spending as much time as needed to get it right before we endeavor into
this project.

Tim

I thank you both and head back to the
tables and diagrams. I read designing is the staple to a good database,
so
I am spending as much time as needed to get it right before we endeavor
into
this project.

you're welcome, Tim. and you're absolutely right - proper data modeling
"up-front" is essential to building a good database. and never fear, you'll
be far more than repaid for the time you're spending now, in all the time
and headaches you'll save on down the line!


"Parts Manager" wrote in message
...
"Nikos Yannacopoulos"
Table tblVitem is indeed the way to go, no doubt about that. I've seen a
few different names for this type of table, referring to "joining",
"resolving" etc. Not aware of a standard one.

Good, then I am heading in the right direction.


Now, on the PK for this table: using the vendor's item number doesn't
look
a very good idea to me either; what guarantee is there that two
different
vendors will never use the same item number? None, I suppose.

You are correct. After creating the table and reviewing it, I realized
that
exact same thing. There are some vendors who will use the MFG Item Number
as their own number, so this is common. But then there are those vendors
who have their own internal numbers too. :-)

So I will need another solution to that problem.


Therefore, I would suggest that you use a separate field in tblVitem for
the vendor item number, so it can handle duplicates. Once the vendor and
the item are selected in your order form, the vendor item number can be
looked up univocally. Also, instead of using an autonumber (or whatever)
field as the PK for tblVitem, I would use a composite PK comprising
vitemReference and vitemVendor, so among other things it excludes
duplicates. I see no use for a separate PK field.

Yes, after stumbling upon what you mention of 2 vendors using the same
item
number (mfg number usually), it occurred to me that maybe utilizing a
vendor
prefix of some sort would help this.

You and Tina both have kept me along the path and gave me
options/suggestions for improvements. I thank you both and head back to
the
tables and diagrams. I read designing is the staple to a good database,
so
I am spending as much time as needed to get it right before we endeavor
into
this project.

Tim

Tina said it all...

you're welcome, Tim. and you're absolutely right - proper data modeling
"up-front" is essential to building a good database. and never fear, you'll
be far more than repaid for the time you're spending now, in all the time
and headaches you'll save on down the line!

Follow-up to this thread;

Using a joined table (tblVitem), I have the primary key as a composite of
vitemVendor & vitemReference.

The vitemId in this table is the vendors item number they use and will be
set to allow duplicates. It is merely to house the vendor number for
ordering.

I believe I am up to speed now on this first Many-To-Many table.

Now I am working on the purchase order table(s). I am working at the moment
on the purchase order detail table where a part can be ordered as well as
qty etc.... In this table I need to have a field (polnItemId) which is the
item to purchase.

The thought is that I run out of our internal part number and need to
reorder. That (itemId) is linked with a (vendorId) in the Joined table of
tblVitem. So my question is;

In my new table of tblpoline and the field of polnItemId, what field do I
relate to so that it all works proper?

Is my polnItemId:
A) itemId - internal item number from tblItem
B) vitemReference - from the joined table which is part and vendor

My thought, end of the work day here, is that I want to relate each line of
the purchase order to the Joined table and that Joined table will populate
from the internal item fields.

This is still table design and I have to work out the finer details of the
Joined table so that any internal part may be purchased by an vendor and the
reverse be true also. From that I need to create purchase order and invoice
tables. So I have not designed any forms or reports etc.... In my mind I
envision this form where I can enter the purchase order information, then
the line items. The line items would include;

Line ID (autonumber)
PO Number reference (Relate to purchase order)
ITEM TO ORDER (Stuck here, my internal, or the joined VitemId instead)
etc...


Those are the fields I am playing with at this time. All I understand
except, where to relate the actual item to order so that when a new purchase
order is written, that the item is actually available from that vendor.

Tim

sorry, Tim, i getting ready for finals, so don't have time to sit and ponder
your post so i can give you an intelligent answer. suggest you wait a day or
so to see if anybody else picks up the thread; if not, try starting a new
thread in this same newsgroup - there're lots of experienced folks here who
can help you out. good luck!


"Parts Manager" wrote in message
...
Follow-up to this thread;

Using a joined table (tblVitem), I have the primary key as a composite of
vitemVendor & vitemReference.

The vitemId in this table is the vendors item number they use and will be
set to allow duplicates. It is merely to house the vendor number for
ordering.

I believe I am up to speed now on this first Many-To-Many table.

Now I am working on the purchase order table(s). I am working at the
moment
on the purchase order detail table where a part can be ordered as well as
qty etc.... In this table I need to have a field (polnItemId) which is
the
item to purchase.

The thought is that I run out of our internal part number and need to
reorder. That (itemId) is linked with a (vendorId) in the Joined table of
tblVitem. So my question is;

In my new table of tblpoline and the field of polnItemId, what field do I
relate to so that it all works proper?

Is my polnItemId:
A) itemId - internal item number from tblItem
B) vitemReference - from the joined table which is part and vendor

My thought, end of the work day here, is that I want to relate each line
of
the purchase order to the Joined table and that Joined table will populate
from the internal item fields.

This is still table design and I have to work out the finer details of the
Joined table so that any internal part may be purchased by an vendor and
the
reverse be true also. From that I need to create purchase order and
invoice
tables. So I have not designed any forms or reports etc.... In my mind I
envision this form where I can enter the purchase order information, then
the line items. The line items would include;

Line ID (autonumber)
PO Number reference (Relate to purchase order)
ITEM TO ORDER (Stuck here, my internal, or the joined VitemId instead)
etc...


Those are the fields I am playing with at this time. All I understand
except, where to relate the actual item to order so that when a new
purchase
order is written, that the item is actually available from that vendor.

Tim

Tim,

Let me see if I understand this correctly:
First, you establish the need to reorder a certain qty of part X, X
being your internal part number (itemId in tblItems).
Then, you need to search tblVItem for records where vitemReference
equals the required itemId, so you have a list of vendors selling the
specific part. Am I guessing correct so far?
I guess you are using a form to enter the PO, on which you first select
the part in a listbox or combo, and then a second listbox or combo for
vendors is filtered based on the previous selection? Or what? Anyway, my
assumption is that after you select the part you have some means of
selecting a vendor on the same form, so that both (internal) part ID and
vendor ID are now available on the form. The question is how to identify
the chosen vendor's part number for the given part (to communicate that
to the vendor), right? Well, it takes no more than a simple DLookup on
table tblVItem!
Is this helping? If you need more specific help, please post back the
details of your form (form name, type and name of listboxes, comboboxes,
textbox where you want the vendor's item number entered etc.).
I hope I'm not on the wrong track altogether!

Nikos

Parts Manager wrote:
Follow-up to this thread;

Using a joined table (tblVitem), I have the primary key as a composite of
vitemVendor & vitemReference.

The vitemId in this table is the vendors item number they use and will be
set to allow duplicates. It is merely to house the vendor number for
ordering.

I believe I am up to speed now on this first Many-To-Many table.

Now I am working on the purchase order table(s). I am working at the moment
on the purchase order detail table where a part can be ordered as well as
qty etc.... In this table I need to have a field (polnItemId) which is the
item to purchase.

The thought is that I run out of our internal part number and need to
reorder. That (itemId) is linked with a (vendorId) in the Joined table of
tblVitem. So my question is;

In my new table of tblpoline and the field of polnItemId, what field do I
relate to so that it all works proper?

Is my polnItemId:
A) itemId - internal item number from tblItem
B) vitemReference - from the joined table which is part and vendor

My thought, end of the work day here, is that I want to relate each line of
the purchase order to the Joined table and that Joined table will populate
from the internal item fields.

This is still table design and I have to work out the finer details of the
Joined table so that any internal part may be purchased by an vendor and the
reverse be true also. From that I need to create purchase order and invoice
tables. So I have not designed any forms or reports etc.... In my mind I
envision this form where I can enter the purchase order information, then
the line items. The line items would include;

Line ID (autonumber)
PO Number reference (Relate to purchase order)
ITEM TO ORDER (Stuck here, my internal, or the joined VitemId instead)
etc...


Those are the fields I am playing with at this time. All I understand
except, where to relate the actual item to order so that when a new purchase
order is written, that the item is actually available from that vendor.

Tim