Concatenate Rows based on grouped query

[cprobertson1]

I have recently had an issue grouping with a memo field and removing something other extraneous data - but today the specificaiton has changed! (Again(after spending most of last week working on that problem)) - and now I need to add the contract number that corresponds to each rejection...

Outstanding!


I've given an idea below of what I need to happen: tbl1 [GROUPED] is the result of the previous GROUP BY clause - tbl2 [RAW] is one of the tables used in teh GROUP BY clause - and tbl3 is what I need as an output.

As you can see, I need to concatenate all instances of column C from tbl2, based on a matching columns A AND B from tbl1 and tbl2

tbl1 [GROUPED]

col_A   col_B   col_C   col_D   col_E   col_...n
A       1       doc1    reason  excuse  
A       1       doc2    reason  excuse  
A       1       doc3    reason  excuse
B       1       doc     reason  excuse
B       2       doc     reason  excuse
B       3       doc     reason  excuse
C       1       doc     reason  excuse
D       1       doc     reason  excuse
E       1       doc     reason  excuse

tbl2 [RAW]

col_A   col_B   col_C   col_...n
A       1       111 
A       1       222 
A       1       333 
B       1       444 
B       2       555
B       3       666
C       1       777
D       1       888
E       1       999

tbl3 [grouped + concatenated RAW]

col_CONCAT	col_A	col_B	col_C	col_D	col_E	col_...n
111,222,333	A	1	doc1	reason	excuse	
111,222,333	A	1	doc2	reason	excuse	
111,222,333	A	1	doc3	reason	excuse
444		B	1	doc	reason	excuse
555		B	2	doc	reason	excuse
666		B	3	doc	reason	excuse
777		C	1	doc	reason	excuse
888		D	1	doc	reason	excuse
999		E	1	doc	reason	excuse

I'm not entirely sure how to approach the problem - what I think I need to do, is a SELECT subquery that matches up and groups any instances of columns A and B, with C not being grouped - and then carrying out the concatenation (which is the problematic part) and finally join all that to the existing query.



I believe Allen Browne's ConcatRelated function may be key here, but I can't figure out how to integrate it into the group-by function - it'd be fine where I manually searching for a given PO or part number, but I need to carried out on all of them!

Any suggestions?

Many thanks in anticipation!

 

[plog]

Why? What does the data in the third table help you achieve? Seems like it works against you. Suppose you want to find out information about just contract 222? How are you going to do that?

You want to break normalization and have given no reason for why that is necessary. Until you can explain what a concatenated contract field achieves I think this is a bad idea and you should not do it.

Without that explanation my advice is to store your data in tables properly (one record for each contract) and if you need a certain format as an output, then you build queries to achieve it.

 

[jdraw]

I agree with plog --if you're going to use Access(or any RDBMS) --you should get your tables designed/normalized to make proper use of the database system.
If there are other circumstances/constraints that you haven't mentioned, please tell us what they are.

Good luck.

 

[cprobertson1]

Why? What does the data in the third table help you achieve? Seems like it works against you. Suppose you want to find out information about just contract 222? How are you going to do that?

Oops! Sorry, should have specified!

The rest of the database is all standardised and normalised - this messy part is just for a report.

We're needing a list of rejected documents from our suppliers: each document applies to single part, BUT multiple contracts may have that part (for instance, the customer may have ordered 10 duplicate contracts each under its own contract number)

I've got a query for the active rejections (3 joined tables): which is grouped by PO and Part number - but that leaves out the contract number. Adding the contract number into the grouping, or joining it to the original table means we have duplicate rejection details under each contract number (which is unwieldy)

The problem is that we have around 20 current rejections, corresponding to 70 odd parts.

13699			4700108890	P4000056583	WRONG UT			details
13745,13746,13747	4700113670	P2000066628	INCORRECT SCAN PLAN		details

versus the following (bearing in mind that the "details" field is a memo field and contains a lot of technical details about the rejection (and is usually 500+ characters)

13699	4700108890	P4000056583	WRONG UT			details
13745	4700113670	P2000066628	INCORRECT SCAN PLAN		details
13746	4700113670	P2000066628	INCORRECT SCAN PLAN		details
13747	4700113670	P2000066628	INCORRECT SCAN PLAN		details

In the second case, the details section swamps out the report and we end up with 20 odd pages for what should be a two-to-three page report.

--EDIT--
I should probably also mention that the report's SQL is dynamically generated based on user's search criteria (for instance, sometimes we want to pull the rejection details for a single job, a single part, or the entire table)