How can I get a query to display zero values when no data is enter

[Guest]

I created two table: one for refunds and one for billing adjustments. When
I run a query for each I get no results because some of the clients have
niether or not one or the other. How can I construct a query that returns
values for those clients that have a refund and/or a billing adjustment? I
already made the default value zero, but, of course, that doesn't work.

I orginally created a payment method of billing adjustment and refund. I
then created a query to sum those payments. I had the same problem. My
preference is to use the payment method rather than separate tables. But, in
the final analysis, I will use whichever is easier to get the result I want.

Do I need to enter dummy values in the tables or payment amount fields to
get the queries to work?

I want to break out how the client invoices were paid. I have cash or
charge payments, billing adjustments, refunds as payment options.

Thanks in advance for your help.

 

[Guest]

Hi FLBill0420,

How about a query that returns those with a refund, unioned with a query
that returns those with a billing adjustment... eg:

select CustomerName, Refund as Amount from tblCustomer inner join tblRefund
on tblCustomer.CustomerID = tblRefund.CustomerID

union

select CustomerName, BillingAdjustment as Amount from tblCustomer inner join
tblBillingAdjustment on tblCustomer.CustomerID =
tblBillingAdjustment.CustomerID

That's the seperate table method... I personally prefer the "Payment
Method" version, assuming that Payment Method is the correct term for what
you are trying to achieve... that way you could do this:

select CustomerName, nz(Payment, 0) as Amount from tblCustomer inner join
tblPayment on tblCustomer.CustomerID = tblPayment.CustomerID
where Amount <> 0 and PaymentMethodID in (1, 2)

Assuming 1 and 2 indicate Refund and Billing Adjustment in Payment Method.
You use the nz(FIELD, 0) to check for null and replace with a 0.

Hope that helps.

Damian.