Relation between two tables

[Kushtrim]

Hi everyone,
I have two tables and I want to relate them, in one-to-many relationship.
The structure of those two tables is:

AccountReceiveable:
-ID=AutNumber
-Client=Text
-IDNumber=Number (long integer)
-Amount=currency
...

Payment:
-ID=AutoNumber
-Client=Text
-IDNumber=Number (long integer)
-Amount=Currency
...

What I need is help on how to relate these two tables, for example wich fields to be related, and if you have any suggestions for field DataType.

My goal is to have two tables in which I write the Debts that clients own me (the company) and the other table to write Payments when clients pay, then through the query to have the balance for each client.

If you have any idea how this can be done, please help me. I really need this help.


Thanks a lot.

 

[Scooterbug]

What you want to do is to store the Primary Key field from the AccountsReceivable table in which the client is making a payment with the record that will store the payment information in the Payment table. So it will look something like this:

tblAccountReceivable
AccountID - Primary Key, Autonumber
ClientID - Foreign Key (I am assuming that you have a separate table for Clients.)
Amount
Any other Relavent data

tblPayments
PaymentID (Primary Key)
AccountID (Long Number, Foreign Key)
Other Payment Data

You do not need to store the Client Info in the payment table. Since the Client Info is stored in the Account Receivable table and the Payments store the AccountID, you dont need to store the clientID twice.