Two-to-one relationship basic query (novice alert)

[JayEmm]

Hi All,

I've just got back into filemaker after many, many, many years away from databases (the last time was at school) and everything's going fairly swimmingly but one thing's tripping me up.

I'll try and explain what I'm doing so hopefully the solution will be quite obvious.

I'm doing a database for a car business, that sells only used cars. Typically, someone will sell us or part exchange a car, we hand them a bit of paper and then we sell it to someone else.

I've got two tables, one for cars (each with a unique ID) and one with customers (each with a unique ID).

Now, currently I have a one-to-one link as in the car's table is a field for customer ID - which ideally should be the current/new owner of the car.

I have a sheet we give to people when we take their OLD car, which has all of their details on - linked by their customer ID.

However, when we sell the car to someone else I have to change this customer ID to reflect the new owner.

How do I create TWO customer ID relationships within the cars recorrd - one for OLD owner and one for NEW owner and when I'm creating the forms (part-ex/sale agreement) how do I get filemaker to bring up the correct customer's details?

Many many thanks in advance for all your help!

I'm sure it's a simple answer but I want to do this properly!

James

[comment]

How do I create TWO customer ID relationships within the cars recorrd

Instead of one CustomerID field in the car record, you should have two: a SellerID and a BuyerID. Then use two occurrences of the Customers table to link one to each field.

Another option is to use three tables: Cars, Customers and Transactions.