Results 1 to 7 of 7
  1. #1
    CongoGrey is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2016
    Posts
    4

    many to one data must export as a single row

    Greetings Access Experts,
    I hope one of you has a fresh idea that can help me.

    I am exporting multiple access tables to Excel. A few tables are in a 'many' relationship with my mainTable.
    I need this data to export as a single row with the mainTable data as follows (all fields mainTable, all fields in first record manyTable, all fields in second record manyTable, etc.)
    I developed a vba solution that is complicated (count, insert, create tempTable, append, load recordset, export) but works just fine.



    I am curious if there is a simpler way to accomplish and maintain this function.
    Ideally, I would like to perform this work using standard Access queries that the user can more easily manipulate if they choose to add or delete fields.
    I thank you in advance --

    CongoGrey

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    normally, there'd be no reason to do this.
    you dont take a 7 course dinner , shove it in a blender, and put it on a single sandwich.

  3. #3
    CongoGrey is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2016
    Posts
    4
    So I am thinking about this problem incorrectly?
    This medical data so mainTable is the patient demographics and manyTable would contain rows for vist1, visit2, etc.
    I should export this as (row 1, patient1, visit1) , (row2, patient1, visit2)?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is the structure of your tables? There is one main table for patient demographics, then many tables containing their visits? Could you post your database with a few lines of data?

  5. #5
    CongoGrey is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2016
    Posts
    4
    Quote Originally Posted by aytee111 View Post
    What is the structure of your tables? There is one main table for patient demographics, then many tables containing their visits? Could you post your database with a few lines of data?
    A Good Morning to All,

    Attached is a very, very simple demo db with two queries - one returning multiple rows per patient and the other running code to develop a single row per patient.
    The actual db base is much more complicated. It has multiple tables with up to 100 fields per table. There are also lookups and checkbox fields.

    Because I coded this, it would be very difficult for my users to add or delete a field and still run the export queries.
    I did try several suggestions using crosstabs but had a lot of problems due to the volume of the fields and eventually gave that path up.

    I hope you can give me some ideas--one to many relationships between tables are fairly common yet I can't think of a way to do this which is more user friendly.

    CongoGrey
    Attached Files Attached Files

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Access is very versatile and everyone would have done this in a different way. But your way works, very well done, no need to make any changes. The only thing I would suggest is to look to the future and set a limit on the number of visits reported on, otherwise you will get to the point where you will exceed the number of Excel columns allowed (for the hypochondriacs!).

  7. #7
    CongoGrey is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2016
    Posts
    4
    Thank you aytee111!
    I guess I just needed someone to say "yup".

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

Similar Threads

  1. Coding to export data from single record to Word Bookmarks
    By Fatmonkeymedia in forum Programming
    Replies: 5
    Last Post: 12-01-2016, 10:39 PM
  2. Export Single record from a form to PDF
    By ManuelLavesa in forum Import/Export Data
    Replies: 2
    Last Post: 09-10-2015, 05:47 PM
  3. Export Report to Single PDF Pages
    By 02kev02 in forum Import/Export Data
    Replies: 2
    Last Post: 05-23-2012, 07:03 AM
  4. Export single record from a form to PDF
    By GraemeG in forum Programming
    Replies: 1
    Last Post: 04-10-2011, 01:33 PM
  5. Export single records to new Word Document
    By karmaimages in forum Import/Export Data
    Replies: 0
    Last Post: 11-19-2009, 03:37 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