I have one 2 Input Tables
for ex:
1 Input Table
DN0000001 DN0000010 DN0000001 DN0000013 10 .\Box0001\ Agreement.pdf Samsung .\Box0001\Dir001\Text\
DN0000011 DN0000013 DN0000001 DN0000013 3 .\Box0001\ Patent.pdf Samsung .\Box0001\Dir001\Text\
DN0000014 DN0000014 DN0000014 DN0000033 1 .\Box0001\ Patent.pdf Oppo .\Box0001\Dir001\Text\
DN0000015 DN0000020 DN0000014 DN0000033 6 .\Box0001\ Legal docs.pdf Oppo .\Box0001\Dir001\Text\
DN0000021 DN0000032 DN0000014 DN0000033 12 .\Box0002\ Memo.pdf Oppo .\Box0002\Dir001\Text\
2nd Input Table
BegDoc EndDoc BegAttach EndAttach PageCount
DN0000001 DN0000004 DN0000001 DN0000005 4
DN0000005 DN0000005 DN0000001 DN0000005 1
DN0000006 DN0000006 DN0000006 DN0000010 1
DN0000007 DN0000010 DN0000006 DN0000010 4
DN0000011 DN0000013 DN0000011 DN0000015 3
DN0000014 DN0000014 DN0000011 DN0000015 1
DN0000015 DN0000015 DN0000011 DN0000015 1
DN0000016 DN0000017 DN0000016 DN0000017 2
DN0000018 DN0000021 DN0000018 DN0000021 4
But I need Output like below Table
BegDoc EndDoc BegAttach EndAttach PageCount Box File Name Folder Text Path
DN0000001 DN0000004 DN0000001 DN0000005 4 .\Box0001\ Agreement.pdf Samsung .\Box0001\Dir001\Text\
DN0000005 DN0000005 DN0000001 DN0000005 1 .\Box0001\ Agreement.pdf Samsung .\Box0001\Dir001\Text\
DN0000006 DN0000006 DN0000006 DN0000010 1 .\Box0001\ Agreement.pdf Samsung .\Box0001\Dir001\Text\
DN0000007 DN0000010 DN0000006 DN0000010 4 .\Box0001\ Agreement.pdf Samsung .\Box0001\Dir001\Text\
DN0000011 DN0000013 DN0000011 DN0000015 3 .\Box0001\ Patent.pdf Samsung .\Box0001\Dir001\Text\
DN0000014 DN0000014 DN0000011 DN0000015 1 .\Box0001\ Patent.pdf Oppo .\Box0001\Dir001\Text\
DN0000015 DN0000015 DN0000011 DN0000015 1 .\Box0001\ Legal docs.pdf Oppo .\Box0001\Dir001\Text\
DN0000016 DN0000017 DN0000016 DN0000017 2 .\Box0001\ Legal docs.pdf Oppo .\Box0001\Dir001\Text\
DN0000018 DN0000021 DN0000018 DN0000021 4 .\Box0001\; .\Box0002\ Legal docs.pdf; Memo.pdf Oppo .\Box0001\Dir001\Text\; .\Box0002\Dir001\Text\
I need to Populate Box, File Name, Folder, Text Path field from 1st table and the Same field in both tables are BegDoc EndDoc
But for the (Box File Name Folder Text Path) 4 fields I need to concatenate vales from the first Input table by matching BegDoc and EndDoc Number
Ex:
.\Box0001\; .\Box0002\ Legal docs.pdf; Memo.pdf Oppo .\Box0001\Dir001\Text\; .\Box0002\Dir001\Text\
What I have tried:
I have tried to Update query but didnt got result