Help needed - Trying to concatenate rows

[clarkian11]

Hi Guys,

This is a bit of a difficult problem, so I hope someone out there can help.

What I am trying to achieve is a tool that verifies coding issues contained in raw data. It does this by comparing codes in an unacceptable coding table to the invoice data contained in a further table.

Each invoice has its on InvoiceID and each code is kept on a seperate invoice line, which looks like this:-

InvoiceID InvoiceLine Code
1 1 a
1 2 b
2 1 a
2 2 b
2 3 c

and so on.

There can be many lines to an invoice and can contain muliple unacceptable combinations. For example:

A + B could be unacceptable, A + B + C could be unacceptable and A+C could be unacceptable.

I have developed it so far as it identifies the unacceptable combinations and displays like the following



InvoiceID Unacceptable Combination
1 a, b
1 b, a
2 a, b
2 b, a
2 a, c
2 a, c
2 b, c
2 c, b

What I need is to have the data like

InvoiceID Unacceptable Combination
1 a, b
2 a, b, c

So in theory I need to group the invoiceID and possibly concatenate the unacceptable combinations whilst removing the duplicate values. Each code is unique and should be able to be repeated.

Any help or advice on this one would be very gratefully receieved.

Thanks in advance.

[blue4512]

Run a query that creates an addition field to concatenate your fields and check against the unacceptable combinations.
test:=[a]& ", " & [b]
I am a bit confused when you way each code is unique and should be able to be repeated. In ms access "unique" typically means it can/will not be repeated.

[clarkian11]

Hi Blue4512,

Thanks for your reply.

That is how I have done it already, which is fine but only gives me the following:

InvoiceID Unacceptable Combination
1 A, B
1 B, A
2 A, B
2 B, A
2 A, C
2 C, A

When I need it to be

InvoiceID Unacceptable Combination

1 A, B
2 A,B,C

The unacceptable codes are in a table, this is set out in two colums:

Code Unacceptable code combination

A B
A C
B A
B C
C A
C B

And so on and so fourth.

On each invoice I would only expect to one each acceptable code, which would be maybe

InvoiceID Codes

1 A, D

So if I concatanate it the way you say I would get the above, if I concatanate further I would end up with

1 A,B,B,A
2 A,B,A,C,B,A,C,A

Which would have duplicate values in the results, so would need to remove the duplicate values.

[June7]

Can't simply concatenate fields because the codes are in multiple records for each invoice. This requires 'pivoting' the data. How many different codes are there? I doubt this can be done by query and will require VBA.

[clarkian11]

Hi June7,

Thanks for the reply.

I thought that would be the case.

There are over 5600 seperate codes, max number of combinations of 10 per invoice that I have seen so far but potentially could have over 50-100 combinations if its a complicated invoice.

What VB code would you suggest? and where in the process would you suggest having it?

Many thanks

[June7]

Well, that is too many codes to allow pivot.

Programming would be a significant effort, not something I could just 'whip up'. If you don't have the programming skills I suggest you find a consultant who can offer this service. Without knowledge of your business operations, can't really suggest when this procedure should be run.

[clarkian11]

Hi June7,

Thank you very much for the info, will have a look into it, doubt I will be able to get a consultant to do it for me, cost is a big factor. Will have a look around for some coding.

[Rhino373]

