Append Some of a table to another?

[jdbit2byte]

I have two tables tbl_Invoices and tbl_PaymentTotals:

tbl_Invoices includes
InvoiceNum
InvoiceAmount

tbl_PaymentTotals includes
InvoiceNum
TotalPayments

I want to add TotalPayments to tbl_Invoices but only if InvoiceNum matches
in both records.
Here is an example of what I want to end up with

InvoiceNum/////InvoiceAmount/////TotalPayments
123432123/////$10342.67 /////$8000.00
987678900/////$1234.56 /////
456776544/////$4567.54 /////$4567.54

This will allow me to accomplish three things with further queries.
1. The invoice has "X" paid and "X" more to pay. (First Examle)
2. The invoice has $0 paid so far and is put on high priority (Second
Example)
This one is the toughest for me because in tbl_PaymentTotals invoice
987678900 would have no record (since there were no payments made so far).
3. The invoice has been paid off. (Third Example)

I have been messing with append, update, innerjoins, outterjoins, and union
queries but can't seem to find a way to get a list of payments made in the
invoice table. I think the main issue is that there is not always a record
in payments for each invoice since the first payment may not have been
recieved yet. So therefore tbl_Invoices has more records than
tbl_PaymentTotals.
Note--- In both tables InvoiceNum is set to Text and Currency Fields set to
Currency.

This one is really rackin my brain any help would be awesome.

JD

 

[Steve]

You are digging a hole that may be very hard to get out of later. You need
to scrap Tbl_PaymentTotals. You need just a payments table that looks like:
TblPayment
PaymentID
InvoiceNum
PaymentDate
PaymentAmount

Use a Totals query to return total payments for each invoice. Base the query
on TblPayment. In design view, click on the Sigma (looks like a capital E)
button in the menu at the top of the screen. UnderPaymentAmount change Group
By to Sum.

Now you can create other queries that includes Tbl_Invoices and the above
Totals Query joined on InvoiceNum to accomplish the three things you want.

Another observation ----
Your invoices table may also be incorrect. If you are invoicing multiple
items on any invoice, the table is incorrect. You would need:
TblInvoice
InvoiceID
CustomerID or SupplierID that you are invoicing
InvoiceDate

TblInvoiceDetail
InvoiceDetailID
InvoiceID
ItemID
Quantity
ItemPrice

You would get InvoiceAmount by summing Quantity x ItemPrice.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[jdbit2byte]

I forgot to mention that the data is coming from another database through an
ODBC. I am in complete agreement that the design is poor, but I do not have
control of this.