How to record payment received from Invoice?

[Guest_imported]

I have the following issue: I have an auction database that will tally the winning bidders for each lot, and generate an invoice from a query. What I can't figure out is how to record the amount paid towards the amount due, and record the type of payment (ie cash, check, credit card).

Tables:

Customer: (CustomerNo, FirstName, LastName, Address, City, State, Postal Code, Telephone, TaxExempt, TaxID, RegistrationDate)

LotNo: (LotID, LotNo)

BidDetails: (CustomerNo{from Customer}, LotNo{from LotNo}, BidAmount)

PaymentDetails: (PaymentID, CustomerNo{from Customer}, AmountPaid, Cash, Check, CreditCard)


Query:

Invoice query which contains tables Customer, LotNO, BidDetails. Under BidAmount I have the following:
In (Select Top 1 [BidAmount] From [BidDetails] Where [LotNo] = [LotNo].[LotNo] Order By [BidAmount] Desc)

The problem is when I add the PaymentDetails table to this query, there is not any data shown in the query.

I would like this to be in a form that shows the customer information, the lot numbers purchased, the amount owed and then a subform that allows you to enter the amount paid and payment type. Then a button that you press to print a single invoice with all appropriate data.

I am close to getting this done, I just can't see the logic for recording payment and having it subtract from the amount due on an invoice that is created from a query.

Thanks in advance for your help.

 

[JerryDennison]

I think the problem lies with the link to your payments. Double click the join line for the payments table and select to include all from the customers table and only those matching from the payments table.