Results 1 to 4 of 4
  1. #1
    Deutz is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    51

    How do I combine multiple fields into one field?

    Using Access 2003
    I have a table with some fields that don’t need to be touched and some that need to be reorganised into a single field and don’t know how to do this with SQL. I spent a good deal of time mucking about with crosstab queries but couldn't see how to make it work.
    Existing Table structure:
    All fields: Name, Address, Suburb, TypeA, TypeB, TypeC, TypeD
    Fields to reorganise: TypeA, TypeB, TypeC and TypeD (CURRENCY)


    I would like to restructure the table as follows:
    All Fields: Name, Address, Suburb, Type, CountOfType
    I want the Type field (TEXT) to contain one of “TypeA”, “ TypeB”, “TypeC” or “TypeD” and the CountOfType to contain the corresponding number for that type.
    Thanks in advance

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Actually, many people are looking forward to a de-crosstab query from microsoft. ):

    before the de-crosstab query comes out, you can use several queries to approach it:

    insert into tblNew (Name, Address, Suburb, Type, CountOfType ) select Name, Address, Suburb, "TypeA" , TypeA from tblOld
    insert into tblNew (Name, Address, Suburb, Type, CountOfType ) select Name, Address, Suburb, "TypeB" , TypeB from tblOld
    insert into tblNew (Name, Address, Suburb, Type, CountOfType ) select Name, Address, Suburb, "TypeC" , TypeC from tblOld
    insert into tblNew (Name, Address, Suburb, Type, CountOfType ) select Name, Address, Suburb, "TypeD" , TypeD from tblOld


  3. #3
    Deutz is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    51
    Thanks weekend00,

    That solves my problem.

    I had thought to do something along similar lines but was trying to be too clever with crosstab queries etc.

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    somebody call this kind of thing de-crosstab.
    crosstab does thing on the countrary way, puting text in records as column head.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combine two fields, Null fields involved
    By yes sir in forum Access
    Replies: 9
    Last Post: 10-03-2010, 09:20 AM
  2. Combine one item fields in one field
    By romadm in forum Reports
    Replies: 7
    Last Post: 06-04-2010, 11:09 PM
  3. Replies: 1
    Last Post: 02-03-2010, 09:17 PM
  4. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  5. Replies: 3
    Last Post: 08-02-2009, 03:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums