Normalizing table???

[avenuw]

I am trying to update several columns in a table with rows from the same table. You see, the data as I receive it is not normalized. Basically I have a table with the following columns

name
date_created
purchase_num
item_num
...

So I might have many rows with the same name, date_created_purchase_num but with a different item_num.

What I am trying to do is do a search and find all the item_num for each group (of name, date_created_purchase_num) and update/insert into new table

name
date_created_
purchase_num
item_num_1
item_num_2
item_num_3
item_num_4

How can I do that?

thanks,
GG

 

[Remou]

A Union query would be best, I think:

SELECT name, date_created, purchase_num, item_num_1 AS Item
FROM tblTable
UNION ALL
SELECT name, date_created, purchase_num, item_num_2 AS Item
FROM tblTable

<...>

 

[avenuw]

I might not be following you correctly but my original table contains the following

name
date_created
purchase_num
item_num

So I have many rows with the same name, date_created,purchase_num but with a different item_num. But What I want is to move it into a new table with the following

name
date_created_
purchase_num
item_num_1
item_num_2
item_num_3
item_num_4

So instead of having many rows just with a different item_num, I want to move each unique item num into one of the fields item_num_1, item_num_2, item_num_3 and so forth.

The union query you suggested would do the opposite.

Thanks,
GG

 

[fneily]

If these are the field names or a record:
name
date_created_
purchase_num
item_num_1
item_num_2
item_num_3
item_num_4
then this is not normalized. Violates the first normal form(protocol, rule). Duplicate column headings - drop the number and you have item_num, iten_num, etc. What happens when they're aren't 4 items? You'd have blanks. What happens if in the future there'll be 5 items? Add another column? Wrong. Result is variable length records.

"
name
date_created
purchase_num
item_num
...
So I might have many rows with the same name, date_created_purchase_num but with a different item_num."
That table is normalized if that's all your data. If you have client info, product info, then you'd have other tables and create a junction table for orders.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[avenuw]

Well, Thanks fneily for setting me straight. I have been thinking too hard and getting lost among all my queries at this point-- Time for a break!!!

Thanks For the responses, appreciate it
GG