Query Help

[LTOSH]

I have a database table consisting of your basic fields of name, date etc but
have 2 fields with Amt Owed and Balance. I have a second table that has
Payments. I am wanting to produce a report that gives me all cases with a
balance. When I create a query to get the SUM of the Payments and connect it
to the original Table and I try to produce a report all I am getting are the
cases that have made a payment. It will not give me the cases where there
has been no payment.
Can anyone help??????

 

[Lou]

I have a database table consisting of your basic fields of name, date etcbut
have 2 fields with Amt Owed and Balance.  I have a second table that has
Payments.  I am wanting to produce a report that gives me all cases with a
balance.  When I create a query to get the SUM of the Payments and connect it
to the original Table and I try to produce a report all I am getting are the
cases that have made a payment.  It will not give me the cases where there
has been no payment.
Can anyone help??????

When all customers and the sum of their payments ( if any ) are to
appear on the report, the query requires a left join.

For example:

SELECT Customer.Name, Nz( B.SumOfPayments,0 ) as TotalPayments
from Customer
left join ( select CustomerID, sum( PaymentAmt ) as SumOfPayments
from Payments
group by CustomerID ) as B
on Customer.CustomerID = B.CustomerID

However, please consider restructuring the data to eliminate the
column [Amt Owed] and [Balance]. These values should be calculated
from the data.

If you can add a CustomerCharges table to your schema, you could query

SELECT Customer.Name,
Nz( A.SumOfCharges, 0) as TotalCharges,
Nz( B.SumOfPayments,0 ) as TotalPayments
Nz( A.SumOfCharges, 0) - Nz( B.SumOfPayments,0 ) as Balance
from ( Customer
left join ( select CustomerID, sum( ChargeAmt ) as SumOfCharges
from CustomerCharges
group by CustomerID ) as A on Customer.CustomerID =
A.CustomerID )
left join ( select CustomerID, sum( PaymentAmt ) as
SumOfPayments
from Payments
group by CustomerID ) as B on Customer.CustomerID =
B.CustomerID

 

[LTOSH]

If I take out the Amt Owed field, that takes away the field showing the
original amount owed. The balance field in the original table is blank,
thinking i would need it later in a query or whereever.

I'm not sure i understand what you mean by 'left join'. I see your
examples, and do make sense but do i create a query with Customer.Name, Nz(
B.SumOfPayments,0 ) as TotalPayments from Customer?? or is all this
(including 'left join ( select CustomerID, sum( PaymentAmt ) as SumOfPayments
from Payments group by CustomerID ) as B on Customer.CustomerID =
B.CustomerID) in one query?
Guess i don't quite follow all you are saying...I'll have to think a little
more on it and process it.
Thanks Lou!

I have a database table consisting of your basic fields of name, date etc but
have 2 fields with Amt Owed and Balance. I have a second table that has
Payments. I am wanting to produce a report that gives me all cases with a
balance. When I create a query to get the SUM of the Payments and connect it
to the original Table and I try to produce a report all I am getting are the
cases that have made a payment. It will not give me the cases where there
has been no payment.
Can anyone help??????

When all customers and the sum of their payments ( if any ) are to
appear on the report, the query requires a left join.

For example:

SELECT Customer.Name, Nz( B.SumOfPayments,0 ) as TotalPayments
from Customer
left join ( select CustomerID, sum( PaymentAmt ) as SumOfPayments
from Payments
group by CustomerID ) as B
on Customer.CustomerID = B.CustomerID

However, please consider restructuring the data to eliminate the
column [Amt Owed] and [Balance]. These values should be calculated
from the data.

If you can add a CustomerCharges table to your schema, you could query

SELECT Customer.Name,
Nz( A.SumOfCharges, 0) as TotalCharges,
Nz( B.SumOfPayments,0 ) as TotalPayments
Nz( A.SumOfCharges, 0) - Nz( B.SumOfPayments,0 ) as Balance
from ( Customer
left join ( select CustomerID, sum( ChargeAmt ) as SumOfCharges
from CustomerCharges
group by CustomerID ) as A on Customer.CustomerID =
A.CustomerID )
left join ( select CustomerID, sum( PaymentAmt ) as
SumOfPayments
from Payments
group by CustomerID ) as B on Customer.CustomerID =
B.CustomerID