Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
9 years ago

how to join two tables.

Hi All, i want to know how can i join two tables with a common feild to make a resultant table.
Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
9 years ago

BhaveshPatel

 

i am sending you the demo data for this issue.

 

 

Document No_Buy-from Vendor No_Posting GroupAmount Including Tax
PINV/0809/00001VEN0027FINISHED220895
PINV/0809/00002VEN0027FINISHED625408
PINV/0809/00003VEN0027FINISHED33098
PINV/0809/00004VEN0004FINISHED322341
PINV/0809/00005VEN0004FINISHED3961
PINV/0809/00006VEN0004 49838
PINV/0809/00006VEN0004 0.02
PINV/0809/00007VEN0027FINISHED15912
PINV/0809/00008VEN0027FINISHED336177
PINV/0809/00009VEN0027FINISHED710793
PINV/0809/00010VEN0004FINISHED2154058
PINV/0809/00011VEN0006FINISHED23204
PINV/0809/00012VEN0006FINISHED946444
PINV/0809/00013VEN0004 191290
PINV/0809/00013VEN0004 -0.45
PINV/0809/00014VEN0039 48687
PINV/0809/00015VEN0040COMPUTERS30720
PINV/0809/00016VEN0041OFFICE EQU1260
PINV/0809/00016VEN0041OFFICE EQU1500
PINV/0809/00017VEN0013UPS & BATT73500
PINV/0809/00018VEN0010FINISHED22781
PINV/0809/00018VEN0010FINISHED5923
PINV/0809/00019VEN0004FINISHED1057044.34
PINV/0809/00019VEN0004 -0.34
PINV/0809/00020VEN0004FINISHED8225
PINV/0809/00021VEN0004 147258
PINV/0809/00021VEN0004 -0.09
PINV/0809/00022VEN0027FINISHED301702.91
PINV/0809/00022VEN0027 0.09
PINV/0809/00023VEN0027FINISHED39671.1
PINV/0809/00023VEN0027 -0.1
PINV/0809/00024VEN0027FINISHED678730.08
PINV/0809/00024VEN0027 -0.08
PINV/0809/00025VEN0027FINISHED787962.96
PINV/0809/00025VEN0027 0.04
PINV/0809/00026VEN0027FINISHED229900.19
PINV/0809/00026VEN0027 -0.19
PINV/0809/00027VEN0027FINISHED131541.53
PINV/0809/00027VEN0027 0.47
PINV/0809/00028VEN0006FINISHED5886013.26
PINV/0809/00028VEN0006 -0.26
PINV/0809/00029VEN0004 20025
PINV/0809/00029VEN0004 -0.09
PINV/0809/00030VEN0004FINISHED143743.03
PINV/0809/00030VEN0004 -0.03
PINV/0809/00031VEN0004 15416
PINV/0809/00031VEN0004 -0.42
PINV/0809/00032VEN0004FINISHED110657.55
PINV/0809/00032VEN0004 0.45
PINV/0809/00033VEN0018COMPUTERS30000
PINV/0809/00034VEN0043COMPUTERS23500
PINV/0809/00035VEN0004FINISHED148066.35
PINV/0809/00036VEN0044FURNITURE167562
PINV/0809/00037VEN0004FINISHED180676.66
PINV/0809/00037VEN0004 200
PINV/0809/00038VEN0010FINISHED5923
PINV/0809/00038VEN0010 22781
PINV/0809/00039VEN0045 37079
PINV/0809/00040VEN0004 15416
PINV/0809/00040VEN0004 -0.42
PINV/0809/00040VEN0004 0.42
PINV/0809/00041VEN0004 20025
PINV/0809/00041VEN0004 -0.09
PINV/0809/00041VEN0004 0.09
PINV/0809/00042VEN0004FINISHED110657.55
PINV/0809/00042VEN0004 0.45
PINV/0809/00042VEN0004 -0.45
PINV/0809/00043VEN0004FINISHED143743.03

 

Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
9 years ago

