Remove Redundancy in joined tables

[AccBeginner]

I have two tables the first one store invoice details and the other payments.
Now the tables are as follows:

tblInvoice
InvoiceNo
Date
customerID
LPO No
ProductNo
Qty
Amount
etc
and tblpayments
PayNo
CustomerID
DatePaid
AmountPaid
etc

Now i needed a query to give me history of Invoices vs Payments. i joined
the two tables by the CUSTOMERID i got multiple enteries.

thanks

 

[Rick Brandt]

I have two tables the first one store invoice details and the other
payments. Now the tables are as follows:

tblInvoice
InvoiceNo
Date
customerID
LPO No
ProductNo
Qty
Amount
etc
and tblpayments
PayNo
CustomerID
DatePaid
AmountPaid
etc

Now i needed a query to give me history of Invoices vs Payments. i
joined the two tables by the CUSTOMERID i got multiple enteries.

thanks

Pretty sure for what you want your payment table also needs a foreign key to the
InvoiceNo.
As it is now you have no idea what invoice a payment is for, only which customer
it is for.

 

[Baz]

It depends what you mean by "history of Invoices vs Payments". If you mean
side-by-side lists of invoices and payments for a customer then you can't do
that with a single query, you'd need to build a form or a report. However,
if you mean a list of payments for each invoice, then you need the invoice
no. as a foreign key on the payments table so you can join on that (but the
invoice details will be repeated against each related payment record).

But, I suspect that what you really want is a report.

 

[AccBeginner]

customers make payments not against individual invoices but total amount
OUTSTANDING. both tblInvoice and tblpayments have customerIDs. what i wish to
have is something like this

CustomerID Date InvoiceNo ProductNo Qty Amount DatePaid
PaidAmount Balance

Cust001 12/04/2007 1002 Prod001 3 4000 -
4000
Cust001 12/06/2007 1005 Prod001 4 3000 -
7000
cust001
12/07/2007 5000 2000

thatz what am looking for. Does it make Sense?

Life is short while alive Enjoy!

 

[Rick Brandt]

customers make payments not against individual invoices but total
amount OUTSTANDING. both tblInvoice and tblpayments have customerIDs.
what i wish to have is something like this

CustomerID Date InvoiceNo ProductNo Qty Amount DatePaid
PaidAmount Balance

Cust001 12/04/2007 1002 Prod001 3 4000
- 4000
Cust001 12/06/2007 1005 Prod001 4 3000
- 7000
cust001
12/07/2007 5000 2000

thatz what am looking for. Does it make Sense?

Life is short while alive Enjoy!

You cannot produce that output with the tables you have. That output requires
that a payment can be matched to an invoice and even to a product. Unless all
of your tables have fields that you can join on those fields then you cannot
produce that output.

You might be able to produce that sort of output to determine what they were
*charged* because I assume your invoice table has all of that, but as for
payments all you can do is show per-customer what total amount they were charged
and what they have paid so far. You cannot link those payments to invoices and
products unless your payment table includes those fields.

 

[AccBeginner]

Thanks Rick i gues i need to go back to the drawing board again!

 

[Ken Sheridan]

I think it might be possible to get close to what you want provided you
return the invoice dates and payment dates in a single column:

SELECT CustomerID, [Date], InvoiceNo, ProductNo,
Qty, Amount, NULL AS PaidAmount,
(SELECT SUM(Amount)
FROM tblInvoice As 12
WHERE I2.CustomerID = I1.CustomerID
AND I2.[Date] <= I1.[Date])
-(SELECT SUM(AmountPaid)
FROM tblPayments
WHERE tblPayments.CustomerID = I1.CustomerID
AND tblPayments.DatePaid < I1.[Date])
AS Balance, 0 AS SortColumn
FROM tblInvoice AS I1
UNION ALL
SELECT CustomerID, DatePaid, NULL, NULL, NULL,
NULL, PaidAmount,
(SELECT SUM(Amount)
FROM tblInvoice
WHERE tblInvoice.CustomerID = P1.CustomerID
AND tblInvoice.[Date] <= P1.DatePaid)
-(SELECT SUM(AmountPaid)
FROM tblPayments AS P2
WHERE P2.CustomerID = P1.CustomerID
AND P2.DatePaid <= P1.DatePaid), 1
FROM tblPayments AS P1
ORDER BY CustomerID, [Date], SortColumn;

The UNION ALL operation tacks the results of the two constituent queries
together, with Nulls being inserted at the column positions in each which are
only represented by columns in the other's table. The two subqueries in the
first part sum the invoice amounts for the current customer less the sum of
the payment amounts for the cutomer before the current date. The two
subqueries in the second part sum the invoice amounts for the current
customer less the sum of the payment amounts for the customer up to the
current date. These should give the balances for the invoice and payment rows
respectively. The SortColumn, by means of the constants 0 and 1, should
ensure that any payment by a customer on the same day as an invoice for that
customer comes after the invoice in the result table.

Ken Sheridan
Stafford, England