need help normalizing data

[quackslikeaduck]

I have an old database that needs to be normalized. It's flat right now. I am hoping someone can help me to figure out how to organize the data. I have 5 fields within the current table QuantityOrdered1, QuantityOrdered2, etc...

I know that at some point I will run into problems if I have to add more QuantityOrdered. How do I take them out of the main table and put them into a table all their own? Do I list them out like they are now or just one instance of QuantityOrdered? For instance if someone orders something right now they open the form and fill out a quantity for each order on the form. So right now they could have ordered 5 different parts and they fill in a a text box for each of the parts.

 

[johnherman]

Order is the master table.
Order item is the detail table. (or Order line, or Order detail, etc)

====================================
Sometimes the grass is greener on the other side because there is more manure there - original.

 

[Andrzejek]

Have a table QuantityOrdered:

[pre]
OrderID Quantity ItemNo(FK)
4 23 5
4 123 47
4 5 4
4 1 1
5 12 5
5 55 47
[/pre]

Another table will keep the Items:

[pre]
ItemNo(PK) ItemDesc
5 Toyota
47 Fiat
4 BMW
1 Ford
[/pre]

OrderID 4 ordered four Items: 23 Toyotas, 47 Fiats, 4BMWs, and 1 Ford

No limits how many Items can one OrderID have

Have fun.

---- Andy

 

[quackslikeaduck]

Do I only put 1 instance of QuantityOrdered "OrderItem" or all 5? Also have QuantityReceived 1 through 5, RequestedItems 1 through5 and PricePerUnit 1 through 5. Do these all go into the same table or different tables? How to create the relationships?
Thank you for responding!!

 

[Andrzejek]

What is the difference between QuantityOrdered and RequestedItems?

You may have:

[pre]
OrderID QuantityOrdered QuantityReceived ItemNo(FK)
[/pre]
And:

[pre]
ItemNo(PK) ItemDesc UnitPrice
[/pre]

Have fun.

---- Andy

 

[quackslikeaduck]

QuantityOrdered is the number of items that were ordered like "10". RequestedItems is the actual item like "car door"

 

[quackslikeaduck]

Andy you said:

You may have:

OrderID QuantityOrdered QuantityReceived ItemNo(FK)


And:

ItemNo(PK) ItemDesc UnitPrice

Are they all separate tables and only 1 reference or each of 5 references of each? Where would my relationships be in each table? Having a hard time with this as they are all in one table now and need to be broken down.

 

[Andrzejek]

In the table with those fields:

[pre]
ItemNo(PK) ItemDesc UnitPrice
[/pre]
ItemDesc would be RequestedItem(s)

You may benefit from reading this: Fundamentals of Relational Database Design

Have fun.

---- Andy

 

[quackslikeaduck]

I setup the new tables and try to add a primary key to field "Supplier" but it tells me "The changes you requested were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I'm not sure where to make other joins but couldn't I also add "Supplier" to the other tables so they would have a join seeing there is not "ItemNumber" anywhere.

 

[Andrzejek]

Looks to me like you are doing the work backwards: you have some data in the table and then you want to add some constrains.

I would suggest to do it other way:
First set up the (empty) tables with all your constrains, relations, PK, FK, etc. and then populate it with the data.


Have fun.

---- Andy

 

[quackslikeaduck]

Thanks! I'll do that. I've never been in a situation where I had the ability to do that. Plus I haven't done anything like this in years. I'm just not sure what to use as primary keys in 2 of the 4 tables. Two of the tables have supplier in common while the other 2 don't have any fields in common can I use the supplier in those too? The reason being is there aren't any item numbers or supplier codes.

I had to overcome the NULL value error then couldn't use it as the primary key anyway.

 

[Andrzejek]

Did you read the article about Fundamentals of Relational Database Design I gave you? That should answer all your questions.

As far as PK for a table goes - use AutoNumber. That's the safest way to go.

Have fun.

---- Andy

 

[quackslikeaduck]

Yes I did read it but... I started with a fresh database. The same thing happens when I try to create PK in my table and it tells me that I can't have NULL value. If I put data in and change the PK then access deletes the data and says that I can't have NULL value.

 

[Andrzejek]

You canNOT have NULLs in your PK field.
Why would you want to have a NULL in PK field anyway? Primary Key field is for identifying the record by PK value, so it HAS to be unique and cannot be NULL.


Have fun.

---- Andy

 

[Andrzejek]

Some information: All About NULL Values

And as one of my teacher said: "Nothing equals to NULL, not even a NULL"

Have fun.

---- Andy

 

[quackslikeaduck]

Okay it wasn't NULL. As I said above I put data in the field and each time I went to save it it would delete my data and come back with the NULL value error.

I'm beyond that now though. I think I might have my joins wrong because my form shows up blank. I took the form from the old database and exported it. As soon as I change the control source on one of the fields that is in error. I save then form shows up blank. I didn't want to have to redo the whole form but looks like I might have to. Not exactly sure where my joins should go. I tried a couple of different ways but seems to me like they are wrong.

I've done this a million times before and this one is kicking my butt.