Sum of Two Feilds of Two Queries in Seperate Query

[shekar_genius]

I am trying to sum two Feilds each from One Seperate Query, but it is not coming.

1st Query : I have summed the Collection Details:

TotalColl,TotalCr,TotalEx,TotalGA,TotalAd,TotalWHT , Against Each Invoice No & CustomerID

2nd Query : Total of Invoice Amt, TotalOPNWHt, TotalADV,Against Invoice No & CustomerID



IN the 3rd Query I need All the Feilds : Against Invoice No : I.e

CustomerID, Invoice No : TotalColl,TotalCr,TotalEx,TotalGA,TotalAd,TotalWHT ,Total of Invoice Amt, TotalOPNWHt, TotalADV [But I needed to Add :TotalWHT+TotalOPNWHt] =Twht


Kindly Assist.

Thanks,

Shekar

[alansidman]

Try this:

Select [1st Query].*,[2nd Query].*, Sum([1st Query].[TotalWHT]+[2nd Query].[TotalOPNWHt]) As Twht
From [1st Query] Inner Join [2nd Query] on [1st Query].[invoice no] = [2nd query].[invoice no];

[shekar_genius]

Try this:

Select [1st Query].*,[2nd Query].*, Sum([1st Query].[TotalWHT]+[2nd Query].[TotalOPNWHt]) As Twht
From [1st Query] Inner Join [2nd Query] on [1st Query].[invoice no] = [2nd query].[invoice no];

HI,

There seems some problem in it either due to some missing information. I just read the error:-You Tried to Execute a query that does not Include the Specified Expression CustomerID as a part of Aggregate Function.

I Follow through the Queries Again
1st Query named : SumCollected
Fields in it:- CustomerID, InvoiceNo,TotalColl,TotalWht,TotalCr,TotalEx,Total GA

2nd Query Named : TotalInvoice
FeildsInit:- CustomerID,SalesmanID,Salesman,InvoiceNo,InvAmt,Op nWht,Adv

The Code sent by you was adjusted in this fashion
Select [SumCollected].*,[TotalInvoice].*, Sum([SumCollected].[TotalWHT]+[TotalInvoice].[OPNWHt]) As Twht
From [SumCollected] Inner Join [TotalInvoice] on [SumCollected].[invoiceno] = [TotalInvoice].[invoiceno];

I need the final query as : Combination of Both

CustomerID from TotalInvoice, InvoiceNo from TotalInvoice, InvAmt,TotalColl,TotalCr,TotalEx,TotalGA, Twht:Sum([SumCollected].[TotalWHT]+[TotalInvoice].[OPNWHt]),Adv

Thanks

Shekar

[alansidman]

Shekar;
Try this:

Select [SumCollected].*,[TotalInvoice].*, ([SumCollected].[TotalWHT]+[TotalInvoice].[OPNWHt]) As Twht
From [SumCollected] Inner Join [TotalInvoice] on [SumCollected].[invoiceno] = [TotalInvoice].[invoiceno];

Remove the term sum from the Twht field as that creates an aggregate query which is what caused the error message.