When a supplier is also a customer..... (1 Viewer)

[getssmart]

Hi

In a hyperthetical situation if Fred and Jim are both a customer and a supplier.
How would I structure the relationships/joints so that I only keep 1 table whether they be suppliers or customers, but on a form I want to say I have bought items off Fred and sold them to Jim.

When using the form wizzard, once I have selected the 'Name' field from the cust/suppliers table (for the bought from) that field dissapears from the dropdown box in the wizzard, so how do I then select it again for the (sold to).

Hope this makes sense....

The lower tech the better solution for me as I am still finding my feet with Access, but loving the experience.

Thanks

 

[KenHigg]

How about this; If you're only talking a rare occasion of this happening, put them in the business and client tables.

 

[getssmart]

No it would be in almost every case, I had to simplfy the scenario and make it hyperthetical.

But your response is appreciated, thanks.

 

[gemma-the-husky]

I posted this earlier today on another thread- i still think its relevant


dont confuse customers and suppliers. They are not really the same, and very few accounting systems would expect them to be.

customers are linked to accounts receivable/debtors/sales orders
suppliers are linked to accounts payable/creditors/purchase orders

both will have distinct identifiers


if they happen to be the same legal entity, its coincidental

Just because an employee happened to be a customer you wouldnt expect to link his employee address to a customer table, would you?

If you really need to manage linked customers/suppliers use a separate table, as generally there would be very few such relationships in any normal situation.

 

[JoeCruse]

Gemma,

in manufacturing, there are untold instances of a customer ALSO being a supplier.

Example: Gemma's Custom Chrome Shop is in the business of selling chrome plated parts and does its own chrome plating. Gemma's happens to have GM as a customer. GM has contracted Gemma's to buy chrome-plated struts from Gemma's for GM's cars. GM has huge purchasing advantages, and can buy the unplated struts MUCH cheaper than Gemma's can. To lower its final costs in a chrome-plated strut, GM buys the struts it needs and ships them to Gemma's to be plated. GM has just become a supplier to Gemma, while also existing as a customer of Gemma's.

That is a fairly common thing in automotive. So common, in fact, that the TS and ISO standards have a section in them that makes companies registered to these standards formally address how they handle customer-supplied materials.

Not coincidental at all.

 

[gemma-the-husky]

thanks joe,

i posted my reply in another thread and saw a similar point made, although not as clearly. I still think that having eg a common address table for all sorts of purposes may make a system hard to use and design, and is anti-relational. I thought we were talking about having a single "contacts" file for all company relationships, and therefore including both customers and suppliers in the same file, which again I would not do.