Results 1 to 7 of 7

Transposing columnar data to row data; Export to excel for SPSS

  1. #1
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57

    Transposing columnar data to row data; Export to excel for SPSS

    Hey all!

    I've created a rather complex database with numerous tables to capture some basic medical information like blood pressure, medications taken, height and weight, etc. each with it's own table (see example below). I have mostly one primary key (ID#) that links the data to a main table that I will want to export. The reason for creating several tables is that many of the patients could have numerous entries for the same type of data. For example, I want to capture Mr. Smith's blood pressure (BP) values each time he came in to the clinic so I created a BP table with the fields Name (actually used ID# in the real database); BP_Date; Sys_BP; and Dia_BP. In my limited database development experience it sounded like the rational thing to do so that each patient could have an essentially limitless amount of dates/entries and Access would keep it nice and tidy for me.

    Now here's the problem: All data needs to be exported into excel and will be imported into SPSS statistical software. According to my research lead, the software requires that each patient's info be on one row only. According to sources from my research group when they usually design databases for export to SPSS they "guess" how many entries will be needed for each event and create a field for each one. So if they estimated a max of 5 blood pressure screeenings they would create the fields BP_Date_1; Sys_BP_1; Dia_BP_1; BP_Date_2; Sys_BP_2; Dia_BP_2; and so on until they got to 5. The same would be true for medications, height and weight, etc. They all need to be in the same row as the patient name so that each row represents one patient. This seems kind of ridiculous to me from a database design standpoint (again I'm fairly new) resulting in many null fields while self-limiting the number of entries you can make.

    At this point, the database is already created (which took me hundreds of hours) and data collection has already begun, so starting over from scratch is not an option. I'm thinking there has to be a way in Access to transpose the data into single rows to export as desired. Initial thought was some sort of query that counts the entries for each table per patient and makes a new table with new fields based on the counts and transposes them. I just don't have enough knowledge or coding experience to know how to make that happen.

    I'd love to hear your thoughts and suggestions. I've also attached below a simplified example spreadsheet of how the data is currently stored in Access and how I'd like to export it.

    Thanks!!



    Currently stored like this (except using an ID# instead of Name):
    Click image for larger version. 

Name:	Screen Shot 2019-01-24 at 3.58.20 PM.png 
Views:	19 
Size:	30.0 KB 
ID:	37112

    Needs to be exported like this:
    Click image for larger version. 

Name:	Screen Shot 2019-01-24 at 3.59.07 PM.png 
Views:	19 
Size:	37.5 KB 
ID:	37113

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,260
    Did you try a Crosstab query?

  3. #3
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57
    I have not tried a Crosstab query. I don't have access to the database right now, but I'll try tomorrow.

    Thanks!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,082
    Review http://allenbrowne.com/ser-67.html#MultipleValues

    Unfortunately, that data will not work as is with CROSSTAB. Need a sequence ID for each patient group. Consider:

    TRANSFORM First(Data.BPDate) AS FirstOfBPDate
    SELECT Data.PtID
    FROM Data
    GROUP BY Data.PtID
    PIVOT "Date" & DCount("*","Data","PtID=" & [PtID] & " AND ID<=" & [ID]);

    TRANSFORM First(Data.SysBP) AS FirstOfSysBP
    SELECT Data.PtID
    FROM Data
    GROUP BY Data.PtID
    PIVOT "SYS" & DCount("*","Data","PtID=" & [PtID] & " AND ID<=" & [ID]);

    TRANSFORM First(Data.DiaBP) AS FirstOfDiaBP
    SELECT Data.PtID
    FROM Data
    GROUP BY Data.PtID
    PIVOT "DIA" & DCount("*","Data","PtID=" & [PtID] & " AND ID<=" & [ID]);

    Now build another query that joins the last 2 to the first.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57
    Hi June! I reviewed the article and understand generally what needs to happen. The details I'm a little lost in though. I'll try to run through some of it tomorrow or when I get a chance and let you know where I get stuck. The parts that I'm a little unclear on now are how to set up the sequence ID per patient group and not sure what you mean by joining the last 2 to the first.

    Thanks for the help!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,082
    Build the 3 CROSSTAB queries as I show. Build a 4th query that joins last 2 to the 1st, linking on the PtID fields. Or if you want patient name, join all 3 CROSSTAB queries to Patients table.

    The DCount() expression provides the group sequence ID.
    Last edited by June7; 01-25-2019 at 03:43 AM.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,984
    I am not that strong with crosstab queries (I am still looking at June's example).
    I tend to use VBA and "brute force" it.

    So, here is a VBA solution.

    You need to set a reference to "Microsoft Excel XX.0 Object Library" where XX is the version number. A2010 is version 14 - not sure what A2007 version number is -12???

    There is a lot of enhancements that could be added, such as provide a file name and create the new Excel workbook on the fly, allow a different folder to be selected to save the file or open an Excel file in a different folder.
    In the current code, an Excel workbook named "Test1.xlsx" must to be in the same folder as the Access dB.

    And I just noticed this:
    Currently stored like this (except using an ID# instead of Name):
    The table I created has a name in the Name field, not an ID_num - the code will need to be modified if your structure is different.
    Post here or PM me if you have questions....
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 23
    Last Post: 11-27-2018, 12:00 PM
  2. Transposing Linked Data
    By Rustin788 in forum Access
    Replies: 4
    Last Post: 03-09-2017, 02:46 PM
  3. Replies: 6
    Last Post: 07-31-2014, 11:53 AM
  4. transposing data in a report
    By brianrunsphilly in forum Reports
    Replies: 3
    Last Post: 10-23-2013, 01:22 PM
  5. Transposing Data
    By NoiCe in forum Queries
    Replies: 0
    Last Post: 11-09-2008, 04:24 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
  •  
Tech Forums: Microsoft Office Forums