What am I doing wrong

[ericB]

I have an invoice table that I enter all billable sale for each of my
customers. I have a second table that I enter all my customers payments in.
There may be times when I have an unequal amount of payments or bills
outstanding. For this reason I want to make a balance sheet for each client
that keeps a running total for them. I have tried to do this with a query
with the 2 tables linked but because of the unequal entries for each the
query repeats the receivables for each billing entry. How can I fix this? Is
there a way to do a balance sheet?

 

[Allen Browne]

A UNION query will let you merge the records from two tables into one
result.

You have to enter this in SQL View of a query (View menu), but it will be
something like this:

SELECT Sale.ID, Sale.SaleDate, Sale.Amount, "Sale" AS TransactionType
FROM Sale
UNION
SELECT Payt.ID, Payt.BillDate, - Payt.Amount, "Payt" AS TransactionType
FROM Bill
ORDER BY SaleDate, ID;