asked on

Access

I have the following access file avialble which expert exchange experts helped me out to figure it out. I have almost the same question. I included the vba and the querry that helped in my previous code.
On the attachment you can find the Table2 which is the inital table I have and here is how I need it to look like: (FYI: "Current" table is for the old question just to help with coding).

FYI: The date for each number has to be the latest post date.
Also, the sequence of the Work Codes on each cell for each SRO Number (RCV, RCV....) has to be based on the Trans Date and Trans Number.

Thanks!

Final-Access1.accdb

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pauledwardian

ASKER

Thanks a lot. But for instance on S34 the sequence should be HF, HF, HF, HF, HF TIG, TIG, AIR, TIG. The querry shows it as HF, HF, TIG, HF, TIG, HF, AIR, HF, TIG!
Please take a look at the attachment.
Final-Access1.accdb
Thank you
Paul

Patrick Matthews

Paul,

I had mistakenly assumed that you wanted to sort the concatenated results based on the [Trans Num] column. Based on your message above, it appears that you might want to sort on the [Post Date] or [Trans Date] columns instead.

To sort on, say, [Trans Date], just replace "[Trans Num]" in the function call with "[Trans Date]".

I did notice that for [SRO Num] = S000000168 and [SRO Num] = S000000351 you have some instances of multiple records with the same [Trans Date] value. You can sort by multiple columns. For example, to sort first by [Trans Date] and then by [Trans Num] then replace "[Trans Num]" with "[Trans Date], [Trans Num]".

https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Patrick

pauledwardian

ASKER

Exactly, I need to sort the sequence by Transaction number and Post Date. Im sorry how can I change this querry to meet my requirment:

SELECT [SRO Num], DConcat("[Work Code]", "[Table2]", "[SRO Num] = '" & [SRO Num] & "'", ", ", "",
        False, "[Trans Num]") AS WorkCodes, Max([Post Date]) AS PostDate
FROM Table2
GROUP BY [SRO Num];

Open in new window

Patrick Matthews

Replace:

"[Trans Num]"

with:

"[Post Date], [Trans Num]"

pauledwardian

ASKER

Thank you very much. One last question! how can I replace the "," in the vba code to "-" (dash)?

Thank you again

pauledwardian

ASKER

Thanks alot. I wish you could also help me with replacing commas with dash.