ACCESS FEED: Re: [MS_AccessPros] Report to not show Zero balances

Khalid-

Try this:

SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, Clients.ClientCity, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID])-DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID]) AS Balance

FROM Clients INNER JOIN [Clients Transaction] ON Clients.ClientCIN = [Clients Transaction].ClientCIN

WHERE ((([Clients Transaction].TransactionDate) Between #1/1/2012# And Now()))

AND Clients.ClientCIN IN (SELECT ClientCIN FROM Clients As C2

WHERE DSum("AmountReceived","[Clients Transaction]","ClientCIN = " & C2.[ClientCIN]) - DSum("Expenses","[Clients Transaction]","ClientCIN =" & C2.[ClientCIN]) <> 0)

I changed it to work more efficiently using IN. It should select only the client records where the balance is NOT zero.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 21, 2016, at 5:50 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Thanks for replying, Yes in other words i want to see NO transactions from any cllient who has a zero balance.

But i am sorry i could not add it at proper place what you gave me to add to my WHERE clause:

My present sql is (i have removed checks ClientCity Null or Not Null):

FROM Clients INNER JOIN [Clients Transaction] ON Clients.ClientCIN = [Clients Transaction].ClientCIN

WHERE ((([Clients Transaction].TransactionDate) Between #1/1/2012# And Now()));

-----------------------------------------

And you gave the following to add to my WHERE clause:

And ClientCIN NOT IN (SELECT ClientCIN FROM Clients As C2

WHERE DSum("AmountReceived","[Clients Transaction]","ClientCIN = " & C2.[ClientCIN]) - DSum("Expenses","[Clients Transaction]","ClientCIN =" & C2.[ClientCIN]) <> 0)

Please help.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

In other words, you want to see NO transactions from any client who has a zero balance. Instead of what I gave you earlier, add this to your WHERE clause:

And ClientCIN NOT IN (SELECT ClientCIN FROM Clients As C2

WHERE DSum("AmountReceived","[Clients Transaction]","ClientCIN = " & C2.[ClientCIN]) - DSum("Expenses","[Clients Transaction]","ClientCIN =" & C2.[ClientCIN]) <> 0)

That may run really slowly because the DSums will run for each row.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 15, 2016, at 11:24 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Sorry that i did not mentioned other controls on the report my apologies.

On Report Header I have put following controls Set Visible to "No":
TransactionID-->Control Source-->TransactionID
TransactionDate-->Control Source-->TransactionDate
AmountReceived-->Control Source-->AmountReceived
Expenses-->Control Source-->Expenses
Balance-->Control Source--> =Nz([AmountReceived],0)-Nz([Expenses],0)
Text69-->Control Source--> =Sum([AmountReceived])
Text70-->Control Source--> =Sum([Expenses])
Text71-->Control Source--> =([Text69])-([Text70])
At report Footer GrandTot--> Control Source--> =([Text71]) ===== Visible=True
--------------------
On ClientCIN Header I have following controls:
ClientCIN-->Control Source-->ClientCIN
ClientName-->Control Source-->ClientName
----------------------------------------------------
AND THE CONTROLS YOU ASKED FOR: (Set Visible to "No")
TotAmountReceived-->Control Source--> =Nz(Sum([AmountReceived]),0)
ToTExpenses-->Control Source--> =Nz(Sum([Expenses]),0)
----------------------------------------------------
ClientBalance-->Control Source--> =Nz([TotAmountReceived]-[TotExpenses])
---------------------
On ClientCIN Footer i have controls Set Visible to "No":
Text83-->Control Source--> =Sum([AmountReceived])
Text84-->Control Source--> =Sum([Expenses])
Text85-->Control Source--> =([Text83])-([Text84])

CitySubTotal--Control Source--> =([Text85]) ========== Visible=True

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

How are you calculating TotAmountReceived and TotExpenses? Those fields are not in your query.

John

Sent from my iPad

On Feb 14, 2016, at 11:45, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I'm still getting Zero balance rows.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Add this to your Where clause:

And DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID])-DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID]) <> 0

John

Sent from my iPad

On Feb 13, 2016, at 09:13, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

In Sorting and Grouping of ReportI have:
ClientCity
ClientCIN
Both having Group Header and Group Footer set to 'Yes'
On ClientCity Header I have Text box 'ClientCity'
And on ClientCIN Header I have Controls:
ClientCIN, ClientName, ClientBalance

ClientBalance--> Control Source--> =Nz([TotAmountReceived]-[TotExpenses])

Now as you asked how can I test that TotAmountReceived minus TotExpenses is not zero ?

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Your SQL makes no sense. You are limiting the output rows to those between January 1, 2012 and the current date, but you're including all transactions from Client Transactions and Expenses in your DSum expressions. And this part of your WHERE clause is confusing:

AND ((Clients.ClientCity) Is Null)) OR ((Not (Clients.ClientCity) Is Null));

Why test ClientCity at all if you're including both rows that are Null and rows that are not?

If you want to eliminate all zero balance rows, then you need to test that TotAmountReceived minus TotExpenses is not zero.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 11, 2016, at 8:53 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi All,

I have a report with following:
Record Source --> AllClientsBalanceSheetTodayQuery
SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, Clients.ClientCity, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID])-DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID]) AS Balance
FROM Clients INNER JOIN [Clients Transaction] ON Clients.ClientCIN = [Clients Transaction].ClientCIN
WHERE ((([Clients Transaction].TransactionDate) Between #1/1/2012# And Now()) AND ((Clients.ClientCity) Is Null)) OR ((Not (Clients.ClientCity) Is Null));
----------
ClientBalance--> Control Source
=Nz([TotAmountReceived]-[TotExpenses])

I need that report should not display "ClientBalance" having Zero balance for any row, there are presently most of the balances in (-) minus figure which are displaying and i need them to display after any changes we make to not display ZERO balances of any ClientCIN.

Help required please.

Regards,
Khalid