There will be times when a seller may also buy products, but the seller and buyer would have two different id #'s. For instance seller: John Doe sold 3 cars and bought 5 cars:

John Doe seller_id = 05; John Doe buyer_id = 101

Sold: 1997 Ford Escort: $5000

1995 Chev Cavalier: $6000

1998 Buick LeSabre: $10,000

Total:$21,000

Bought: 1985 Mazda B2000: $400

1986 VW Passat: $1000

1995 Ford F250: $2000

1990 Chev Corsica:$2500

1989 Honda Accord:$1500

Total:$7400

Now I would need to be able to add the three items sold together, then add the five items bought together and relate the seller_id to the buyer_id so that I would subtract the two (bought total and sold total) to get the amount that we would still owe him.

example:

seller_id (05)related to buyer_id (101) as same person so that:

$21,000 (sold) - $7400 (bought) = 13,600 (owed)

How would I set this up?

Hi Robbydobbs,

You know from our previous discussions that I would hold both Seller and Buyers within the same db, with the same records, but use a separate db (Acounts) for this job.

I post this here just to know if others would disagree with this setting....

Why have separate fields for buyer_id and seller_id -- just have one ID.

Have one file be a list of people ("People") -- including name and ID.

Have one related file where each record is one sale ("Transctions"). Create two relationships from People -- one based on ID::BuyerID, and one for ID::SellerID.

Back in Transactions, put the buyer's ID into "BuyerID" and the seller's ID into "SellerID" -- in fact, I'd use a value list pulling info from the People file. Actually, I'd use a value list of names to enter the names of the buyer & seller, and have the "ID" fields be calc fields, returning the related IDs.

Now enter the transactions in the Transactions file. You can view the appropriate transactions in the People file. Create a field that totals the prices for items bought, and another for prices of items sold. Now create a calc field that subtracts one from the other, and voila.

Et voil