helped needed...

[londonworker]

I have a table containing the fields:

DeliveriesID // SupplierID // Date // ProductID // QTY // costprice // totalprice​

I need to make this table 1NF. I know I should make related tables but I'm not sure what fields go with which.

I have attached a copy of the database

 

[wazz]

tblDelivery (usually singular noun for table names)
DeliveryID (PK)
SupplierID (FK) (omit Company - found in Supplier table)
DeliveryDate

i imagine a delivery could involve many products. if so:

tblDeliveryDetails
DeliveryDetailsID (PK)
DeliveryID (FK)
ProductID (FK) (remove Product Name - found in tblProduct)
Quantity
Cost (probably store the costprice of a product in tblProduct (as you do) and look it up (for this field) as needed; don't store total cost in this table - calculate later as needed)

to be on the safe side, i would probably move all the supplier contact info out of tblSupplier and into your tblSupplierContact (many contacts for each supplier). might save you a headache later.

 

[londonworker]

thank you, deliveryID is autonumber but contains duplicats so can't be PK. The ProductID field is the only colume without duplicate data. Should these two fields be changed round?

 

[wazz]

oic.
it looks like you can get many products per delivery. you'll have to do some juggling of your data. try to get things more or less as described by cutting and pasting data into new tables. one thing you could do is make a composite (combined) PK in tblDeliveryDetails out of DeliveryID and ProductID (or add a new field, DeliveryDetailsID, make it AutoNumber and set Allow Duplicates to NO).

 

[londonworker]

thanks again i was actually talking about tblDelivery not the other one