Concatenate multiple rows without VBA

[illusionek]

Hello!

I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.

So I am desperately trying to find a way of doing this without VBA code. Can anyone help please?

Example of data:

Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105


I am trying to achieve below:

Customer Name |Order Number
Smith |O101, O103
Brown |O102, O105
Green |O104

 

[illusionek]

Data and all calculations are in Ms Access. I use Excel only as an output file to retrive data from Ms Access

 

[JHB]

What if you create a new query based on the query with the function?

 

[namliam]

You can do this in excel only, but you are limited to FINITE number of options
i.e.

Select x0.customername
     , x0.Ordernumber
     , x1.Ordernumber
     , x2.Ordernumber
     , x3.Ordernumber
     , x4.Ordernumber
From      (Select customername, ordernumber from yourtable ) X0
left join (Select customername, ordernumber from yourtable ) X1 on x0.customername = x1.customername 
                                                               and x1.ordernumber  > x0.ordernumber
left join (Select customername, ordernumber from yourtable ) X2 on x1.customername = x2.customername 
                                                               and x2.ordernumber  > x1.ordernumber
left join (Select customername, ordernumber from yourtable ) X3 on x2.customername = x3.customername 
                                                               and x3.ordernumber  > x2.ordernumber
left join (Select customername, ordernumber from yourtable ) X4 on x3.customername = x4.customername 
                                                               and x4.ordernumber  > x3.ordernumber

Is limited to 5 orders, you can extend this to 10 or 100 but still the limit will exist.
The advantage of a piece of VBA, it is limitless regardless of the number of records you may have.

 

[illusionek]

@JHB

Unfortunately, I am getting the same error message but I have to admit it sounded as a brilliant idea

@namliam

I am so sorry I keep getting error message: "Syntax error (missing operator) in query expressions ''.

I noticed missing ; at the end so I added it but still can't get it work :banghead: Would be grateful for suggestions.

 

[namliam]

Forgot about the access requirement of brackets and "AS"

Try this:

SELECT X0.customername, X0.Ordernumber, X1.Ordernumber, x2.ordernumber, x3.ordernumber, x4.ordernumber
FROM ((((( (Select customername, ordernumber from yourtable )  AS X0 
LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X1 ON X0.customername = X1.customername and x1.ordernumber  > x0.ordernumber)
LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X2 ON X1.customername = X2.Customername and x2.ordernumber  > x1.ordernumber)
LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X3 ON X2.customername = X3.Customername and x3.ordernumber  > x2.ordernumber)
LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X4 ON X3.customername = X4.Customername and x4.ordernumber  > x3.ordernumber)
Inner Join (select customername
           , min(ordernumber) as MinOrder    from yourtable 
            group by customername                           )  as X  on x0.customername = x.customername  and x0.ordernumber  = x.minorder )

 

[illusionek]

Thanks a lot!! You code works very well. As you mentioned the only disadvantage is that you can run it for a fixed number of records.

I still keep fingers crossed someone can advise a more flexible method However if not definitely I go with your code.

Really appreciate help.

 

[namliam]

The best way to do this is the one way you dont want to go (for some misterious reason), VBA

 

[illusionek]

I would love to go with VBA solution as it would be much easier and cleaner.

However as I mentioned in my first post, I need to be able to link this query to Excel file. I tried couple different VBA codes and each time I get error message in Excel saying:

Undefined function * in expression

This the only reason I cannot use VBA code for this. As far as I can see there is no solution to this error, I tried several possible solutions

 

[namliam]

Why do you link this in excel? I.e. can you build the "Proper" list using some Excel vba instead?

Or can you push the data from access instead of pulling with excel?

 

[illusionek]

Using VBA Excel would be very difficult. This report gets data from six different files, which are combination of CSV, TXT and XLS. Unfortunately I cannot push the data because the brief was that end users should have only exposure to Excel file without the need to use Access at all. Basicially noone else knows how to use Access. This is also the reason for linking query to Excel.

 

[namliam]

Well, from excel/office automation you can open access running a pre-created macro to push the query from access into a CSV, then open the CSV in excel.

Only "biggy" being people need to have access installed, guess that might be true as well if you are fetching data from it.

On the other hand, doing some office automation to open access, run export macro to create a csv shouldnt be that hard.... Just run a command line....
You can find out more about the access command line here:
http://office.microsoft.com/en-001/access-help/command-line-switches-for-access-HA010166605.aspx

 

[illusionek]

Thanks for the above, I have done couple .bat files in the past that had simila code in them. I found it very handy and useful option.

Just a question, is there any name for the syntax you used in your query to concatenate strings? I kind of understand what it does but I would like to read more as I do not like to just copy/paste code I do not fully understand

Many thanks.

 

[namliam]

The name would be "Subselect" and "Self join"

Subselect is simply executing queries in queries
Self join is a join of a table or query back into itself

 

[illusionek]

Hello,

I just discovered an error in this code and I wonder if you would be able to help me?

It seems to multiple rows as per attached print screen. I would hope to see only first row which have all relevant dates.

Is there any way of fixing it? It works fine if there are any two entries but it goes into error for more.

 

[namliam]

would need to know more about the purpose of this query and its design even its SQL

 

[illusionek]

This is exactly the same query you kindly provided previously. It works well for the table from my initial post:

Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105


However if you add on another Smith it stops working and instead of groupping by customer name, it multiplies rows for the customer.

Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105
Smith |O106

I just need to be able to concatenate all Order Numbers for each customer in one record. The problem is that sometimes I may have even 10-15 different order numbers for each customer.

SELECT X0.customername, X0.Ordernumber, X1.Ordernumber, x2.ordernumber, x3.ordernumber, x4.ordernumber FROM ((((( (Select customername, ordernumber from yourtable )  AS X0  LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X1 ON X0.customername = X1.customername and x1.ordernumber  > x0.ordernumber) LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X2 ON X1.customername = X2.Customername and x2.ordernumber  > x1.ordernumber) LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X3 ON X2.customername = X3.Customername and x3.ordernumber  > x2.ordernumber) LEFT JOIN  (Select customername, ordernumber from yourtable )  AS X4 ON X3.customername = X4.Customername and x4.ordernumber  > x3.ordernumber) Inner Join (select customername            , min(ordernumber) as MinOrder    from yourtable              group by customername                           )  as X  on x0.customername = x.customername  and x0.ordernumber  = x.minorder )

 

[illusionek]

Please, please anyone any suggestions?

This little error holds up my entire project :banghead:

I was looking high and low and cannot find any other solution that would not involve VBA code/function. So I really need to make this work.

 

[namliam]

have you considered "grouping" by the customer name and taking the max of each order number? This should only get one record per customer and get the proper (unique) order number

 

[illusionek]

I did but maybe I got syntax wrong?

I used the code you provided below and then used & to concatenate results but when I selected Max order number I got a number i.e. 6 if there were six digits in my orders instead of proper order number.