This might be dirty and would have to be done through multiple queries but I would SUM the ascii value of the unacceptable combination then I would group by ID and unacceptable combination and take the FIRST of unacceptable combination. (This would get rid of duplicates like A,B and B,A since added together the sum of their ascii value would be the same. This would also show you the entry A,B. In your example it should return A,B; B,C and C,A I would then create a concatenate of the Left([unacceptable Combination],Instr([Unacceptable Combination],",")-1). This would grab the first letter of the result set and should give A, B, C (after you add a comma and space back in during the concatenation)

[June7]

Okay, I stand corrected, maybe possible in a query or several queries. Very clever idea Rhino373. You may be new to the forum but no novice.

[clarkian11]

This might be dirty and would have to be done through multiple queries but I would SUM the ascii value of the unacceptable combination then I would group by ID and unacceptable combination and take the FIRST of unacceptable combination. (This would get rid of duplicates like A,B and B,A since added together the sum of their ascii value would be the same. This would also show you the entry A,B. In your example it should return A,B; B,C and C,A I would then create a concatenate of the Left([unacceptable Combination],Instr([Unacceptable Combination],",")-1). This would grab the first letter of the result set and should give A, B, C (after you add a comma and space back in during the concatenation)

Hi Rhino373,

Sounds like a great soluation. Will have a go at it and let you know if it worked. Thank you for your help on this,

[Rhino373]

Here is a database that gives an example: http://www.access-programmers.co.uk/...ad.php?t=64611.

[clarkian11]

Hi Rhino,

Thanks a lot for the example, good piece of code.

The solution you gave is very good, although I am still trying to get it to work with my code. The difficulty I have is there is 5000 plus combinations, and the codes are made up with 5 character for example:

AA000
AA001
A0001
A0002
00001
00002

So the unacceptable combination could be:

AA000, AA001
AA000, AA001, A0001

All the first characters would have the same ASCII value.

I have tried to identify the ascii value for each character, with some success, but it has to have a - for separation, which means I am unable to sum the values.

Do you know of a way around this at all?
my dataset for the query is like the following:
invoiceID, primary code, unacceptable code combination

There can be several unacceptable code combinations like:

InvoiceID primary code, unacceptable code combination

1, AA001, A0001
1, AA001, 00001
1, AA002, 00001

Many thanks

[Rhino373]

I think I've made some mistakes in my explanation leading you a little off track. Just to be 100% sure I understand, given these possible combinations:

(I assume here there are only 2 columns, the ID and the unacceptable combination)
InvoiceID primary code, unacceptable code combination
1, AA001, A0001
1, AA001, 00001
1, AA002, 00001

Your information actually looks like this:
1, AA001, A0001
1, AA001, 00001
1, AA002, 000011, A0001, AA001
1, 00001, AA001
1, 00001, AA002

So it shows 2 for each combination forward and backward.

This is how I understand it per this quote:

I have developed it so far as it identifies the unacceptable combinations and displays like the following

InvoiceID Unacceptable Combination
1 a, b
1 b, a
2 a, b
2 b, a
2 a, c
2 a, c
2 b, c
2 c, b

If I understand this correct, you would create a Totals query. Group by InvoiceID then for the second column, make it look like this: First Entry: Left([unacceptable Combination],InStr([Unacceptable Combination],",")-1)

You can replace "First Entry" with whatever you want

From your original example the results would then look like this:
1 a
1 b
2 a
2 b
2 c

From here you can use the database example to concatenate them together.

Let me know if I'm not following this correctly.

Regards

Ryan

[Rhino373]

Oh and the second column is a Group By total as well.

Your information actually looks like this:
1, AA001, A0001
1, AA001, 00001
1, AA002, 000011, A0001, AA001
1, 00001, AA001
1, 00001, AA002

This really has 6 line items I just missed a hard return on the third item

[clarkian11]

Hi Ryan,

Thanks again for your reply.

There are two tables which hold the information.

The first is the invoice data itself, which is pulled from the system. This would hold all the information including the codes submitted, invoice amounts etc. The codes are recorded on separate invoice lines, but can be grouped by invoice ID. So from this table I am only pulling the invoice ID and codes associated with that invoice.

InvoiceID, Code
1, A0001
1, A0002
2, A0001
2, A0002
2, A0003
3, A0001
3, A0002
3, A0003
3, A0004

Then I have a second table, which holds the unacceptable code combinations which looks like:

Code, Unacceptable Code Combination
A0001, A0002,
A0001, A0003,
A0001, A0004,
A0002, A0001,
A0002, A0003,
A0002, A0004,
A0003, A0001,
A0003. A0002
A0003, A0004,
A0004, A0001

Once I have run my query, it compares the invoice data against the unacceptable code combinations and produces:

Invoice ID, Code, Unacceptable Code Combination
1, A0001, A0002,
1, A0002, A0001,
2, A0001, A0002,
2, A0001, A0003,
2, A0002, A0001,
2, A0002, A0003,
2, A0003, A0001,
2, A0003. A0002

And so on and so forth

The end result needs to look like

InvoiceID, Unacceptable codes
1, A0001, A0002,
2, A0001, A0002, A0003,
3, A0001, A0002, A0003, A0004

It can be concatcanted if needs be or new columns can be either created or updated into a table which has been set up with enough columns to cope with max possible combinations. As long as it works.

Hope this information helps.