Table Design Issue

[Guest]

I have a database the has a Parts Table and a Vendors Table. what needs to
happen is i need a form that will allow someon to view all the information
about that parts itself and the vendors that supply the part but every
part is available by all 5 vendors for different cost, and i need to
construct tables that when part information is undated or deleted it will
reflect across the board and on the form itself be able to see the vendor
name and the part information for that vendor.

what type of relationships will be involved here

 

[Allen Browne]

Part table: one record for each part, with PartID primary key.

Vendor table: one record for each vendor, with VendorID p.k.

PartVendor table, with fields:
PartID Number relates to Part.PartID
VendorID Number relates to Vendor.VendorID
PartVendorCode Text the order code for this part from this vendor
PriceEachEx Currency how much this vendor charges for this part
Primary key can be the combination of PartID + VendorID.

When you create a Parts form, add a subform for the vendors and their parts.
The subform will have a combo for selecting the vendor.

 

[Guest]

What i need ot do is set up a table and/or form that i will be able to
enter the following infomation for an individual part, all this information
used to be allin one table and for some reason i didnt think it was the
correct way to have it so i spilt it all up into 3 diferent tables. The goal
is to be able to display each part and its information. Each part has 5
primary Vendors, and each vendor has 3 prices for volume purchasing and a new
and used price, and an OEM Price. all that information must be able to be
changed on the form because the prices can change day to day.


Old Products Table:

ProductID (PK) ProductName ProductDescription CategoryID
UnitPrice Discontinued ReorderLeve LeadTime
PartDeliveryTime SerialNumber
Vendor1Name Vendor1Contact Vendor1Address Vendor1City
Vendor1Region Vendor1ZipCode Vendor1Country Vendor1Phone
Vendor1Fax Vendor1Email Vendor1Website Vendor1County
Vendor1QouteDate Vendor2Name Vendor2Contact Vendor2Address
Vendor2City Vendor2Region Vendor2Country Vendor2ZipCode
Vendor2Phone Vendor2Fax Vendor2Email Vendor2Website
Vendor2Cost Vendor2QouteDate Vendor3Name Vendor3Contact
Vendor3Address Vendor3City Vendor3Region Vendor3Country
Vendor3ZipCode Vendor3Cost Vendor3QouteDate Vendor3Phone
Vendor3Fax Vendor3Email Vendor3Website Vendor4Name
Vendor4Contact Vendor4Address Vendor4City Vendor4Region
Vendor4Country Vendor4ZipCode Vendor4Phone Vendor4Fax
Vendor4Email Vendor4Website Vendor4Cost
Vendor4QouteDate Vendor5Name Vendor5Contact Vendor5Address
Vendor5City Vendor5Region Vendor5ZipCode Vendor5Country
Vendor5Phone Vendor5Fax Vendor5Email Vendor5Website
Vendor5Cost Vendor5QouteDate OEM OEMContact
OEMAddress OEMCity OEMRegion OEMZipCode
OEMCountry OEM Sell Price OEM DatePartLastSold OEM Phone
OEMFax OEMEmail OEMWebsite Remarks


TAble 1 - Products

ProductID (PK) OEM_ID Category ID Supplier_ID Product Name
Product Description Unit Price NewPrice UsedPrice Discontinued
PartDeliveryTime Remarks OEMPrice DatePartLastSold
VolDiscount_1 VolDiscount_Price1 VolDiscount_2
VolDiscount_Price2 VolDiscount_3 VolDiscount_Price3

Table 2 Suppliers (Vendors)

SupplierID (PK) SupplierName ContactName Contact Title
Address City Postal Code State/Province Country
PhoneNumber FaxNumber EmailAddress WebsiteAddress

Table 3 OEM

OEM_ID (PK) Product_ID Supplier_ID Product Name
Product Description Category ID Unit Price Discontinued
OEMName OEMContact OEMAddress OEMCity
OEMRegion OEMZipCode OEMCountry
OEM Phone OEMFax OEMEmail OEMWebsite
Remarks

 

[Allen Browne]

So you will create the tables as suggested, plus add one field to the
PartVendor table to handle the Quantity (or MinQuantity?) that the price
applies for.

You will then interface it as a main form (bound to the Part), with a
subform bound to the PartVendor table. When you bring up the part in the
main form, the relevant vendor purchase info. will show in the subform where
you can edit it.

 

[Guest]

i understnad that complety now, ecause i was asked to put in a text box to
edit and enter all that information and i knew thaat would require additional
colums to the table and i really wasnt tryin to go there

 

[Guest]

Allen, one more questin, i should go with th 3 seperate table, correct?

 

[Allen Browne]

Yes.