Right now I have a flat database with too many unnormalized fields. I am trying to figure out how to break down the repeated "QuantityOrdered" 1 through 5. "QuantityReceived" 1 through 5. "RequestedItems" 1 through 5 and "Price" per unit 1 through 5. I'm not sure if they go into the same tables, if they are each listed 5 times and if in different tables how many instances 1 or 5 to show each as seperate and how to create a relationship between them.
normalizing data
- Thread starter gizmogeek
- Start date
Can you show all the fields of this table along with some sample data? Do all the same numbered fields' data go together? Does QuantityOrdered1 relate to QuantityReceived1 relate to RequestedItem1, etc and so on?
Also, is the numbers significant? Do they designate an order of items you need to keep? Does QuantityOrdered1 have a priority over QuantityOrdered2? Or is it that you just have 5 items and you decided to add numbers to them, but the numbers themselves have no meaning?
Again, sample data would be prefered.
Also, is the numbers significant? Do they designate an order of items you need to keep? Does QuantityOrdered1 have a priority over QuantityOrdered2? Or is it that you just have 5 items and you decided to add numbers to them, but the numbers themselves have no meaning?
Again, sample data would be prefered.
- Local time
- Yesterday, 21:37
- Joined
- Jan 23, 2006
- Messages
- 15,575
Here's a tutorial to give you the general procedure.
Work through the example, then try with your own data.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
Work through the example, then try with your own data.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
Yeah, this is normalization 101. You really need to start from scratch learning about how to properly set up a database. Luckily Invoice/Purchase Orders are a topic that have been covered a lot in this forum. I'd read up on the link jdraw posted, try and lay out your database properly and then come back here with questions.
In response to your initial issue: What you would need is a seperate table called something like 'PurchaseOrderItems' which would hold a foreign key to tblPurchaseOrder a foreign key from tblItemList and then one field for every numerated one you listed prior.
Again though, read up on normalization first.
In response to your initial issue: What you would need is a seperate table called something like 'PurchaseOrderItems' which would hold a foreign key to tblPurchaseOrder a foreign key from tblItemList and then one field for every numerated one you listed prior.
Again though, read up on normalization first.
I don't know what to tell you, you still have a plague of errors. You really need to read up on normalization. You don't mention what table this Supplier field is in, I'm guessing tblItemList. If so, Supplier shouldn't be a primary key in this table. Nor should the Supplier field be text in this field. It should contain a numeric foreign key to tblSupplierList.
Also, tlbSupplierList shouldn't have one field called 'City/State'. First you should only use alphanumeric characters and underscores in object names. Second, each discrete piece of data needs to be in its own field. That mans a field for City and a field for State.
Additionally, you created tblPurchaseOrderItems but put too many fields in it. Tables should have data vertically not horizontally. Instead of all those numerated field names (TotalInventory1, TotalInventory2...) you should just have 1 field called 'TotalInventory' and then add rows of data if you need to add more.
Again, read up on normalization for a day. You have lots and lots of basic errors.
Also, tlbSupplierList shouldn't have one field called 'City/State'. First you should only use alphanumeric characters and underscores in object names. Second, each discrete piece of data needs to be in its own field. That mans a field for City and a field for State.
Additionally, you created tblPurchaseOrderItems but put too many fields in it. Tables should have data vertically not horizontally. Instead of all those numerated field names (TotalInventory1, TotalInventory2...) you should just have 1 field called 'TotalInventory' and then add rows of data if you need to add more.
Again, read up on normalization for a day. You have lots and lots of basic errors.
