Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move data from one field into another (2003)

    Is there a way to make a query that returns data appended into a new field? I have data rows with item1, item2, item3 etc as separate fields. All of these fields contain the same kind of data, just in different positions. I want the query results (not a new table) to give me a count of all those items in one single field. I start with this
    ID item1 item2 item3 item4 item5
    1... 475 .... 01004
    2... 475 .... 01004
    3... 18 .... 00026 .... 00565 .... 00565

    and want to end up with this
    Item... count
    475 ... 2
    18 ... 1
    1004 ... 2
    26 ... 1
    565 ... 2

    Is there a reasonable way to do this?
    Thanks so much.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move data from one field into another (2003)

    It would be much more convenient to change to a normalized table structure:

    <table border=1><td align=right>ID</td><td align=right>Item</td><td align=right>Value</td><td align=right>1</td><td align=right>1</td><td align=right>475</td><td align=right>1</td><td align=right>2</td><td align=right>01004</td><td align=right>2</td><td align=right>1</td><td align=right>475</td><td align=right>2</td><td align=right>2</td><td align=right>01004</td><td align=right>3</td><td align=right>1</td><td align=right>18</td><td align=right>3</td><td align=right>2</td><td align=right>00026</td><td align=right>3</td><td align=right>3</td><td align=right>00565</td><td align=right>3</td><td align=right>4</td><td align=right>00565</td></table>
    You can use VBA to do this; if you search for normalize in this forum you'll find examples, and you can download a free database with sample code from Roger's Access Library. Using the normalized structure, the query you need is a straightforward totals query.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move data from one field into another (2003)

    Thank you. I'll play with that. I'd have to rename every table (up to 10/day) and then modify the field names in the Module for every query. At that point it might be simpler to aggregate each field into Excel and then pivot it. I'm trying to avoid having to make new tables since the tables may have a million or more rows each. But this at least gives me some ideas. I do appreciate it.
    Judy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move data from one field into another (2003)

    > I'd have to rename every table (up to 10/day)

    Where do these tables come from? If you're importing them from another database system, chances are the data are stored in a normalized format there, and denormalized for export, so it might be worthwhile investigating whether you can get the original data.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move data from one field into another (2003)

    Yes, I'm pulling data from a warehouse. I could pull one row for each item, but I have reasons for not wanting to do it that way. I want to pull all items (max 5) in one row.
    I'll keep playing with it- something is bound to occur. Thanks for your help.
    Judy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move data from one field into another (2003)

    It's your decision, of course, but you are making things much more difficult for yourself by flaunting the rules for relational databases.

Posting Permissions

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