Include Results with Null or Zero

[emarkman10]

I am trying to put together a query to track customer receivables. The relevant data is in two tables: tblData and tblCash. tblData has customer info, tblCash has payment records. Each of the tables is organized by a customer number. I need a simple query to tell me the following:



The sum of deposits, organized by customer number, and I want to input the cutoff deposit date. However, I want the results to include policies where no payment has been made. For example, if Customer XYZ placed an order on 3/1 for $50, and payment is due by 3/31, and a payment is either not made, or not made by the input date, I want the query to show a deposit of $0, rather than leaving the customer off of the results. In many cases, tblData may have the order info, but because no payment has been made, tblCash will have nothing for that customer number.

[Robeen]

Do this:
1. Create a New Query & add tblData & tblCash - in that order.
2. Click and drag the CustomerNumber from tblData to the CustomerNumber in tblCash to create a join between those fields in the two tables.
3. Right-Click on the join line between the two tables in your query design -> Click Join Properties and select 2. Include all records from . . ..
4. If you did that right - the join line between the two tables will have an arrow pointing to tblCash.
5. Select CustomerID [and CustomerName ?] from tblData - and the Deposits field from tblCash.
6. Run the Query - you should see ALL CustomerIDs [and Names if you added that field] from tblData - whether they have made any payments or not -> and the Deposits [if any]. If a customer doesn't have a deposit - you'll see a 'blank' in the Deposits field for that customer.

Now - as far as getting the 'sum of deposits' - in the query design window - click the 'Totals' button [top of screen - just right of center] and down where your fields are - you will see a new row with 'Total:' on the left [where 'Field:' . . . Table:' etc are]. You will select 'Group By' on the CustomerID & CustomerName - and 'Sum' for the Deposits.

I hope this helps!

[emarkman10]

Thank you!