2nd table.

No_Buy-from Vendor No_Posting DatePosting Description
PINV/0809/00001VEN00274/15/2008 0:00Order PO00001
PINV/0809/00002VEN00274/15/2008 0:00Order PO00002
PINV/0809/00003VEN00274/21/2008 0:00Order PO00003
PINV/0809/00004VEN00044/22/2008 0:00Order PO00004
PINV/0809/00005VEN00044/22/2008 0:00Order PO00005
PINV/0809/00006VEN00044/22/2008 0:00Order PO00006
PINV/0809/00007VEN00275/7/2008 0:00Order PO00007
PINV/0809/00008VEN00275/7/2008 0:00Order PO00008
PINV/0809/00009VEN00275/8/2008 0:00Order PO00009
PINV/0809/00010VEN00045/31/2008 0:00Order PO00010
PINV/0809/00011VEN00065/29/2008 0:00Order PO00012
PINV/0809/00012VEN00065/29/2008 0:00Order PO00013
PINV/0809/00013VEN00045/12/2008 0:00Order PO00011
PINV/0809/00014VEN00394/17/2008 0:00Order PO00015
PINV/0809/00015VEN00406/1/2008 0:00Order PO00016
PINV/0809/00016VEN00415/14/2008 0:00Order PO00017
PINV/0809/00017VEN00135/12/2008 0:00Order PO00018
PINV/0809/00018VEN00106/29/2008 0:00Order PO00019
PINV/0809/00019VEN00047/7/2008 0:00Order PO00020
PINV/0809/00020VEN00047/7/2008 0:00Order PO00021
PINV/0809/00021VEN00047/7/2008 0:00Order PO00022
PINV/0809/00022VEN00276/27/2008 0:00Order PO00023
PINV/0809/00023VEN00276/25/2008 0:00Order PO00024
PINV/0809/00024VEN00276/27/2008 0:00Order PO00025
PINV/0809/00025VEN00276/27/2008 0:00Order PO00026
PINV/0809/00026VEN00276/11/2008 0:00Order PO00027
PINV/0809/00027VEN00277/14/2008 0:00Order PO00028
PINV/0809/00028VEN00067/11/2008 0:00Order PO00029
PINV/0809/00029VEN00047/11/2008 0:00Order PO00030
PINV/0809/00030VEN00047/11/2008 0:00Order PO00031
PINV/0809/00031VEN00047/11/2008 0:00Order PO00032
PINV/0809/00032VEN00047/11/2008 0:00Order PO00033
PINV/0809/00033VEN00187/29/2008 0:00Order PO00035
PINV/0809/00034VEN00436/20/2008 0:00Order PO00036
PINV/0809/00035VEN00048/8/2008 0:00Order PO00038
PINV/0809/00036VEN00441/1/2009 0:00Order PO00039
PINV/0809/00037VEN00047/24/2008 0:00Order PO00040
PINV/0809/00038VEN00107/12/2008 0:00Order PO00034
PINV/0809/00039VEN00457/30/2008 0:00Order PO00043
  • Satish_Kumar's avatar
    Satish_Kumar
    Icon for Helper V rankHelper V
    9 years ago

    BhaveshPatel on the basis of No_ feild i have to join these table together to summerize the amount feild.

    • Satish_Kumar's avatar
      Satish_Kumar
      Icon for Helper V rankHelper V
      9 years ago

      Hi BhaveshPatel hoping that you found the solution for this issue i am waiting for the solution.

      • BhaveshPatel's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        9 years ago

        Satish_Kumar

        Sorry to keep you waiting. As I was working on the other problem.

         

        Please follow the attached screenshot for the solution.

         

        Step 1: Go to the Table 1 and select Edit Queries, Select the Merge as a new Queries.

        Merge Queries

        Step 2: Expand the newly created column and Select the amount including Tax.

        Expand the newly created column

        Step 3 : Final Results

        Final output