Solved Nz in queries and forms

[Eric the Viking]

I hope you can help. I have a small database I am using to track ledger debits and receipts for members of my angling club (110 members). Some members have bar spending some do not some have made payments for this some have not. The structure is a members details table with separate linked (by members unique key) tables for debits and receipts. My problem is in having a continuous form that shows total debits, total receipts and what is outstanding (a calculated field). It works fine for members who have debits and receipts but the problem comes with members who have only debits and no receipts as the queries don't return a result if a member hasn't a receipt as yet. I have tried using Nz on the totals but this does not return "0" for members who have no debits or receipts. I have looked at the join properties but anything other than option 1 causes Access to say there is an ambiguous join. Where am I going wrong? Cheers

 

[theDBguy]

Hi. If you're using a query, you might need to use an OUTER JOIN too (if you're not already).

 

[plog]

I think you set up your table incorrectly. Credits and debits shouldn't be in different fields, they should all be together. Then when you want a balance you run a simple aggregate query to add that one field up.

 

[The_Doc_Man]

Darn, theDBguy beat me to it.

First, if you have individual debit and payment transactions, you can write queries grouped by the person's ID. You didn't show us detailed table structure, so it would be possible for me to give you bad advice here. In general, NZ works fine in queries but if you don't have the right kind of JOIN you will never get to USE the NZ.

If you have a list of members OUTER JOINED (written as LEFT JOIN, probably), you can join the member list to the debit and payment lists, then form the sums as a second layer of a query. But here is my question: Do your debit and receipt tables exist separately or as a merged table with a code for debit or credit?

 

[Eric the Viking]

Thank you all for your replies you have pointed me in the correct direction, my failure was in not having a first and second layer in my queries, and then having the wrong join types to boot. My final query is now returning correctly using the Nz function. Thanks again.

 

[Eric the Viking]

I think you set up your table incorrectly. Credits and debits shouldn't be in different fields, they should all be together. Then when you want a balance you run a simple aggregate query to add that one field up.

Hi thanks for your response but in accounting terms I need the debits and receipts separate as they are different ledger accounts.

 

[The_Doc_Man]

Then for summation purposes, if you have not already done so, may I suggest that you look into UNION queries that would let you make a single query out of two tables with similarities? This would be a layer that lets you do something like:

SELECT AccountID, ReceiptAmount as Amount, RecieptDate as XDate FROM Receipts
UNION ALL
SELECT AccountID, -DebitAmount  as Amount, DebitDate as XDate FROM Debigs ;

With this as the lowest layer, you could then have a higher layer that did sums, sorting, etc. very easily on transaction history as a single entity yet the data would stay separate in the raw tables. If you are already doing something like this, then great, you are in the right direction. If not, read up on UNION queries. They are great for dynamically merging related but separate things.

 

[Minty]

Hi thanks for your response but in accounting terms I need the debits and receipts separate as they are different ledger accounts.

You could simply store the allocation account information as well as the amount then your sums could work in one query?

 

[plog]

Hi thanks for your response but in accounting terms I need the debits and receipts separate as they are different ledger accounts.

Incorrect. You really should read up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

.

Storing data improperly will cause you to have more issues like this that need to be hacked around.

 

[The_Doc_Man]

It should be noted that my UNION ALL solution is only if you really have a legal requirement to keep things separately. If you don't have legal issues that dictate your structure, then please look at normalization that would allow you to keep things in one table, which would eliminate the need for the UNION query. I answered your direct question. plog answered the overall question of structure. Neither of us is wrong.