Problem with query expression

[pikou]

Hello all,
I have ran into a bit of a problem with a database of mine that is for an architectural firm.I have 3 tables in total.They all have a relationship between them and the main table is ERGO.This table has general info about the work that the company has undertaken on behalf of the client as well as the total price.Now a house can have two predetermined prices one for the study of it and another for the supervision.These two pieces of info are divided into two fields in the main table.The other two tables have info on the various deposits the client makes for either the "study" or the "supervision" or both.
What i want to achieve is to be able to print in a report the balances for all the clients that have (balance > 0) either for study or supervision.I have tried to make an expression in a query and use that in the report but i get an error when trying to run the query.On the other hand when I tried to do the calculation in the report itself the calculation is computes just fine but I have instead of one "output per house" I have multiple outputs as many as the deposits of the client.
I have included a copy of the tables , if someone has any clue plz help me out

 

[Jon K]

See the queries in the attachment.

qryFinancial is based off ERGO table, qryStudy and qrySupervising.

qryFinancial_NotBalance is based off qryFinancial and is for the report.
.

 

[pikou]

Thank you very much for your prompt reply this is exactly what I needed.I wanted to ask a couple of things though if you can spare some time.
On this expression (StudyPriceBalance: Nz([ERGO].[STUDY PRICE])-Nz(StudyDeposits)) what does the (Nz) represent ?I checked the MSDN site and the clarification of the Nz function but if you could specify a bit more it would be great.Also one other thing.In query qryStudy and qrySupervising you have in the criteria (<>0) , what exactly does this mean ?
Thank you for all your help

 

[Jon K]

When a field is empty, its value is Null and when Null operates on anything, the result is Null. E.g. if you leave out the Nz() in:

SupervisionPriceBalance: Nz([ERGO].[SUPERVISION PRICE])-Nz(SupervisionDeposits)

when [SUPERVISION PRICE] or SupervisionDeposits is Null, SupervisionPriceBalance will be Null.


The Nz() function converts a Null value to 0 or to any text string that you want Access to return when a value is Null.

See Post #4 of this thread for ways of using Nz():
putting 0 where field is empty
http://www.access-programmers.co.uk/forums/showthread.php?t=100101


<>0 means "not equal to zero".
.