Relationships

[tom droz]

I am working on a Auto Dealer software. They have customers that are both buyers and sellers.

I created one contact table to hold the buyer/seller/vendor contact information.

I created fields in the main Vehicle table to hokd Buyer ID and Seller ID from that contact table.

All good so far.

Now I want a list of all buyers or all sellers.

My list has the buyers and or sellers BUT if they bought or sold multiple times then the list shows them multiple time (ie Buyer "A" sold us 3 cars, he shows up 3 times)

Any idea how to get the list to only show that buyer once?

I tried multiple layouts with multiple relationships and none seem to produce that.

Seems simple, am I missing something obvious?

Any help would be appreciated!

Thanks!

[comment]

Would it be correct to assume that for each transaction there is either a buyer OR a seller - never both?

[tom droz]

Thanks for your question.

Every deal has information on both.

Who you acquired the car from, who you sold the car too.

Example:

Car 1, Acquired from Customer A, Sold to Customer B

Car 2, Acquired from Customer C, Sold to Customer D

Car 3, Acquired from Customer A, Sold to Customer E

In the above, if I want all my Acquired from customers, I am ending up with A,C,A but want to have output of A, C

I thought having a layout based on the relationship of

Autos then Customers with the link being Autos 'Acquired ID' equals 'Customer ID' would do it since Customer ID only has Buyer A once, but it produces A,C,A....

;)

[comment]

Perhaps you should consider changing your structure to something like:

Vehicles -< Transactions >- Contacts

where each transaction has a type. With your current structure, there's no way you can produce a report of customers by type*. You can, however, produce a report of sellers only AND a report of buyers only. For example, assuming you have these relationships in place:

Seller -< Vehicles >- Buyer

VehiclesSold >- Contacts -< VehiclesBought

you could go to the Contacts layout and perform a find for * in the VehiclesSold::ContactID field. That would give you a list of all contacts that have ever sold a vehicle.

---

(*) BTW, this is not the only limitation of your structure. You also cannot produce a report like this:

March 2010

• Bought: 35

• Sold: 27

April 2010

• Bought: 15

• Sold: 31

[tom droz]

Thank you for your thoughtful reply!

I current can produce a list of all the people we 'acquired' a car from. The problem is I only want each name to show up once.

Example: If I acquired 5 cars from customer 'A', and now want to send a letter to all people I acquired a car from, I only want customer 'A' to show up once. In your model I believe I will still get the results I am currently getting.

My current structure is;

Car:

ID

AcquiredFrom ID

SoldTo ID

ContactAcquired From: (TO of Contacts)

ID

ContactSoldTo: (TO of Contacts)

ID

Where Car is the anchor and Contact is the buoy

and AcquiredFrom ID and SoldTo ID are from the customer ID

Any ideas are welcome!

Thanks

Edited April 30, 2010 by Guest

[comment]

The problem is I only want each name to show up once.

And the solution is to produce the report from the Contacts table, where (hopefully) each contact has only a single record.

Don't you have another TOG where Contacts is the anchor? If not, you can find vehicles where SellerID is not empty, then do GTRR [ Show related only; Match found set; from ContactAcquiredFrom ] to produce a unique list of sellers.

[tom droz]

Thank you so much!

I think I have a working concept, I really appreciate it!!