Subtraction of Two Fields

[shafi_sunshine]

Hi Experts,

Im Using Ms-access 2007
I want to subtract Two fields in the query
and my query is

select A.FC,A.RC,A.TotalAmount,A.CansBalance,A.ShopNo,A.A rea,A.SalesType,B.TotalCustomers,C.TotalUndelivere d ,
(CInt(B.TotalCustomers) - CInt([C.TotalUndelivered])) as TotalDelivered from
( (SELECT sum(CardCust_Trans.FC) as FC, sum(CardCust_Trans.RC) as RC, sum(CardCust_Trans.Tot_Amt) as TotalAmount,sum(Balance_Cans) as CansBalance,ShopNo,Area,'CUSTOMERSALES' AS SALESTYPE FROM CardCust_Trans WHERE(CardCust_Trans.Trans_Date >=#12/1/2012# And CardCust_Trans.Trans_Date <= #12/8/2012#) group by ShopNo,Area order by ShopNo ) as A


inner join


(select count(*) as TotalCustomers ,ShopNo,Area from Customer_Master group by ShopNo,Area) as B on A.ShopNo=B.ShopNo and A.Area=B.Area )


left join


(select CInt(IIF(isnull(count(*)),0,count(*))) as TotalUndelivered ,ShopNo,Area from CardCust_Trans where Trans_Date >= #12/1/2012# and Trans_Date<= #12/8/2012# and FC=0 group by ShopNo,Area) as C on A.ShopNo=C.ShopNo and A.Area=C.Area

and the result of abover query is something like this

198	198	792	0	Shop No 8	No	COUTERSALES	3	0	0
145	65	1450	80	Shop No 7	No	FUNCTIONSALES	4	0	0
242	242	1936	409	Shop No 6	Area 2	CUSTOMERSALES	104	31	73

Here im Subtracting column8 - column 9 and i want to get the result in column9
But still im getting 0 values in the last column
it should be 3-0=3 and 4-0=4
and im getting the correct result if there is any number other than 0 in column8 and column9

Any Help Would be greatly appreciated

Thanks
Shafi

[June7]

Want to provide db so can analyze data and test query? Follow instructions at bottom of my post.

[shafi_sunshine]

Hi Experts

Im Attaching my db file please check it once and let me know the solution



Thanks
Shafi

[June7]

There are no queries in that database. I tried copy/paste of the sql from your post and it errored. Provide db with query that works as you showed in post.

[shafi_sunshine]

Hi Expert

I have attached the latest db with Query


Thanks
Shafi

[June7]

Your tables have some design issues. As example, Customer_Master has fields for ShopID, ShopNo, Area, AreaID. ShopID and AreaID should be removed and the primary key fields in Area and ShopNo tables should not be the autonumber ID fields. Those autonumber fields are not useful because the corresponding fields in Customer_Master are text not number type. Related primary and foreign key fields must be the same type. ShopID field in Customer_Master isn't even populated in every record.

ShopNo table has two fields: ShopNo and ShopID
Why have values in ShopNo like: Shop No 10, Shop No 7? Why repeat 'Shop No'? You should be aware that these values will not sort in numerical order. 'Shop No 10 will sort before 'Shop No 7'.

Another example, Functions table has both ShopNo and ShopID. Again, remove ShopID, even though it is a number field, because of previous comment, ShopNo should be pk/fk. Same for tables CardCust_Trans, OneTime_Cust, and ShopExpense.

If you want to use the autonumber field as PK and FK in related tables, need to fix field types and populate field and adjust query joins.


You did not even post the complete query. Turns out it is a UNION and that is not showing in the posted SQL. The records that are not showing the subtraction are all with 'No' in the Area field and these are the OneTime_Cust and Functions data.

Could build the UNION query and save it then use it as the source for further data manipulation. The UNION essentially results in the table you should have to begin with. The transaction tables could really be one.
AllTransactions query:
SELECT ID, Trans_Date, ShopNo, FC, RC, Balance_Cans, Tot_Amt, Amt_Each, 0 As AdvAmt, Amt_Paid, Area, CardID, "CustomerSales" As SalesType FROM CardCust_Trans
UNION SELECT ID, Cust_Date, ShopNo, FC, RC, Balance, Tot_Amt, Amt_Each, Adv_Amt, Amt_Paid, "NO", "NO", "FunctionSales" FROM Functions
UNION SELECT ID, Cust_Date, ShopNo, FC, RC, Balance, Tot_Amt, Amt_Each, Adv_Amt, Amt_Paid, "NO", "NO", "CounterSales" FROM OneTime_Cust;

Why does CardCust_Trans table have Adv_Amt field?

SumTransactions query:
SELECT AllTransactions.ShopNo, AllTransactions.Area, AllTransactions.SalesType, Count(AllTransactions.ID) AS TotalTrans, Sum(IIf([FC]=0,0,1)) AS CountFC, [TotalTrans]-[CountFC] AS TotalUndelivered, Query2.[TotalCustomers] AS TotCardCust, IIf([SalesType]="CustomerSales",[TotCardCust],[TotalTrans])-[TotalUndelivered] AS TotalDelivered, Sum(AllTransactions.FC) AS SumOfFC, Sum(AllTransactions.RC) AS SumOfRC, Sum(AllTransactions.Balance_Cans) AS SumOfBalance_Cans, Sum(AllTransactions.Tot_Amt) AS SumOfTot_Amt, Sum(AllTransactions.Amt_Each) AS SumOfAmt_Each, Sum(AllTransactions.AdvAmt) AS SumOfAdvAmt, Sum(AllTransactions.Amt_Paid) AS SumOfAmt_Paid FROM
(SELECT count(*) AS TotalCustomers, ShopNo, Area FROM Customer_Master GROUP BY ShopNo, Area) As Query2 RIGHT JOIN AllTransactions ON (Query2.Area = AllTransactions.Area) AND (Query2.ShopNo = AllTransactions.ShopNo)
WHERE (((AllTransactions.TransDate) Between #12/1/2012# And #12/8/2012#))
GROUP BY AllTransactions.ShopNo, AllTransactions.Area, AllTransactions.SalesType, Query2.[TotalCustomers];

However, here is your original query all fixed:
SELECT A.FC AS FC, A.RC AS RC, TotalAmount, CansBalance, A.ShopNo, A.Area, SALESTYPE, TotalCustomers, TotalUndelivered, TotalCustomers-TotalUndelivered AS TotalDelivered FROM (SELECT IIf(IsNull(Count(*)),0,Count(*)) AS TotalUndelivered, ShopNo, Area FROM CardCust_Trans WHERE Trans_Date BETWEEN #12/1/2012# AND #12/8/2012# AND FC=0 GROUP BY ShopNo, Area) AS C RIGHT JOIN ((SELECT Count(*) AS TotalCustomers, ShopNo, Area FROM Customer_Master GROUP BY ShopNo, Area) AS B INNER JOIN (SELECT Sum(CardCust_Trans.FC) AS FC, Sum(CardCust_Trans.RC) AS RC, Sum(Tot_Amt) AS TotalAmount, Sum(Balance_Cans) AS CansBalance, ShopNo, Area, 'CUSTOMERSALES' AS SALESTYPE FROM CardCust_Trans WHERE Trans_Date BETWEEN #12/1/2012# AND #12/8/2012# GROUP BY ShopNo, Area) AS A ON (B.Area = A.Area) AND (B.ShopNo = A.ShopNo)) ON (C.Area = A.Area) AND (C.ShopNo = A.ShopNo)
UNION ALL SELECT Sum(FC), Sum(RC), Sum(Tot_Amt), Sum(Balance), ShopNo, 'No', 'COUNTERSALES', Count(*), 0, Count(*)-0 FROM OneTime_Cust WHERE Cust_Date BETWEEN #12/1/2012# AND #12/8/2012# GROUP BY ShopNo
UNION ALL SELECT Sum(FC), Sum(RC), Sum(Tot_Amt), Sum(Balance), ShopNo, 'No', 'FUNCTIONSALES', Count(*), 0, Count(*)-0 FROM Functions WHERE Cust_Date BETWEEN #12/1/2012# AND #12/8/2012# GROUP BY ShopNo
ORDER BY ShopNo;