Results 1 to 4 of 4
  1. #1
    kid is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2020
    Posts
    20

    access - append columns to row

    My Table columns are likely
    Name,Age,Category,module1,module2,module3...

    Now I want a new table with



    Name, Age, Category,Module
    Name,age,category,module1
    Name,age,category,module2
    Name,age,category,module3
    ....
    (under module column append modules one by one in separated rows with that name,age,category)

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should focus on some concepts that underlie relational database.
    There are several articles in the Database Planning and Design link in my signature.
    See the rdbmsprinciples.pdf in that link, and/or the Introduction to Database by guru99 and research Normalization.

    If you spend 45-60 minutes working through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in that link you will experience the database design process that can be used with any DBMS. It will take you from a business description to a blueprint for the database(tables and relationships) needed to support that business and deal with normalization.
    Good luck.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to add to orange's comments, age is not a good value to use - it will be wrong next year. better to use a date or year of birth, manufacture, whatever

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A UNION query can rearrange data.

    But I agree with others. If you are developing db, then normalize.

    If this is provided data for which structure you have no control over and must deal with, use UNION. There is no wizard/designer for UNION, must type or copy/paste in SQLView of query builder.

    SELECT [Name], age, category, module1 AS Data, "M1" AS Src FROM tablename
    UNION SELECT [Name], age, category, module2, "M2" FROM tablename
    UNION SELECT [Name], age, category, module3, "M3" FROM tablename;

    Name is a reserved word and should not use reserved words as names for anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 11
    Last Post: 01-08-2020, 03:26 PM
  2. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 PM

Tags for this Thread

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