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

[MarkN]

Hi, peoples.

I am a complete newbie to Access, so please be gentle with me. 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:

<tbody>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] ObjectID [/TH]
[TH="bgcolor: #c0c0c0"] Sub_objectID [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 2 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 3 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 4 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 5 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 6 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 7 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 8 [/TH]

[TD="align: center"] 1
[/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] Truck Driver [/TD]
[TD="align: center"] tessaract [/TD]
[TD="align: center"] 29 [/TD]
[TD="align: center"] Big [/TD]
[TD="align: center"] Heavyweight [/TD]
[TD="align: center"] Black [/TD]
[TD="align: center"] Ten [/TD]

[TD="align: center"] 2 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] Truck Driver [/TD]
[TD="align: center"] cube [/TD]
[TD="align: center"] 92 [/TD]
[TD="align: center"] Small [/TD]
[TD="align: center"] Lightweight [/TD]
[TD="align: center"] red [/TD]
[TD="align: center"] Ten [/TD]

[TD="align: center"] 3 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] Truck Driver [/TD]
[TD="align: center"] block [/TD]
[TD="align: center"] 88 [/TD]
[TD="align: center"] Medium [/TD]
[TD="align: center"] Middleweight [/TD]
[TD="align: center"] green [/TD]
[TD="align: center"] Ten [/TD]

[TD="align: center"] 4 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] 4 [/TD]
[TD="align: center"] Truck Driver [/TD]
[TD="align: center"] tessaract [/TD]
[TD="align: center"] 51 [/TD]
[TD="align: center"] Tiny [/TD]
[TD="align: center"] Light Heavyweight [/TD]
[TD="align: center"] purple [/TD]
[TD="align: center"] Ten [/TD]

[TD="align: center"] 5 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] Farmer [/TD]
[TD="align: center"] cube [/TD]
[TD="align: center"] 36 [/TD]
[TD="align: center"] Medium [/TD]
[TD="align: center"] Bantamweight [/TD]
[TD="align: center"] blue [/TD]
[TD="align: center"] Ninety [/TD]

[TD="align: center"] 6 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] Farmer [/TD]
[TD="align: center"] circle [/TD]
[TD="align: center"] 29 [/TD]
[TD="align: center"] Small [/TD]
[TD="align: center"] Heavyweight [/TD]
[TD="align: center"] pink [/TD]
[TD="align: center"] Ninety [/TD]

[TD="align: center"] 7 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] Farmer [/TD]
[TD="align: center"] oval [/TD]
[TD="align: center"] 79 [/TD]
[TD="align: center"] Big [/TD]
[TD="align: center"] Lightweight [/TD]
[TD="align: center"] red [/TD]
[TD="align: center"] Ninety [/TD]

[TD="align: center"] 8 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] 4 [/TD]
[TD="align: center"] Farmer
[/TD]
[TD="align: center"] disk [/TD]
[TD="align: center"] 46 [/TD]
[TD="align: center"] Small [/TD]
[TD="align: center"] Heavyweight [/TD]
[TD="align: center"] Black [/TD]
[TD="align: center"] Ninety [/TD]

[TD="align: center"] 9 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] Salesman [/TD]
[TD="align: center"] triangle [/TD]
[TD="align: center"] 52 [/TD]
[TD="align: center"] Small [/TD]
[TD="align: center"] Heavyweight [/TD]
[TD="align: center"] blue [/TD]
[TD="align: center"] Twelve [/TD]

[TD="align: center"] 10 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] Salesman [/TD]
[TD="align: center"] disk [/TD]
[TD="align: center"] 16 [/TD]
[TD="align: center"] Medium [/TD]
[TD="align: center"] Lightweight [/TD]
[TD="align: center"] red [/TD]
[TD="align: center"] Twelve [/TD]

[TD="align: center"] 11 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] Salesman [/TD]
[TD="align: center"] Square [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] Big [/TD]
[TD="align: center"] Middleweight [/TD]
[TD="align: center"] pink [/TD]
[TD="align: center"] Twelve [/TD]

[TD="align: center"] 12 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 4 [/TD]
[TD="align: center"] Salesman [/TD]
[TD="align: center"] circle [/TD]
[TD="align: center"] 72 [/TD]
[TD="align: center"] Small [/TD]
[TD="align: center"] Light Heavyweight [/TD]
[TD="align: center"] green [/TD]
[TD="align: center"] Twelve [/TD]

[TD="align: center"] 13 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 5 [/TD]
[TD="align: center"] Salesman [/TD]
[TD="align: center"] circle [/TD]
[TD="align: center"] 36 [/TD]
[TD="align: center"] Big [/TD]
[TD="align: center"] Bantamweight [/TD]
[TD="align: center"] seven [/TD]
[TD="align: center"] Twelve [/TD]

</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:

<tbody>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] ObjectID [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 2 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 3 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 4 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 5 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 6 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 7 [/TH]
[TH="bgcolor: #c0c0c0"] Attribute 8 [/TH]

[TD="align: center"] 1 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] Truck Driver [/TD]
[TD="align: center"] "tessaract, cube, block, tessaract" [/TD]
[TD="align: center"] "29, 92, 88, 51" [/TD]
[TD="align: center"] "Big, Small, Medium, Tiny" [/TD]
[TD="align: center"] "Heavyweight, Lightweight, Middleweight, Light Heavyweight" [/TD]
[TD="align: center"] "Black, red, green, purple" [/TD]
[TD="align: center"] Ten [/TD]

[TD="align: center"] 2 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] Farmer [/TD]
[TD="align: center"] "cube, circle, oval, disk" [/TD]
[TD="align: center"] "36, 29, 79, 46" [/TD]
[TD="align: center"] "Medium, Small, Big, Small" [/TD]
[TD="align: center"] "Bantamweight, Heavyweight, Lightweight, Heavyweight" [/TD]
[TD="align: center"] "blue, pink, red, Black" [/TD]
[TD="align: center"] Ninety [/TD]

[TD="align: center"] 3 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] Salesman [/TD]
[TD="align: center"] "triangle, disk, Square, circle, circle" [/TD]
[TD="align: center"] "52, 16, 3, 72, 36" [/TD]
[TD="align: center"] "Small, Medium, Big, Small, Big" [/TD]
[TD="align: center"] "Heavyweight, Lightweight, Middleweight, Light Heavyweight, Bantamweight" [/TD]
[TD="align: center"] "blue, red, pink, green, seven" [/TD]
[TD="align: center"] Twelve [/TD]

</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!

 

[ranman256]

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.

 

[MarkN]

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.

 

[ranman256]

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

 

[MarkN]

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! 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. 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.