Results 1 to 5 of 5
  1. #1
    pbras is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    2

    How to make a pivot table in Access?

    I have a table (sample):
    Event Day1 Day2 Day3 Day4 Day5
    EventA 20-01-03 20-01-09 20-01-17
    EventB 20-01-11
    EventC 20-01-02 20-01-15

    And want a list:


    Date Event
    20-01-02 EventC
    20-01-03 EventA
    20-01-09 EventA
    20-01-11 EventB
    20-01-15 EventC
    20-01-17 EventA

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    within access, since the first table is not normalised, you will need to us a union query, one for each day

    SELECT Day1 as eventdate, Event
    FROM myTable
    WHERE Day1 is not Null
    UNION SELECT Day2 as eventdate, Event
    FROM myTable
    WHERE Day2 is not Null
    etc

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    An alternative solution is to import your table into Power Query also know as Get and Transform in your version of Excel. Highlight the first column in the PQ editor and select Transform-->Unpivot other Columns. You now have your data in the form requested. Close and Load to an Excel Spreadsheet.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You are asking to UNpivot.
    normally you start with your 'want' example, then pivot to your 'have' example.
    To do what you want create an append query for each DAY. Append that 1 day to the target table,
    then run the next Day untill all days have benn appended.

  5. #5
    pbras is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    2
    Thanks toi all of you for help.

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

Similar Threads

  1. Change color for pivot table access
    By barby.pava in forum Forms
    Replies: 1
    Last Post: 03-15-2016, 03:42 PM
  2. Replies: 0
    Last Post: 09-22-2014, 07:07 AM
  3. Access VBA Excel Pivot Table Formatting
    By Tomlon in forum Programming
    Replies: 1
    Last Post: 02-07-2013, 02:28 PM
  4. Pivot table - access to excel
    By antagonia in forum Access
    Replies: 3
    Last Post: 12-16-2011, 01:57 PM
  5. [Help] Access Pivot Table
    By weiguo.shi in forum Queries
    Replies: 0
    Last Post: 05-18-2011, 09:49 AM

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