Results 1 to 10 of 10
  1. #1
    Helge is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    5

    Combine several records into one record

    Combine several records into one record



    How can I combine several records in a table into one record?

    Suppose that I have a table like Table1 in the attached image.
    Then I want to combine all records with the same value for Key1 in one record.
    The result is shown in Table2 in the attached image. Table2 could be query or table.

    I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
    Alternatively I could accept to turn to VBA that could do it. Any good links about this?
    Click image for larger version. 

Name:	Tables.jpg 
Views:	22 
Size:	27.4 KB 
ID:	15156

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Have you tried using the query builder? You can save a named query object after you drag the tables to the design area and create a join on the Key fields. Afterwards, you can look at your query object in datasheet view, design view, or SQL view, etc. You will also be able to integrate the query object in your application by referencing the name.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you're talking about something close to a crosstab query but I'm not sure it is what you're really after.

    It would look more like this:
    Key1 Key 2 Value 1 Key 2 Value 2 Key 2 Value 3
    Key1 Value 1 1.1 1.2 1.3
    Key1 Value 2 2.1 2.2 2.3
    Key1 Value 3 3.1 3.2 3.3

    If that's what you're after look at your the crosstab query wizard.

  4. #4
    Helge is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    5
    Yes, it is perhaps close to a crosstab query, but it is NOT a crosstab query.
    I need Key2 (e.g. 'W2') together with the data value (e.g. 3.1) in the data record.
    The pairs (Key2, Data) should be 'left-aligned' within Table2. Therefore the bottom right corner of Table2 is empty in my example.

    Quote Originally Posted by rpeare View Post
    you're talking about something close to a crosstab query but I'm not sure it is what you're really after.

    It would look more like this:
    Key1 Key 2 Value 1 Key 2 Value 2 Key 2 Value 3
    Key1 Value 1 1.1 1.2 1.3
    Key1 Value 2 2.1 2.2 2.3
    Key1 Value 3 3.1 3.2 3.3

    If that's what you're after look at your the crosstab query wizard.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    well in a crosstab that would be empty as well because there would be no value, I just filled it in as an example.

    The issue here is that based on your example data there's no way to determine which is the 'second' record and which is the 'third' record



    if I were to use your data a crosstab would actually look like this:

    Key1 W2 X2 Y2 Z2
    A1 1.1 1.2 1.3
    B1 2.1 2.2 2.3
    C1 3.1 3.2

    In other words it would be a matrix that cross references all values for Key1 and Key2. What you're proposing isn't a cross reference and would likely require a lot of coding in a query that would be extremely sluggish in all likelyhood (I say that because it would probably involve a lot of domain functions which shouldn't be performed in queries in my opinion).

    so for instance, just to get the first value you'd have to have something like

    DMIN("[Key2]", "Table1", "[Key1] = '" & [Key1] & "'")

    what the domain function does is process your ENTIRE dataset looking for the minimum value (or count or max or any other of the d functions) to fill in this value. So in this case you would end up with your 9 records being processed at least 27 times, effectively tripling the processing time of this one field for this small dataset. Then you'd be proposing using more complex domain functions for the remaining column values in your proposed format. Are you sure that's what you want to do?

  6. #6
    Helge is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    5
    Thank you for your answers.
    I started out with:
    "I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
    Alternatively I could accept to turn to VBA that could do it. Any good links about this?"
    So now I think that it's time for me to give up and use VBA instead!

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm saying it is possible, but inadvisable, using the domain functions to build what you want in a query and I gave you an example of a domain function to do it. I'm not sure why the crosstab won't work for you because you seem to have a normalized structure on the example table, which to me implies that you could have an unlimited number of KEY2 values for a matching KEY1 value. Let's take the example to the extreme.

    Let's say you have 100 values for key 1 and 1000 POSSIBLE values for Key1.

    Based on your description, if you had Key 2 value of X1 in every record of Key 1 that value of X1 could appear in any column, so if this were going to be used as a visual reference a person would have to know the value of X1 then scan every single column for the value of X1 to find the value they were looking for.

    Perhaps if you could explain why you want it in the format you've asked for it might make more sense but from the outside it doesn't (at least to my meager mind).

    At any rate, if you are determined to do it the way you've described I do not see a great alternative to using a temp table and vba code to cycle through the records.

  8. #8
    Helge is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    5
    After some tests in SQL I have decided to do it in VBA!
    When I have finished the VBA I'll upload it for your information.

  9. #9
    Helge is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    5
    Hi rpeare

    I have made a VBA routine that can do it.
    Please notice that tables and fields have new names:

    Table1 : Data Processes Output ' Input table
    Field11 : Process ' Field 1
    Field12 : Output flow ' Field 2
    Field13 : Output ' Field 3
    Table2 : Data Processes Output W ' Output table
    Field21 : Process ' Field 1
    Field22 : Output Flow ' Field 2, 4, 6, 8, ...
    Field23 : Output ' Field 3, 5, 7, 9, ...

    The VBA code is attached.


    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sounds like you already have a solution, but for future reference, review:

    http://allenbrowne.com/func-concat.html

    http://forums.aspfree.com/microsoft-...ry-322123.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.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-19-2013, 08:15 AM
  2. Combine Two Records into One from One Table
    By darcien in forum Queries
    Replies: 3
    Last Post: 10-10-2012, 09:17 AM
  3. Replies: 2
    Last Post: 10-02-2012, 03:18 PM
  4. Combine records in a table
    By smoothlarryhughes in forum Queries
    Replies: 3
    Last Post: 09-14-2012, 08:14 PM
  5. Replies: 8
    Last Post: 01-21-2011, 10:28 AM

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