wasim

wasim

  • NA
  • 4
  • 0

Concatination of columns of two tables

Dec 26 2009 6:33 AM
I am using VB Express 2008 with MsAccess as my database.  I tried following query to combine the columns of two tables to view in a ListView and it worked for me.

Dim cmdText As String = "Select EDescription, SUM(EQuantity) AS EQuantity, " & _
        "SUM(EAmount) AS EAmount, TDescription,SUM(TQuantity) AS TQuantity, SUM(TAmount) AS TAmount " & _
        "From " & _
        "(" & _
        "SELECT de.Description AS EDescription, de.Quantity AS EQuantity, de.Amount AS EAmount," & _
        "0 AS TDescription, 0 AS TQuantity,0 AS TAmount FROM DailyExpenses de " & _
        "WHERE de.EDate Between @START and @END " & _
        "UNION ALL " & _
        "SELECT 0 AS EDescription, 0 AS EQuantity, 0 AS EAmount, " & _
        "dt.Description AS TDescription,dt.Quantity AS TQuantity,dt.Amount AS TAmount FROM DailyTransaction dt " & _
        "WHERE dt.DTDate Between @START and @END " & _
        ") a " & _
        "GROUP BY EDescription,TDescription"
this query has one problem for me that it is showing data as under:

EDescription EQuantity EAmount TDescription TQuantity TAmount

0 0 0 cocacola 2 6

0 0 0 Sprite 3 12

0 0 0 Pepsi 4 15

Rent 1 1500 0 0 0

Beaf 10 50 0 0 0

Conveyance 1 10 0 0 0

My question is, How I can amend this query to avoid these zeros and to get the result as under:

EDescription EQuantity EAmount TDescription TQuantity TAmount

Rent 1 1500 cocacola 2 6

Beaf 10 50 Sprite 3 12

Conveyance 1 10 Pepsi 4 15

Thanks

Answers (1)