three tables relationship

[Jimmy]

Hi

I have a three tables in the Access 2002

Tabel A

Invoice number
Date
Customer

Table B-Receipt

Invoice number
Paid Date
Customer

Table C-Receipt(2006)

Invoice number
Paid Date
Customer

I tried to create One query based on the Table A's Invoice number to match
with Table B and Table C in order to find out the Invoice of Table A (is it
paid and then when)

I cannot do it,

Please help

Thanks
Jimmy

 

[BruceM]

You will need to explain the intended purpose of the three tables. The
typical structure if there are customers and invoices is that each customer
may have many invoices (and each invoice is associated with one customer).
Therefore there is a one-to-many relationship between the Customer and
Invoice tables.

tblCustomer
CustomerID (Primary key)
Customer Name, etc.

tblInvoice
InvoiceID (Primary key)
CustomerID (Foreign key)
InvoiceDate
PaidDate
any other fields specific to the invoice

There is a one-to-many relationship between the CustomerID fields. For data
entry the interface is typically a form/subform based on
tblCustomer/tblInvoice. If you need to look at invoices from a particular
date range you would use a query, not a separate table for each year.

Another typical situation with Customers and Invoices is that each invoice
may contain a number of items, so there is an InvoiceDetail table related
one-to-many form the Invoice table:

tblInvoiceDetails
DetailID (Primary key)
InvoiceID (Foreign key)
InvoiceItem
UnitPrice
etc.

 

[JK]

You will need 3 or 4 tables - Customers, Invoices, Receipts (? Invoice
Detials) - The Receipts table is required as there may be more than one
payment on an invoice or invoice is not be paid in full (or overpaid)

tblCustomers
CustomerID (Key)
name, address etc

tblInvoices
invoiceID (key)
CustomerID (foreign)
InvAmount (double)
InvBalance (double) not a must but recommended
InvDate, invNumber etc

(if you have invoice detail, as distinct from entering invoiced amounts from
a manual system then you will need tbleInvoiveDetails on line with Bruce
suggestion)

tblReceipts
ReceiptID (Key)
InvoiceID (foreign)
ReceiptAmount (Double)
RcptDate, RcptNo, PayRef etc.

I agree with Bruce, no need for different tblReceipts for each year - (a
receipts for one Invoice, if more than one receipt can be made in two years,
say, half in December and balance in the following January.

Relations:

tblCustomer to tblInvoice on CustomerID (one-to-many)
tblInvoice to tblReceipts on Invoice_ID (ditto)
(tblInvoice to tblInvoiceDetails on InvoiceID) (ditto)


Start from here and see how you go

Regards/JK