Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Normalize a table

    I have a table like this :

    Name Salary1 Salary2 Salary3 Expense1 Expense2 Expense3

    John 100 200 300 50 100 150


    I want to normalize this table to become :

    Name Type 1 2 3
    John Salary 100 200 300
    John Expense 50 100 150



    Does anyone know how to do this ?



    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Tidewater, VA
    Posts
    8
    I would create a query that would place the fields together in the order you want them. You could sort by whatever field you wanted as well.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why stop there? it's not normal yet

    you should take it to this --

    Name Type No Amt
    John Salary 1 100
    John Salary 2 200
    John Salary 3 300
    John Expense 1 50
    John Expense 2 100
    John Expense 3 150


    once you set the new table up, populating it from the old table is easy

    you will need 6 queries that all look like this:

    insert into newtable
    ( Name, Type, No, Amt )
    select
    Name, Expense, 2, Expense2
    from oldtable


    rudy

  4. #4
    Join Date
    Apr 2002
    Posts
    168
    Thanks Rudy, that is really helpful. If I just want to take it to my level, is this the correct code :


    Insert into new_table
    ( Name, Type, 1, 2, 3)
    select
    Name, Salary, Salary1, Salary2, Salary3
    from old_table

    Insert into new_table
    ( Name, Type, 1, 2, 3)
    select
    Name, Expense, Expense1, Expense2, Expense3
    from old_table

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, yes, with two conditions --

    1. you should not use a number as a column name

    2. you're really really sure you want three denormalized columns, because if you ever have to add a fourth, you will be S.O.L.... also, if you have any other tables that have to join to these ones, your queries will be foo=column1 OR foo=column2 OR foo=column3 which will have terrible performance

    rudy

  6. #6
    Join Date
    Apr 2002
    Posts
    168
    Rudy,


    That's true, I should use Month1-3 instead.

    What does SOL mean ? Isn't adding forth just need to change the query very little, in this case, the same as if I get into your level ?

    In terms of joining, I can just join with Name and Type, right ?

    Thanks



    2. you're really really sure you want three denormalized columns, because if you ever have to add a fourth, you will be S.O.L.... also, if you have any other tables that have to join to these ones, your queries will be foo=column1 OR foo=column2 OR foo=column3 which will have terrible performance

    rudy

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see SOL

    yeah, you're right, you would not join to amount fields


    rudy

  8. #8
    Join Date
    Apr 2002
    Posts
    168
    Thanks again Rudy.


    Actually I have another table to normalize, and this is more complicated I would say.

    So in this case, instead of salary or income, it's product. For example :


    Name Prod1Month1 Prod1Month2 ... Prod2Month1 Prod2Month2 ...



    The number of month is known, however, the number of product may varies. Sometimes there are 24 products. Is there any way to automate this ?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure what it is you wnt to automate, certainly if you change the table layout you will have to change the programs that insert and use the data...

    create table products
    ( name varchar(50) not null
    , prodname varchar(50) not null
    , prodyear smallint not null
    , prodmonth smallint not null
    )

    i'm guessing


    rudy

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    it is normal it's in second normal form which is perfectly okay, admittedly access work better in third normal form but hay it's his DB

    Originally posted by r937
    why stop there? it's not normal yet

    you should take it to this --

    Name Type No Amt
    John Salary 1 100
    John Salary 2 200
    John Salary 3 300
    John Expense 1 50
    John Expense 2 100
    John Expense 3 150


    once you set the new table up, populating it from the old table is easy

    you will need 6 queries that all look like this:

    insert into newtable
    ( Name, Type, No, Amt )
    select
    Name, Expense, 2, Expense2
    from oldtable


    rudy
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by milan
    Thanks again Rudy.


    Actually I have another table to normalize, and this is more complicated I would say.

    So in this case, instead of salary or income, it's product. For example :


    Name Prod1Month1 Prod1Month2 ... Prod2Month1 Prod2Month2 ...



    The number of month is known, however, the number of product may varies. Sometimes there are 24 products. Is there any way to automate this ?
    as for this table, it really looks like it should be 3rd normal ie
    name, month, product, quantity

    i had a fairly large table that i needed to normalizes and i used the following method, 50 columns and thousands of rows

    Sub makescraptable()
    Dim rs As DAO.Recordset, i As Integer
    Set rs = CurrentDb.OpenRecordset("qryScrap")
    rs.MoveFirst
    DoCmd.SetWarnings False
    While Not rs.EOF
    For i = 2 To 50
    If rs(i).Value > 0 Then DoCmd.RunSQL "INSERT INTO Scrap ( MachineID, PostingDate, ScrapCode, Quantity )SELECT '" & rs(0).Value & "' , " & CDbl(rs(1).Value) & " , " & Val(Right(rs(i).Name, 2)) & " , " & rs(i).Value & ";"
    Next
    rs.MoveNext
    Wend
    DoCmd.SetWarnings True
    End Sub

    which use dthe following queries
    Scrapdate:
    SELECT IIf(Max(scrap.PostingDate) Is Not Null,Max(scrap.PostingDate),0) AS Expr1
    FROM Scrap;

    qryscrap:
    SELECT MachineOutput.MachineID, MachineOutput.PostingDate, Sum(MachineOutput.C_COUNT01) AS SumOfC_COUNT01, Sum(MachineOutput.C_COUNT02) AS SumOfC_COUNT02, Sum(MachineOutput.C_COUNT03) AS SumOfC_COUNT03, Sum(MachineOutput.C_COUNT04) AS SumOfC_COUNT04, Sum(MachineOutput.C_COUNT05) AS SumOfC_COUNT05, Sum(MachineOutput.C_COUNT06) AS SumOfC_COUNT06, Sum(MachineOutput.C_COUNT07) AS SumOfC_COUNT07, Sum(MachineOutput.C_COUNT08) AS SumOfC_COUNT08, Sum(MachineOutput.C_COUNT09) AS SumOfC_COUNT09, Sum(MachineOutput.C_COUNT10) AS SumOfC_COUNT10, Sum(MachineOutput.C_COUNT11) AS SumOfC_COUNT11, Sum(MachineOutput.C_COUNT12) AS SumOfC_COUNT12, Sum(MachineOutput.C_COUNT14) AS SumOfC_COUNT14, Sum(MachineOutput.C_COUNT15) AS SumOfC_COUNT15, Sum(MachineOutput.C_COUNT16) AS SumOfC_COUNT16, Sum(MachineOutput.C_COUNT17) AS SumOfC_COUNT17, Sum(MachineOutput.C_COUNT18) AS SumOfC_COUNT18, Sum(MachineOutput.C_COUNT19) AS SumOfC_COUNT19, Sum(MachineOutput.C_COUNT20) AS SumOfC_COUNT20, Sum(MachineOutput.C_COUNT21) AS SumOfC_COUNT21, Sum(MachineOutput.C_COUNT22) AS SumOfC_COUNT22, Sum(MachineOutput.C_COUNT23) AS SumOfC_COUNT23, Sum(MachineOutput.C_COUNT24) AS SumOfC_COUNT24, Sum(MachineOutput.C_COUNT25) AS SumOfC_COUNT25, Sum(MachineOutput.C_COUNT26) AS SumOfC_COUNT26, Sum(MachineOutput.C_COUNT27) AS SumOfC_COUNT27, Sum(MachineOutput.C_COUNT28) AS SumOfC_COUNT28, Sum(MachineOutput.C_COUNT29) AS SumOfC_COUNT29, Sum(MachineOutput.C_COUNT30) AS SumOfC_COUNT30, Sum(MachineOutput.C_COUNT31) AS SumOfC_COUNT31, Sum(MachineOutput.C_COUNT32) AS SumOfC_COUNT32, Sum(MachineOutput.C_COUNT33) AS SumOfC_COUNT33, Sum(MachineOutput.C_COUNT34) AS SumOfC_COUNT34, Sum(MachineOutput.C_COUNT35) AS SumOfC_COUNT35, Sum(MachineOutput.C_COUNT36) AS SumOfC_COUNT36, Sum(MachineOutput.C_COUNT37) AS SumOfC_COUNT37, Sum(MachineOutput.C_COUNT38) AS SumOfC_COUNT38, Sum(MachineOutput.C_COUNT39) AS SumOfC_COUNT39, Sum(MachineOutput.C_COUNT40) AS SumOfC_COUNT40, Sum(MachineOutput.C_COUNT41) AS SumOfC_COUNT41, Sum(MachineOutput.C_COUNT42) AS SumOfC_COUNT42, Sum(MachineOutput.C_COUNT43) AS SumOfC_COUNT43, Sum(MachineOutput.C_COUNT44) AS SumOfC_COUNT44, Sum(MachineOutput.C_COUNT45) AS SumOfC_COUNT45, Sum(MachineOutput.C_COUNT46) AS SumOfC_COUNT46, Sum(MachineOutput.C_COUNT47) AS SumOfC_COUNT47, Sum(MachineOutput.C_COUNT48) AS SumOfC_COUNT48, Sum(MachineOutput.C_COUNT49) AS SumOfC_COUNT49, Sum(MachineOutput.C_COUNT50) AS SumOfC_COUNT50
    FROM MachineOutput, Scrapdate
    WHERE (((MachineOutput.PostingDate)>[Expr1]-1))
    GROUP BY MachineOutput.MachineID, MachineOutput.PostingDate;
    Last edited by m.timoney; 03-06-03 at 13:00.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Apr 2002
    Posts
    168
    Rudy,

    Im my example, let's say there are 2 products and 3 months. My code will be :


    Insert into new_table
    (Name, Type, Month1, Month2, Month3)
    select
    Name, Prod1, Prod1Month1, Prod1Month2, Prod1Month3
    from old_table

    Insert into new_table
    (Name, Type, Month1, Month2, Month3)
    select
    Name, Prod2, Prod2Month1, Prod2Month2, Prod2Month3
    from old_table

    However, sometimes the number of products is 20, so I am looking for a way not to repeat this code 20 times, maybe there is a clever way to do this.



    Timoney, I agree with you, it's not fully normalized. It's just easier for me to do query based on second normal.


    Thanks.

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    there is as long as there's a recognisable pattern to the field names,
    can you give 3 examples of the field headings

    Originally posted by milan
    Rudy,

    Im my example, let's say there are 2 products and 3 months. My code will be :


    Insert into new_table
    (Name, Type, Month1, Month2, Month3)
    select
    Name, Prod1, Prod1Month1, Prod1Month2, Prod1Month3
    from old_table

    Insert into new_table
    (Name, Type, Month1, Month2, Month3)
    select
    Name, Prod2, Prod2Month1, Prod2Month2, Prod2Month3
    from old_table

    However, sometimes the number of products is 20, so I am looking for a way not to repeat this code 20 times, maybe there is a clever way to do this.



    Timoney, I agree with you, it's not fully normalized. It's just easier for me to do query based on second normal.


    Thanks.
    Last edited by m.timoney; 03-06-03 at 13:11.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "However, sometimes the number of products is 20, so I am looking for a way not to repeat this code 20 times"

    what do you mean by "repeat"?

    it's part of the key, it has to be repeated


    rudy

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by r937
    "However, sometimes the number of products is 20, so I am looking for a way not to repeat this code 20 times"

    what do you mean by "repeat"?

    it's part of the key, it has to be repeated


    rudy
    i think he mean that he doesn't want to run the queries for the products that don't appear while being sure that they'll run when they're present
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •