Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Accessv2003-Need to combine multiple contact records into a single record

    Apologies if this has been answered adequately somewhere already. I didn't find it.

    Scenario:
    I work in a K-12 school district and handle the data management (am not a DBA). Each student usually has multiple "parent/guardian" contacts. When I export these out of the existing database (SASI - db4), I get a separate record for each of the contacts for a student. I can extract it to either comma or tab delimited and I can pull it into Access with no problem... but...

    I need to get these multiple records combined into a single record for each student with the following information:
    --Student ID
    --Student name, etc
    --Parent/Guardian1 Name
    --PG1 Relationship (to student)
    --PG1 Main Phone
    --PG1 Secondary Phone
    --PG2 Name
    --PG2 Relationship (to student)
    --PG2 Main Phone
    --PG2 Secondary Phone

    I have to get this accomplished for various reasons. I need to be able to pull it into a simple excel spreadsheet for others to use as well (comma/tab delimited - I don't care... either will work)

    There are multiple reasons I need to accomplish this. We are also switching student management systems, going from SASI to PowerSchool. The new system is structured completely different, and has a single student record for each student with the multiple contact structure described above.

    I have seen this question asked numerous times and have seen several replies on why it shouldn't be done, but none that explain how to do it for those of us that need to do it. I don't need more of that. I simply need an answer to how it CAN be done... Thanks much in advance for any help you can provide.

    Regards.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would argue against storing data that way, but it's not uncommon to need it exported out as you've described. If it can be concatenated into a single field, this could be adapted to handle the multiple fields:

    Modules: Return a concatenated list of sub-record values

    If you want it in separate columns in Excel, you'll probably need use automation and build it in code.
    Paul

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    Thanks

    Thanks Paul.

    I'll work on it. It's a bit past my level, but I'll see if I can figure it out. It will help greatly if I can get it to work.

    jb

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, and welcome to the site by the way! Post back if you get stuck.
    Paul

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jbeaupain, Do you have an example of the data while it is in the multiple row per student format?

    What I am trying to find out is if there is something that identifies the different Guardian types (G1, G2, etc.).

    Also, do you have a finite number of potential Guardians?

    Depending on your answers, I might have a single SQL statement that will get your one row format (assuming it put the multi-row format into an Access table first).

    Is it something like this:

    Student ID, etc, PG Name, etc, Guardian Type/number 1
    Student ID, etc, PG Name, etc, Guardian Type/number 2

    With the Guardian Type/number being the identifier for the Guardians.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •