Table Structure

[Secret Squirrel]

Having a bit of a hard time trying to figure this one out. Maybe it's under
my nose but I still can't seem to work it out.

I have a list of part numbers in a spreadsheet that we purchase. In column A
I have the part number, in column B is the description and in column C is the
vendor #. I want to put this into a table in my database but the same part
number is listed more than once because it can be purchased from different
vendors. Can someone help me figure out how I can put the part numbers into
one table but then link it to the table where I store my vendors? How can I
link the part numbers to all the vendors that it can be purchased from? Hope
I'm explaining this correctly....

SS

 

[Mr B]

Secret Squirrel,

This is the old "Many to many" thing.

Seriously, you need to create a third table with two fields. Let's call it
"tblLinkPartsVendor" One field for the VendorId from the Vendors table and
the other for the PartId from the Parts table.

Then for each part you would write a record to the new table for each Vendor
from which the part can be purchased.

 

[Allen Browne]

This is not a simple question. There are so many things that can go wrong
with an import.

Import the spreadsheet to a temporary table where you can manipulate the
data. I suggest importing rather than merely linking because it's easy to
manipulate and query the data, and it also addresses issues that a link
doesn't (such as Access guessing the wrong data type.)

Once you have the data in the table, you can create a query that selects
only the distinct values, and use the Unmatched Query Wizard to choose the
one's that don't already exist. After visually scanning to catch any
mis-spelled names, you can then write these values into your other table.
Then add a new column to the temp table to store the key value (assuming its
an AutoNumber and not the actual unique name.)

Now you can perform other checks on the temporary table (such as handling
other lookup fields, identifying duplicate values that are supposed to be
unique, finding blank values where the target is a required field, removing
bad data such as "N/A" in a date field, and pinning down values that don't
match any validation rules you have in your tables.

Finally, once you've sorted it all out, you can write to your real table,
and remove the temporary table.

If this is a one-off import, you do all that yourself. If it's a regular
occurance, you can write an interface and program it to handle these steps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Secret Squirrel]

Hi Mr B,

Thanks for the response. I was thinking the same thing as you but have a
couple of questions....

A) How do I link my 3 tables together?
B) If I add a new part number to the "tblParts" how can I automatically add
that part number to the "tblLinkPartsVendors" and then add a vendorid to that
table as well?

I would be doing this via form using the NotInList event to add it to my
tblParts.

 

[Secret Squirrel]

Hi Allen,

Thanks for the response. I'm not having a problem with the import. It's more
on how I structure my tables to link the part numbers table with my vendors
table when there is more than 1 vendor that I can purchase a particular item
from. I want to be able to link 1 part number to multiple vendors so that
when a user selects a part number on my form they can then select a specifc
vendor to use to purchase that item.

SS

 

[Mr B]

Secret Squirrel,

You would like your tables by linking the PartId in the Parts table to the
PartId in the "tblLinkPartsVendor" table and link the VendorID in the
"tblLinkPartsVendor" to the VedorID in the Vendors table.

As for adding a new record, I would assume that even if you are going to use
the NotInList event of a combo box, you would still be presenting a form to
users where all of the infor for each part will be entered. In that same
form you would need to have a list of the Vendors from which is would be
available. You would also need to present a list of current Vendors and
allow users to select the Vendor from that list. (This list could and might
need to be a Multi Select type list box.) In any event, when the user
selects the Vendor from the current list of Vendors, you would have code that
would add the record to the "tblLinkPartsVendor" table for the new Part and
the selected Vendor. You should probably think about how to deal with the
fact that a Vendor may suddenly no longer supply a specific part. This may
mean that you need to add an "Active/Inactive" field as a third field to the
"tblLinkPartsVendor" table so you can flag the link between part and vendor
as no longer active. (just thinking outside the box)

This is not exactly a really simple process but it is the only way to really
have the correct management of this type of data.

 

[Secret Squirrel]

Do I need to have a primary key in the tblLinkPartsVendor or just the
vendorid and the partid?

 

[Allen Browne]

message

Thanks for the response. I'm not having a problem with the import. It's
more
on how I structure my tables to link the part numbers table with my
vendors
table when there is more than 1 vendor that I can purchase a particular
item
from. I want to be able to link 1 part number to multiple vendors so that
when a user selects a part number on my form they can then select a
specifc
vendor to use to purchase that item.

I am assuming you have a relational structure with at least these tables:
- vendor table (one record per company)
- parts table (one record per part number)
- junction table (one record per combination.)

If so, when you perform the import, you must examine the incoming data to
see:
a) Does the vendor already exist, or is this new one?
b) Does the part already exist, or is this a new one?
(Further, if the spreadsheet has both the number and the name, you may want
to check for mismatches.)

Then you need to match whatever is the primary key values in your tables to
the actual numbers used in the import, in order to know the values to store
in the junction table.

But perhaps none of this is relevant, since you say:
I'm not having a problem with the import.

 

[Mr B]

You do not absolutely have to have a PK in the linking table just the two
fields will suffice. There is nothing wrong with have another field for a PK
if you want. In this case you would now be up to 4 fields in this table.

 

[huangda 晋江兴达机械]

Having a bit of a hard time trying to figure this one out. Maybe it's
under
my nose but I still can't seem to work it out.

I have a list of part numbers in a spreadsheet that we purchase. In column
A
I have the part number, in column B is the description and in column C is
the
vendor #. I want to put this into a table in my database but the same part
number is listed more than once because it can be purchased from different
vendors. Can someone help me figure out how I can put the part numbers
into
one table but then link it to the table where I store my vendors? How can
I
link the part numbers to all the vendors that it can be purchased from?
Hope
I'm explaining this correctly....

SS