Client Parent company setup

[Gazza2]

Hi all,

I am trying to figure out how to setup the tables for the following scenario but am unable to find what I am looking for.

If I have a customer(company2) that has an address for goods to be delivered but the invoice has to be made out to the parent company and address (Company1).

Sometimes there can be multiple companies with different names but the invoice must be made out to there Parent company which we can also deliver to.

At the moment I have 3 tables (TblCompany, TblAddress, TblAddressType)

Not sure how to setup the tables for this situation.

Thanks
Gareth

 

[jdraw]

Gareth,

Before you can create the table(s), you need to sort out the rules related to
Companies, Customers, Addresses and the conditions for each. It is quite common in applications to have

PhysicalLocationAddress
MailingAddress
ShipToAddress
HQAddress

Here is a model re Customers and Orders that may give you some insight into tables and relationships.

Good luck.

 

[Gazza2]

Jdraw,

Thanks for the reply.

The problem I`m having is not so much with the addresses but more with the companies.

As a rather bizarre example lets say nokia which is owned by Microsoft(Ignore the name change of nokia for the moment).

On our delivery tickets we have a deliver to box which lists the company name( in this case nokia) and the related address.

The tickets also have an invoice to box which would list the parent company name( in this case Microsoft ) and there related address ( which is also there delivery address.

So if I have a delivery for Microsoft both boxes will have the Microsoft name and address but if I have a delivery for nokia then the deliver to box will have nokia and there address but the invoice to box will have Microsoft and there address.

The address isn't the problem as this is stored in a separate table with a link to the company table. I am just not sure on how to go about setting up the tables for the companies.

I have looked at a few of those data models as I thought this would be quite common but just cant seem to figure out what to look for.

Thanks again for the help.

Gareth

 

[CJ_London]

Would need to know a lot more about your table design and relationships, but in principle you need an additional field in you company table, we'll call it ParentID with a default of 0. You may choose to do this instead in a separate table consisting of childID and ParentID. Typically childID would be the primary key (a child can only have one parent, but a parent can have many children)

So your nokia customer would have a parentID of your customer Microsoft and you Microsoft customer would have a parentID of 0.

If you have a separate table, the childID would be the nokiaID and parentID as above.

This means you can have a parent (Microsoft) with many children (Nokia) and they in turn can have their own children.

To query using this relationship then the table links to itself on parentID inner join childID

e.g.

 SELECT *
 FROM customers as Child inner join customers as Parent on child.parentID=parent.childID

The benefit of this is you do not need a separate table for parents (which will become very difficult to manage in time) and you can have many 'ancestors' so you can map some quite complex relationships if required.

The disadvantage is Access does not have a recursive query ability (e.g. find the ultimate parent of a customer) so you either need to do this in VBA with a loop of some sort or you know the maximum no of 'levels' of ancestors and build your queries accordingly

 

[jdraw]

Gareth,

Further to CJ's comments on company hierarchy, that is a technique that is often done with any hierarchy or Bill of materials processing. We used it often when categorizing records in a hierarchy.

for example:

.....
522	 Credit Intermediation and Related Activities
5221   Depository Credit Intermediation
52211	 Banking
522111	  Personal and Commercial Banking Industry
522112	  Corporate and Institutional Banking Industry
52213	 Local Credit Unions
52219	 Other Depository Credit Intermediation
5222  Non-Depository Credit Intermediation
52221	 Credit Card Issuing
52222	 Sales Financing
52229	 Other Non-Depository Credit Intermediation
522291	   Consumer Lending
522299	   All Other Non-Depository Credit Intermediation
............

I think of your situation much like ordering something online. You often get asked what your shipping address is; then they ask what is your billing (Invoicing address) and often there is a Same As Above box. (probably the other way around --they want the billing info first!!)

I don't see it as a different concept.
Another consideration is to have multiple address types (where needed)
You could have the Nokia Billing Address pointing to the Microsoft Finance Dept.
Depending on how often/how many of these you have, you can judge whether every company needs every address type. You could have 1 address type, or more for only a few companies. Let your situation dictate.

Good luck with your project.