0

I have a question regarding joining some large tables then attempting to concatenate multiple entries of an attribute. The data is stored in Access which is where I am attempting to restructure the data via queries for my use case.

I am able to join the tables fine as seen in example.. but not sure what is the best method for concatenating multi-valued attributes.. my data set is huge so I have been having performance issues.

I have created some comparable data I am working with to give an idea on how I am joining data. I have noted the number of rows that I have for each table.

TabOrders (121,965 rows)
------------------------------------------
OrderNum | Product | ConfigInstance
------------------------------------------
1        | Product1| 100
2        | Product2| 200


TabConfigurations (121,965 rows)
-------------------------------------
ConfigInstance | Configuration
-------------------------------------
100            | C100
200            | C200


TabConfigDetails (4,021,244 rows)
--------------------------------------
Configuration | ConfigIndicator
--------------------------------------
C100          | A1V2
C100          | A2V1
C100          | A3V1
C100          | A3V2
C100          | A4V2
C200          | A1V1
C200          | A2V2
C200          | A2V4
C200          | A3V4
C200          | A3V5
C200          | A4V2


TabAttributes (27,665 rows)
-------------------------------------------
ConfigIndicator | Attribute  | Value
-------------------------------------------
A1V1            | Product    | Car
A1V2            | Product    | Bike
A1V3            | Product    | Motorcycle
A1V4            | Product    | Go Cart
A2V1            | Color      | Red
A2V2            | Color      | Green
A2V3            | Color      | Blue
A2V4            | Color      | Orange
A3V1            | Accessories| Helmet
A3V2            | Accessories| Cup Holder
A3V3            | Accessories| Cargo
A3V4            | Accessories| Trailer
A3V5            | Accessories| GPS
A4V1            | Size       | Small
A4V2            | Size       | Large

Here is the query Ive used to join everything:

SELECT TabOrders.OrderNum, TabOrders.Product, TabAttributes.Attribute, TabAttributes.Value
FROM ((TabOrders INNER JOIN TabConfigurations ON TabOrders.[ConfigInstance] = TabConfigurations.[ConfigInstance]) INNER JOIN TabConfigDetails ON TabConfigurations.[Configuration] = TabConfigDetails.[Configuration]) INNER JOIN TabAttributes ON TabConfigDetails.[ConfigIndicator] = TabAttributes.[ConfigIndicator]

And gets me:

OrderNum | Product | Attribute  | Value
------------------------------------------
1        | Product1| Product    | Bike
1        | Product1| Color      | Red
1        | Product1| Accessories| Helmet
1        | Product1| Accessories| Cup Holder
1        | Product1| Size       | Large
2        | Product2| Product    | Car
2        | Product2| Color      | Green
2        | Product2| Color      | Orange
2        | Product2| Accessories| Trailer
2        | Product2| Accessories| GPS
2        | Product2| Size       | Large

But I would like to get the data formated as below.. but the methods* I have used takes way too long and access crashes..

OrderNum | Product | Attribute  | Value
------------------------------------------
1        | Product1| Product    | Bike
1        | Product1| Color      | Red
1        | Product1| Accessories| **Helmet;Cup Holder**
1        | Product1| Size       | Large
2        | Product2| Product    | Car
2        | Product2| Color      | **Green;Orange**
2        | Product2| Accessories| **Trailer;GPS**
2        | Product2| Size       | Large

*Ive mostly attempted utilizing functions, I attempted using GetList function (I created another column CONCAT1 to be used as index.. concatenating ConfigInstance and Attribute then saved the query as DataConfigurations) GetList: GetList

Is there a better way to structure the query for better performance? It seems when function runs, it reprocesses the entire query each time its triggered. Here is the query:

SELECT DISTINCT DataConfigurations.OrderNum, DataConfigurations.Product, DataConfigurations.Attribute, GetList("Select Value From DataConfigurations As T1 Where DataConfigurations.CONCAT1 = " & [DataConfigurations].[CONCAT1],"",", ") AS Value_CONCAT
FROM DataConfigurations

This seemed to work only when processing on small amount of orders.. if I tried on entire data set it would run and hangup my computer.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
VCDeveloper
  • 3
  • 3

1 Answers1

0

Reference this other question, seems to achieve what you are wanting to accomplish. Performance issues could be result of a improper link (aka duplication) or your PC just needs to be beefier.

Community
  • 1
  • 1
Pants
  • 669
  • 7
  • 24
  • Yes, I remember looking at that. I attempted the solutions but was not able to get them to work in Microsoft access query. I dont think Coalesce or XML path are supported. – VCDeveloper Feb 09 '17 at 20:35
  • How about these: [question 1](http://stackoverflow.com/questions/92698/combine-rows-concatenate-rows) [question 2](http://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value) – Pants Feb 09 '17 at 20:40
  • I saw those too, I was running into same issue with those functions. When I debugged my observation was that the saved query DataConfigurations was re-ran each time the function was called. I also tried saving the DataConfigurations query as a table, but didnt seem to help. Im not an expert on SQL but I just want the function to work against the data group I care about for given row.. I thought about running a subquery on DataConfigurations to pull all rows with same ConfigurationInstance.. then saving that as alias then passing that to function.. but functions dont seem to recognize aliases – VCDeveloper Feb 09 '17 at 21:19