INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

need help normalizing data

need help normalizing data

(OP)
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.

RE: need help normalizing data

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.

RE: need help normalizing data

Have a table QuantityOrdered:

OrderID Quantity  ItemNo(FK)
4       23        5
4       123       47
4       5         4
4       1         1
5       12        5
5       55        47
 

Another table will keep the Items:

ItemNo(PK)   ItemDesc
5            Toyota
47           Fiat
4            BMW
1            Ford
 

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

No limits how many Items can one OrderID have smile

Have fun.

---- Andy

RE: need help normalizing data

(OP)
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!!

RE: need help normalizing data

What is the difference between QuantityOrdered and RequestedItems?

You may have:

OrderID QuantityOrdered   QuantityReceived  ItemNo(FK)
 
And:

ItemNo(PK)   ItemDesc   UnitPrice
 

Have fun.

---- Andy

RE: need help normalizing data

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

RE: need help normalizing data

(OP)
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.

RE: need help normalizing data

In the table with those fields:

ItemNo(PK) ItemDesc UnitPrice
 
ItemDesc would be RequestedItem(s)

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

Have fun.

---- Andy

RE: need help normalizing data

(OP)
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.

RE: need help normalizing data

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

RE: need help normalizing data

(OP)
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.

RE: need help normalizing data

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

RE: need help normalizing data

(OP)
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.

RE: need help normalizing data

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

RE: need help normalizing data

Some information: All About NULL Values

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

Have fun.

---- Andy

RE: need help normalizing data

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close