UNION ALL NOT SHOWING QUERY RESULTS.??

[PinoyDev]

Good day!

I have problems running my union all query..It only executes the first select statement but the 2nd and 3rd union was not executed. My backend DB is MSAccess 2003..

If rs_dailySales.State = adStateOpen Then rs_dailySales.Close

SQL = "SELECT SUM(NetAmount) AS totalCashSales FROM InvoiceMain WHERE InvoiceType='Cash' AND DateOInvoice=DateValue('" & filterdate & "')" & _
" UNION ALL SELECT SUM(NetAmount) AS totalCreditSales FROM InvoiceMain WHERE InvoiceType='Credit' AND DateOInvoice=DateValue('" & filterdate & "')" & _
" UNION ALL SELECT SUM(NetAmount) AS totalDriverSales FROM InvoiceMain WHERE InvoiceType='Driver' AND DateOInvoice=DateValue('" & filterdate & "')"
rs_dailySales.Open SQL, conn, adOpenStatic, adLockOptimistic, adCmdText

I do not know where i miss something..?

Thank you for helping...

[pritaeas]

The SELECT clauses must be the same, you can't name the columns differently.

[PinoyDev]

Thank you pritaeas!

But how can I get the value of totalCashSales ,totalCreditSales , totalDriverSales?

[pritaeas]

If you use the union the 1st returned record will be totalCashSales, 2nd totalCreditSales, 3rd totalDriverSales.

Another way would be to use sub-selects, kinda like this:

SELECT 
    SUM(SELECT NetAmount FROM InvoiceMain WHERE InvoiceType='Cash') AS CashSales,
    SUM(SELECT NetAmount FROM InvoiceMain WHERE InvoiceType='Credit') AS CreditSales,
    SUM(SELECT NetAmount FROM InvoiceMain WHERE InvoiceType='Driver') AS DriverSales
FROM InvoiceMain
WHERE DateOInvoice=DateValue('" & filterdate & "')"

Untested, but may get you started.

[PinoyDev]

Thank you pritaeas!

Ive modified the query below but I get this error "At most one record can be returned by this subquery"

SQL = "SELECT" & _
    " SUM(SELECT NetAmount FROM InvoiceMain WHERE InvoiceType='Cash') AS CashSales," & _
    " SUM(SELECT NetAmount FROM InvoiceMain WHERE InvoiceType='Credit') AS CreditSales," & _
    " SUM(SELECT NetAmount FROM InvoiceMain WHERE InvoiceType='Driver') AS DriverSales" & _
    " FROM InvoiceMain" & _
    " WHERE DateOInvoice=DateValue('" & filterdate & "')"

rs_dailySales.Open SQL, conn, adOpenStatic, adLockOptimistic, adCmdText

[pritaeas]

Can't test right now. Use the other query and get the results in order.

[PinoyDev]

anybody has a better working solution?

[PinoyDev]

I got it working. Please see and comment below code. Can I add additional subquery to it to perform additional calculation? Im worry about performance issue if I added more subquery. But at least 6 total subquery I want to add to it..

SQL = "SELECT" & _
" (SELECT SUM(NetAmount) FROM InvoiceMain AS A WHERE InvoiceType='Cash' AND DateOInvoice=DateValue('" & dt1.Value & "')) AS CashSales," & _
" (SELECT SUM(NetAmount) FROM InvoiceMain AS B WHERE InvoiceType='Credit' AND DateOInvoice=DateValue('" & dt1.Value & "')) AS CreditSales," & _
" (SELECT SUM(NetAmount) FROM InvoiceMain AS C WHERE InvoiceType='Driver' AND DateOInvoice=DateValue('" & dt1.Value & "')) AS DriverSales," & _
" (CashSales + CreditSales + DriverSales) AS TotalSales FROM InvoiceMain"

Thank you!

[pritaeas]

Try it first, worry about performance later. You can perhaps set an index on DateOInvoice, and on InvoiceType.