Tricky concatenation problem

[shanef1981]

Hey folks!

I am trying to work out how to concatenate several entries in one field if two common fields have the same data in it.

For example:

Invoice   SAP_Code    Project    PO       SN
-------------------------------------------------
22457845  010-08745   X234       226543   21215
22457845  010-08745   2345       265854   6744332
22457845  010-01124   Y435       r54445   p774562

The result I would like from this is that the first two lines are combined, so the project, PO and SN fields are concatenated as the Invoice and SAP_Code fields are the same.

Because the third line has a different SAP_Code entry I would like this one left alone.

I have written concatenation modules before, but never based on two matching fields, only one.


Any help or guidance on this would be massively appreciated - thanks

 

[jedraw]

Could you provide more context please?
The Invoice and SAP_codes may be the same but the Project is different.
What exactly are you concatenating and why?
Maybe there is a better query/approach depending on what the real need is.
There must be more to this.

 

[dhookom]

Did you check the faqs of this forum? Specifically faq701-4233.

As Jedraw suggests, it would help if you provided your desired display.

Duane
Hook'D on Access
MS Access MVP

 

[shanef1981]

Thanks for your responses guys...

Input:

Invoice   SAP_Code    Project    PO       SN
-------------------------------------------------
22457845  010-08745   X234       226543   21215
22457845  010-08745   2345       265854   6744332
22457845  010-01124   Y435       r54445   p774562
21565422  021-55451   R265       m26455   o995

Output:

Invoice   SAP_Code    Project     PO               SN
-------------------------------------------------------------------
22457845  010-08745   X234, 2345  226543, 265854   21215, 6744332
22457845  010-01124   Y435       r54445   p774562
21565422  021-55451   R265       m26455   o995

I did a really crap job of trying to describe it I guess.

Basically, if the Invoice and SAP_Code fields are the same, I want it to concatenate the Project, PO and SN fields together with comma separation.

I've cracked concatenating fields with one common field, but not two - and it's giving me headache...

 

[dhookom]

If you want two fields, just use the concatenate function twice. If you have three columns you want to concatenate, call the same function three times. Each time you call it, you select a different field in the function.

Concatenate("SELECT Project FROM tblNoNameGiven WHERE SAP_Code = '" & [SAP_Code] & "'")

Concatenate("SELECT PO FROM tblNoNameGiven WHERE SAP_Code = '" & [SAP_Code] & "'")

Concatenate("SELECT SN FROM tblNoNameGiven WHERE SAP_Code = '" & [SAP_Code] & "'")

Duane
Hook'D on Access
MS Access MVP