Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: How to query to combine multiple records into one record?

    Hi All

    I have an MS Access 2000 database recording funding applications made by researchers. It has an Applications table and an Applicants table.

    One Application ID may have many applicants attached. When creating a query by Application ID, one row is returned for each applicant attached to the application e.g.
    Application ID Applicant ID FirstName LastName
    45677 1 John Smith
    45677 2 Jan Citizen

    What I want is a query that returns the Application ID once and all the applicants attached to it in the one row. I defintely want to know how to do this.

    Could you please explain the query process to use with as mcuh detail as possible, in preference to using using any programming or SQL to obtain the result, if possible.

    Thanks
    George

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bah, I should bookmark these things.... this question comes up in its various forms many times here.

    The solution involves code. Basically you'd need to get the application ID and loop through the applicants.

    What are you going to do with the data? I ask because it is theoretically possible to have unlimited applicants per application. So how many columns will be too many?? If you have application 45677 and 7,845 columns, just what do you think you can do with that?

    What you are going to do with it will help identify the best way to help you.

    If it is just a report (print them), I would actually recommend that you just have a subreport listing the applicants UNDER each application. It's a LOT easier!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Paul

  4. #4
    Join Date
    Oct 2009
    Posts
    3
    Friends, Thank you for your replies. Using code is probably beyond my capabilities, but I can produce a result using the table and query structure even though it is inelegant. I am keeping separate fields rather than concatenating into a single field.

    The method is to insert Applicant ID fields into the Applications table. By linking multiple Applicant tables to the Application table, a query can produce a flat file structure ie one Application ID with many applicants. Inelegant but it works.

    Now the real need for having the information in a single row is for reporting, whether by printing a table or creating a formal report. Otherwise, if there are multiple rows, ie one row for every applicant attached to an application, it is very difficult to produce a report that doesn't show duplicate information. For example the Application or Project Title will appear next to each applicants name. Even this can be hidden if the report is grouped/designed in a particular way, although it is very limiting in terms of appearance. BUT the one problem that cannot be overcome is totalling financial amounts.

    While the Application ID and Applicants may only appear ( be visible) once in the report, becasue the underlying data repeats the application financial amount for each applicant, any total or SUM is wrong; eg if $100K was awarded and there are 4 applicants attached to the project, the total will be 4 x $100K, not $100K as it should be.

    If you, or anyone else, has suggestions for a better query or a simple statement to perform the task, I would be interested to hear them.

    Bless you all for your charitable assistance.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good luck
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'd design a query(s) that make/append to a temp table and then base a crosstab against that temp table.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2009
    Posts
    3

    Crosstabbing what fields though?

    Hi 'Stormy

    I had considered a crosstab query but nout sure what fileds to use. Crosstabs seem most suitable for obtaining a value like a sum or average. If you want data returned they don't seem to work. Eg If you use Applicant ID's, the actual ID's become the headings and then you cannot link to them in queries. How do you overcome that problem?

    Regards
    George

Posting Permissions

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