0

I am trying to combine texts from table fields with comma delimiter but can't find any function (TEXTJOIN() is available on Excel) like that. So, sample tables looks like-

Job Table: (JobNo is number fiels and Primary Key)

JOBNo JOBDate CusID CusName CusPO ItemCode Item PODate
443 44562 4 UNIT-01 CO--060637 FG000038 HANGER STICKER LLH 44626
442 44563 5 UNIT-02 CO--017590 FG000023 MAX STICKER 44626
441 44564 5 UNIT-02 C0--017591 FG000023 MAX STICKER 44626
440 44565 5 UNIT-02 CO--17602 FG000001 LEG STICKER MAX DUBAI 44626

Invoice Table:

InvoiceNo InvoiceDate CusID CusName CusPO JobNo SalesPerson
1 44577 4 UNIT-01 PO-000023 443 Mr. Person
2 44578 4 UNIT-01 CO-056449 443 Mr. Person
3 44578 4 UNIT-01 CO-055752 443 Mr. Person
4 44578 4 UNIT-01 CO-054124 442 Mr. Person
5 44578 4 UNIT-01 CO-056717 442 Mr. Person
6 44578 4 UNIT-01 CO-056419 441 Mr. Person
7 44578 4 UNIT-01 CO-056428 441 Mr. Person
8 44578 4 UNIT-01 CO-056357 440 Mr. Person
9 44578 4 UNIT-01 CO-056186 440 Mr. Person
10 44578 4 UNIT-01 CO-54105 440 Mr. Person
11 44581 4 UNIT-01 CO-057137 440 Mr. Person
13 44586 4 UNIT-01 CO-056576 442 Mr. Person
14 44586 4 UNIT-01 CO-056579 443 Mr. Person
15 44586 4 UNIT-01 CO-056580 441 Mr. Person

Expected Result: (Invoices column is combined and expected from above two tables).

JOBNo JOBDate CusID CusName CusPO ItemCode Item PODate Invoices
443 44562 4 UNIT-01 CO--060637 FG000038 HANGER STICKER LLH 44626 1,2,3,14
442 44563 5 UNIT-02 CO--017590 FG000023 MAX STICKER 44626 4,5,13
441 44564 5 UNIT-02 C0--017591 FG000023 MAX STICKER 44626 6,7,15
440 44565 5 UNIT-02 CO--17602 FG000001 LEG STICKER MAX DUBAI 44626 8,9,10,11

Basically I want to aggregate/combine/join each invoice number against JobNo in query result. Any help/clue is greatly appreciated.

I am using MS-Access 365 (16.0... 64 Bit).

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • The linked answer (mark as duplicated) works but little bit slower. – Harun24hr Mar 08 '22 at 02:16
  • There is no fast way to do it in MS Access. For every row of the result set, the db engine must run an additional query to gather up the related values which will be concatenated for that row. It's a substantial workload. – HansUp Mar 11 '22 at 00:26

0 Answers0