Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Can I create field names from record contents

    Forgive my (lack of) techy jargon here - only just started using Access!

    I have a table that contains over 9000 records - i think its an appalling design and therefore would like to restructure it.

    Basically what i'm wondering is whether its possible to create a new table, using NEW fieldnames created from the old records

    For example

    In the current database I have a field called IVALUE
    In this there are labels such as OS, RAM SIZE, HARD DISK etc

    What i would like to do is use these labels, to create a new table, so that OS, RAM SIZE etc are the field names to my new records

    Does that make sense? Am i approaching this the right way (obviously dont want to input 9000 records from scratch!!)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can probably use a crosstab query with the labels defined as the column headers in order to create a new table.

    But why do you think the current design is bad? The previous developer may have had a good reason for designing the table that way. It makes it very easy to add new attributes to the database without having to change the schema. For instance, to add an attribute called "color" you just add it to the current records with the label "color". With the attributes as individual fields, you will need to add a new field and possibly modify a lot of existing code to handle it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    may be this wil help

    first make a copy of your original .mdb
    you know :

    select it in tableview

    control c
    control v
    rename it


    add in the copy the wanted new fields in design mode

    make an update query for this fields ONE at the time

    the query should do this statement

    update new field with the value of in IVALUE from the original table
    where Ivalue = "OS"

    in the next query change it to "where Ivalue = "ramSize"

    until your done

    if the result is that what you want, delete the field Ivalue,
    make copy
    and rename it to the original table

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't you think a single crosstab query as a make-table statement would be much less effort?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    I've never used cross tab queries before - but i'm currently playing and it looks like it might do the trick!

    Cheers for the advice!

  6. #6
    Join Date
    Mar 2004
    Posts
    3
    Well i thought the crosstab might work but have now hit a wall.

    In CTQs do you always have to have a calculation type field, because all i have is text data which i need to display differently from the original database

    I have attached a screengrab of my data - basically want IPAGE down left hand side, the data in IFIELDS along the top (column header) and then the IVALUES in the corresponding fields.

    Am i asking too much?
    Attached Thumbnails Attached Thumbnails grab.gif  

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your data does not support what you are trying to do. For instance, IPage "Summary = 1H-P3500" has three "Communication Port" labels. How are you going to fit these into a single field? I really think the person who designed this schema used this table format for a reason.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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