Results 1 to 7 of 7
  1. #1
    dabuse is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    3

    Question Converting Columnar Data to Horizontal Data in Access for Excel Import (Access Beginner)

    Hi,



    Mods, please let me know if this post should be migrated to another topic forum if I have mistakenly placed this in the wrong place.

    So I have columnar data, the perfect data for a database. However, I'm not working with a database, and in order to import into this data into a backend CMS for an event's mobile application, I need to convert this data in a useable horizontal/row-based format in excel. Some of this can be done using some quick formulas in excel, which have proved extremely time saving. However, some of the data doesn't quite format well. The reason is that the data is based on the least common denominator of Presentation Name, rather than sessions.

    I have attached an example to this thread of what I mean by columnar data. However, the basic idea is that speakers are arranged vertically by presentation Name, with a Speaker. This means that silos of sessions are created. I can filter for multiple sessions in my horizontal format rather quickly, but I would like to take the Presentation Name, Presentation Start Time, Presentation End Time, and Speaker Display Name and place it into a single excel field using an Access report and export. Is this a possibility? Are there any better ways to do this beside manual entry(I have thousands of rows)?

    Please let me know your thoughts, and thank you in advance!

    Sincerely,

    dabuse
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not sure what you mean by a 'single excel field'.

    Do you want those fields to be combined into one long string of data?

    Can concatenate the fields into one value with expression in query.

    Then reference that constructed field in textbox ControlSource.

    If you want those data to be rotated into one vertical column, that is another matter. You want a column for each speaker? How many possible speakers? Limit of 255 columns in tables and queries.
    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.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Hmmm... didn't look at your file but,

    I would probably try to copy and paste special in a worksheet via Automation from Access. Otherwise, with DAO, you could use the Fields() index to move across the columns.

    That's the only thing I can imagine right now.

  4. #4
    dabuse is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    3

    I'm looking for a column for each speaker

    Quote Originally Posted by June7 View Post
    Not sure what you mean by a 'single excel field'.

    Do you want those fields to be combined into one long string of data?

    Can concatenate the fields into one value with expression in query.

    Then reference that constructed field in textbox ControlSource.

    If you want those data to be rotated into one vertical column, that is another matter. You want a column for each speaker? How many possible speakers? Limit of 255 columns in tables and queries.
    I am interested in a column for each speaker, but I need to take this information and add it to a single cell with html tags.

    Here is what I'm interested in doing:
    Session Name|Name String |First Name|Last Name|Presentation Name|Presentation Start and End Time|
    Session 1 |John Doe, MD| John | Doe | Presentation 1 | Presentation 1 Time |
    Session 2 |Jane Doe, MD| Jane | Doe | Presentation 2 | Presentation 2 Time |

    Then take that information and reformat it to a different excel spreadsheet (potentially using Access):

    Session Name |Session Description | Speaker 1 Full Name | Speaker 1 First Name | Speaker 1 Last Name | Speaker 2 Full Name | Speaker 2 First Name | Speaker 2 Last Name |
    Session 1 |<b>Presentation1</b><br>John Doe, MD(etc)| John Doe, MD | John | Doe | Jane Doe, MD | Jane | Doe |

    I have a unique identifier for much of the data where I can strip the vast majority of session data. However, reformatting speakers is where I run into difficulty.

    Bear in mind, I am an Access newbie/lowbie, so please use Access-Moron speak.

    Thank you for your assistance!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Should that example data be two records for Session 1?

    Will every session always have 2 and only 2 speakers?

    A simple CROSSTAB:

    TRANSFORM First(FullName) AS FirstOfFullName
    SELECT Session
    FROM TableName
    GROUP BY Session
    PIVOT Presentation;

    Review this primer on CROSSTAB http://allenbrowne.com/ser-67.html
    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.

  6. #6
    dabuse is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    3
    Should that example data be two records for Session 1?

    Will every session always have 2 and only 2 speakers?
    The data shows two records sorted by presentation type. The first showed how the data looks, and I was attempting to show how I wanted it to look.

    If every session always had 2 and only 2 speakers, I would transpose like a maniac. Sadly, the number of presentations and speakers is variable.

    Thank you for the steps! I will take a look to see if they will produce my desired result.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The sample data shows records for Session 1 and Session 2 but the transposed output shows the data all for Session 1. How can that be?

    I doubt CROSSTAB is going to be viable. VBA code options:

    1. writing records to a temp table. Review http://forums.aspfree.com/microsoft-...ry-322123.html

    2. opening an Excel object and writing data to cells from recordset one field and record and a time
    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: 3
    Last Post: 11-24-2013, 01:39 PM
  2. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  3. Query used to import data from access > excel
    By Perceptus in forum Queries
    Replies: 1
    Last Post: 07-25-2013, 10:45 AM
  4. Trying to import data into access from excel
    By Cupps256 in forum Access
    Replies: 10
    Last Post: 03-09-2013, 02:31 AM
  5. Import Data from Excel into Access
    By sauce1979 in forum Import/Export Data
    Replies: 2
    Last Post: 10-14-2011, 12:05 AM

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