Using a query to get data from multiple rows into a single row?

MarkN

New Member
Joined
Aug 31, 2015
Messages
7
Hi, peoples.

I am a complete newbie to Access, so please be gentle with me. :LOL: I'm maybe an advanced beginner with Excel - I can do some simple macros, but nothing fancy.

Here's my story:

I have a table which has data in multiple rows, and I need to put it into a single row. Each table describes objects (which have a unique ID), and each object has 1-6 sub-objects. Each sub-object is in its own row, and each sub-object has various attributes (fields). Also, each object has attributes which are common to all sub-objects and which are repeated in each sub-object row. For example:



IDObjectIDSub_objectIDAttribute 2Attribute 3Attribute 4Attribute 5Attribute 6Attribute 7Attribute 8
1
11Truck Drivertessaract29BigHeavyweightBlackTen
212Truck Drivercube92SmallLightweightredTen
313Truck Driverblock88MediumMiddleweightgreenTen
414Truck Drivertessaract51TinyLight HeavyweightpurpleTen
521Farmercube36MediumBantamweightblueNinety
622Farmercircle29SmallHeavyweightpinkNinety
723Farmeroval79BigLightweightredNinety
824Farmer
disk46SmallHeavyweightBlackNinety
931Salesmantriangle52SmallHeavyweightblueTwelve
1032Salesmandisk16MediumLightweightredTwelve
1133SalesmanSquare3BigMiddleweightpinkTwelve
1234Salesmancircle72SmallLight HeavyweightgreenTwelve
1335Salesmancircle36BigBantamweightsevenTwelve

<tbody>
</tbody>


As you can see, Object 1 has 4 sub-objects. Attribute 2 (Truck Driver) and Attribute 8 (Ten) are common to its 4 sub-objects. Attributes 3-7 are different for each sub-object.

I need to get from this layout, to something that looks like this:

IDObjectIDAttribute 2Attribute 3Attribute 4Attribute 5Attribute 6Attribute 7Attribute 8
11Truck Driver"tessaract, cube, block, tessaract""29, 92, 88, 51""Big, Small, Medium, Tiny""Heavyweight, Lightweight, Middleweight, Light Heavyweight""Black, red, green, purple"Ten
22Farmer"cube, circle, oval, disk""36, 29, 79, 46""Medium, Small, Big, Small""Bantamweight, Heavyweight, Lightweight, Heavyweight""blue, pink, red, Black"Ninety
33Salesman"triangle, disk, Square, circle, circle""52, 16, 3, 72, 36""Small, Medium, Big, Small, Big""Heavyweight, Lightweight, Middleweight, Light Heavyweight, Bantamweight""blue, red, pink, green, seven"Twelve

<tbody>
</tbody>



I don't mind if this is a two-step process where I perform a query in Access and then export to Excel and do some concatenation there. My tables started as Excel files and need to wind up in CSV format anyway, for the next step.


Is this something that is doable with a simple query? Or do I need to start learning VBA? I need a repeatable process as I'm going to be doing this about once a month.

All pointers welcome!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Access has nothing really able to do this since its not practical. No one stores data this way. Your original top table is correct and normalized for database use.
VB code may be able do it , but it would be a lot of work for what outcome?
Build a report, and have it tiered by Object ID. Zero effort for 'almost' the same outcome. Otherwise , its LOTS of coding.
 
Upvote 0
Build a report, and have it tiered by Object ID.

Thanks for the response.

Please elaborate. I don't care about the method, as long as the result is something that can be stored as delimited text of some kind that can be used elsewhere than Access.
 
Upvote 0
You can export the data to a spreadsheet, (as is in your 1st table) docmd.transferspreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsQry1", vFile, True, "sheet1"

or via text
DoCmd.TransferText acExportDelim, spec, Table, "c:\folder\file.txt", True
 
Upvote 0
I have solved the problem. It turns out that far from the "can't be done" that was suggested, it was not difficult.

My solution is not elegant, I'm sure. Okay, it's probably a kludge! :LOL: But it gets me where I want to go. For the benefit of any who may be trying to do something similar, and for correction or improvements from experts, I'll outline my steps.


  1. Import the raw data from Excel (of course.)
  2. Create & run a make-table query to extract only the Object-specific data (Object ID, Attribute 2, and Attribute 8). Let's call this ObjectTable.
  3. Create & run a make-table query to extract only the ObjectID and the sub-object data (Sub_objectID, Attribute 3 Attribute 4, Attribute 5, Attribute 6, Attribute 7). Create one query for each possible sub-object ID. So, I'll need 5 queries for the above example. We'll call these Sub-ObjectTables 1-5.
  4. Create a relationship between the table created in step 2, and each of the tables created in Step 3. The linking is the Object ID.
  5. Create & run a make-table query to extract all the fields from the ObjectTable, and all but the ObjectID from Sub-ObjectTables 1-5. (No need to repeat the ObjectID.)
  6. This will put all of the original data on a new table on one line, instead of being on three lines.
  7. Create & run a make-table query to extract the fields which are Object specific (Object ID, Attribute 2, and Attribute 8), then use the concatenation function to bring together the appropriate data for the remaining fields. For example: Attribute 3: [Sub-Object 1 Data_Attribute 3] & "/" & [Sub-Object 2 Data_Attribute 3] & "/" & [Sub-Object 3 Data_Attribute 3] & "/" & [Sub-Object 4 Data_Attribute 3] & "/" & [Sub-Object 5 Data_Attribute 3]. This will produce, for ObjectID 1, the following: tessaract/cube/block/tessaract// (Note that there is no sub-object 5 for this object, so the last field is empty.)
  8. Now export the new table to Excel and we're done!


This works, but with errors. I'm getting duplicate rows in the tables I create. By the time I get to the last one, some rows are showing up 8 times. :oops: That may be a subject for another thread.

If anyone can suggest a more efficient method, I'm all ears! Er... eyes. :)

But now, for The Big Question:

How do I save this database so that I can repeat this process with other data sets? Same formatting, but different data, and needing to be updated monthly.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top