Basic table structure/relationships for a vendor database

[parkerk]

I am developing a vendor management database for my company (an industrial
distributor). The database seems like it should be simple, but this all new
and confusing to me (new Access 2003 user.) My goal is to allow employees to
query vendors (about 200) and products (about 200) to determine:
1. Which vendors make a given product,
2. Who is the preferred vendor of a given product

I've started with 2 tables – tblVendor and tblProduct, with a many-to-many
relationship. Do I simply build a third table, tblVendorProduct as a
junction table to allow for the queries that I need?

 

[Lynn Trapp]

I've started with 2 tables - tblVendor and tblProduct, with a many-to-many

relationship. Do I simply build a third table, tblVendorProduct as a
junction table to allow for the queries that I need?

The third table is a bare minimum requirement for what you need. If you ever
deal with vendors that have multiple locations you may need a 4th table --
tblVendorSites. If you do that, then you will probably want to tie the
tblProducts to this table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[parkerk]

Lynn - Thanks for your help....

In the very unusual event that there are multiple locations, as in purchases
from different divisions, we would enter the other location as a separate
vendor record.

OK, I've created the junction table VendorProduct, and it is on the many
side of the one-many relationship with tblVendor and tblProduct. I then
loaded in some sample data and tried to do a basic query, but am having no
luck getting a successful outcome. The tables are set up as follows:

tblVendor
VendorID PK AN
VandorName
various vendor contact fields

tblProduct
ProductID PK AN
ProductName

tblVendorProduct
VendorProductID PK AN
VendorID
ProductID

Does this look right?

 

[Lynn Trapp]

Yes, your table structure looks fine. Can you post the SQL for the query you
tried?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[parkerk]

Yikes - not sure what an SQL is, but I can tell you what I did. I went into
the query wizard and tried many different combinations of fields, with no
luck. I have already loaded in all my product names, and I loaded in 2
sample vendors. I then went into tblVendorProduct and loaded in several
records - each vendor and 3 products that they carried. I thought I'd see
if I could do a simple query to show me the vendor names and matching product
names.

 

[gls858]

Yikes - not sure what an SQL is, but I can tell you what I did. I went into
the query wizard and tried many different combinations of fields, with no
luck. I have already loaded in all my product names, and I loaded in 2
sample vendors. I then went into tblVendorProduct and loaded in several
records - each vendor and 3 products that they carried. I thought I'd see
if I could do a simple query to show me the vendor names and matching product
names.

:

With the query in design view click on View in the menu bar.
That will open a window with the SQL info requested. Just cut
and paste it into your reply.

gls858

 

[parkerk]

Thanks - I see the process. I did a sample try - as I mentioned my goal in
this query is to take the data from tblVendorProduct, and show the vendor
names with matching product names, taken from tblVendor and tblProduct. I
hope this try doesn't look as silly as I think it may!

SELECT Products.ProductName, Suppliers.SupplierName,
SupplierProduct.ProductID, SupplierProduct.SupplierID
FROM Suppliers INNER JOIN (Products INNER JOIN SupplierProduct ON
Products.ProductID = SupplierProduct.ProductID) ON Suppliers.SupplierID =
SupplierProduct.SupplierID;