SQL Join Question

[Robert Suffecool]

Hi everyone,

I am trying to reconcile two tables with one another. Each table can
have multiple records for each customer. Table one has records of
outstanding bills. Bills that have been paid are listed in the second
table. I need to figure out a way to determine which customer bills have
not been paid yet. Here is an example of the data in each table...

Here is a sample of the invoice table...
Tran_Date Customer Invoice
15-Nov-03 1 $711.18
15-Nov-03 1 $1,043.15
15-Nov-03 1 $990.01
15-Nov-03 1 $69.53


Here is a sample of the Paid Table...
Date Paid Customer AmtPaid
01-Dec-03 1 $711.18
01-Dec-03 1 $990.01
01-Dec-03 1 $1,043.15


Here is what happens when I create an Inner Join...
AcctNum AmtPaid Invoice
1 $711.18 $69.53
1 $711.18 $711.18
1 $711.18 $990.01
1 $711.18 $1,043.15
1 $990.01 $69.53
1 $990.01 $711.18
1 $990.01 $990.01
1 $990.01 $1,043.15
1 $1,043.15 $69.53
1 $1,043.15 $711.18
1 $1,043.15 $990.01
1 $1,043.15 $1,043.15


As you can see, the only invoice that customer #1 hasn't paid yet is the
one in the amount of $69.53.... How can I create a SQL statement to select
only those records? Any ideas?


Sincerely,


Robert

 

[Wayne Morgan]

To join these, you will need a common ID field between the two so that you
know which payment goes with which billing. You could probably used the
amount for most of them and assume that the date paid was after the date
billed (i.e. no prepayments), but there is no guarantee that the customer
doesn't have two bills with the same amount.

Next, why isn't this one table? You are dealing with a single item here, an
invoice. Adding a Date Paid field to the billing table is probably all that
would have been needed. You could then query the table to get the
information that you want.

 

[Robert Suffecool]

Ah, yes... why isn't this info in one table? That is an excellent
question! When I find the person who created the database and ask him or
her, I'll let everyone else know. The problem that I have now... is there
are over 700,000 records in each table. There is a common ID field
already... it is the customer ID. The problem that I'm having is how to
find the records in the invoices paid table to the invoices table? I've
already created an inner join to join these two tables by the customer ID.
What do I have to do next to find the information I need? Any ideas?

Robert

 

[John Vinson]

I've
already created an inner join to join these two tables by the customer ID.
What do I have to do next to find the information I need? Any ideas?

<puzzlement> Have you created a Query, joining the two tables on that
ID? This Query should bring all of a customer's payments and all of
their charges together.

Figuring out which payment goes with which charge (given that a single
payment might cover several charges, or one charge might be spread out
over multiple payments) may be much more of a challenge however!

 

[Wayne Morgan]

John brought up a good point and may be why the designer used two tables.
The payments may not be one for one (payment vs. invoice). If you join the
two tables together on the customer ID, you'll get all of the bills and
payments. You should then be able to calculate a bottom line balance. If you
assume a first in/first out scenario, then the bills that remain unpaid will
be the latest bills whose total is <= the total amount owed. To do anything
more would require a field in the payment table that states which invoice
the payment was for. To do that, since they may pay more than one invoice at
a time, you would have to have a payment ID field also so that you could
track a single payment across multiple invoices.

If the information mentioned above isn't available, then the bottom line
balance method is probably your best bet.

 

[Guest]

Hi all,

I am working on something similar to Robert but I am only recording the amt
incurred for each customer each month instead of the invoice amt. How can I
get the balance if no payment has been made to a particular month? I am
unable to calculate the balance brought foward from Feb using Joins as the
join on CustID and Month criteria is not met when Month = Feb (refer sample
data below).

tblFeesIncurred
CustID Amt Month
1 $50 Jan
1 $30 Jan
1 $20 Feb
1 $40 Mar

tblPayment
CustID Month Amt
1 Jan $50
1 Mar $40

Thank you!
yann

 

[John]

Try changing your join (by highlighting the join line and clicking
properties), select all records from table one (fees), and only those
records in table 2 (payment) that match. If the payment field is blank then,
set it to 0 in the query.

 

[Guest]

Hi John,

Thanks for your help! I've tried to make the change as you mentioned. But I
am unsure how that can be done.. When i go into the design view of my query,
i couldn't find a join line.

Do i need to run this query in codes? If not I have no idea how can i
determine if the payment field is blank. Please advise.

Your help is very much appreciated!
Thanks!
yann

 

[Wayne Morgan]

There should be a thin black line between the tables in your query to tell
the query how the tables are related. If it's not there, then Access doesn't
know how to compare the records between the two tables.

 

[Guest]

Thanks Wayne! i found it.

Can anyone tell me how I can determine if the payment field is blank?

Thanks!

 

[Wayne Morgan]

When you add the Payment field to the query, make it a calculated field.
Normally, you would add the Payment field to the Field row and its table to
the Table row below it, instead place this in the Field row.

CalculatedPayment:Nz([TableName].[Payment], 0)

Change CalculatedPayment to whatever you want to call the field. The Nz
function will cause 0 to be returned if Payment is Null.