0

My situation is I have been given multiple text files with one value per line (categories). What I have done is imported those files each into their own tables and added a second column representing the category number. So table one might be :

value    cat
-----    ----
steve    1
joe      1
sara     1
and so on...

table two might be :

value    cat
-----    ----
sara     2
and so on...

each of the tables will have its respective cat number for every row. It is only the value column which will be different.

My goal here is to create a single output that combines all these tables together into one (no duplicate value) yet concatenating the cat column. Something like this :

value    cat
-----    ----
steve    1
joe      1,6
sara     1,2
jenn     3
dave     5,7,15
and so on...

Why? Well, the data is going to change occasionally and I will always be given plain text files as the source. I figure this way I can update fairly quickly (text import on each table) then just use the query to get my result. The final result query is to create a suitable file for MySQL import.

I am fairly good with MySQL (I would just do a join on all, group by value column, concatenate the cat column), but I found there is no concatenate function with SQL I can use here...

EDIT:

Looks like someone marked this as a dupe. I have any number of different tables that need joined together here unlike the dupe link mentioned. Yes, it shows a way to concatenate, but I am looking at 10 tables (all with a value and cat column) that need joined (no duplicate value and the cat column concatenated for each).

EDIT 2:

Ok, I have all the tables joined into a new table (value and cat columns) called aresults. I added the ConcatRelated() function and am trying to use it for my actual results. Right now I have a single table that looks something like this :

value    cat
-----    ----
steve    1
steve    2
steve    3
jenn     8
joe      6
... and so on
and so on...

What I tried as a query given the example on the linked page :

SELECT value, ConcatRelated("cat", "aresults", "value = " & [value], "value", ",") AS categories
FROM aresults;

Gives me an Error 3075 : Syntax error (missing operator) in query expression 'value='. (roughly 600k times lol) forcing me to endtask Access. What is the correct way to use this as the example is using multiple tables yet this would only be one?

user756659
  • 3,372
  • 13
  • 55
  • 110
  • 1
    You'll need all records in a single table anyway to work with them. UNION them all together into one table. And if the data is going to end up in MySql, why not create the combined table there and use the native concatenation? – Andre Jan 10 '16 at 00:16
  • My mistake there... typed that wrong on my end... it is actually value as is the column in the table. Will edit the post here in a minute. – user756659 Jan 10 '16 at 06:17
  • Noted. It was a copy/paste just that the examples I give here were 'value' and using names to make it easier to view. – user756659 Jan 10 '16 at 06:28
  • 2
    Since your `value` column is text, you need this as `strWhere`: `"value = '" & [value] & "'"` – Andre Jan 10 '16 at 09:03
  • 2
    You will need a `GROUP BY value` to end up with one record per value. See this for performance considerations: http://stackoverflow.com/questions/34684316/access-2013-query-with-concatrelated-containing-50000-entries-taking-forever --- But again, doing the concatenation in MySql will probably be a lot faster. – Andre Jan 10 '16 at 09:05
  • Okay, that worked! Is there any way to sort the concatenated values by chance? I noticed a 10,8 for example instead of 8,10. Not a big deal, but would make it easier to view. Regarding the MySQL, yeah I get I could have done all this awhile ago and be done with it, but this will not change often and I want it local (only the results will be imported for use). – user756659 Jan 10 '16 at 20:41

0 Answers0