Basic question before I try setting this up

[Chatnoir11]

Hi all. I am trying to think before building..and therein lies the trouble ;-)

I have a list of customers and some of those have subsidiaries. I need to be
able to look at the subsidiaries as both "child" of the parent but also as
stand alone companies. (they are related, but operate independently of each
other.. think Saturn is part of GM but is a stand alone company).

Do I have 2 tables, Parent companies and child companies, and then just
duplicate all the data basically? I think this is fairly simple but I am
over complicating it in my head :-(

Thanks for all your help.

 

[Dirk Goldgar]

Hi all. I am trying to think before building..and therein lies the trouble
;-)

I have a list of customers and some of those have subsidiaries. I need to
be
able to look at the subsidiaries as both "child" of the parent but also as
stand alone companies. (they are related, but operate independently of
each
other.. think Saturn is part of GM but is a stand alone company).

Do I have 2 tables, Parent companies and child companies, and then just
duplicate all the data basically? I think this is fairly simple but I am
over complicating it in my head :-(

More likely you would have one table, Companies, containing along with all
the other company attributes a field named "ParentCompany". This field
would contain the ID value of the company's parent company, or else it would
be Null to indicate that the company is not a subsidiary.

When you need to see all companies in a query's results, you would apply no
criteria to the ParentCompany field. When you need to see only top-level
companies, you would apply the criterion "ParentCompany Is Null". When you
need to see only subsidiaries, you would apply the criterion "ParentCompany
Is Not Null", or else (if you want to see who each subsidiary's parent is)
join the Companies table to itself on ParentCompany = CompanyID.

 

[Chatnoir11]

Thank you, Dirk! I have the data set up like that in excel, so it will be
easy to import like that.

One more question, then, please. If I need a report that shows the Parent
companies listed by size, can I have the child companies show up somehow
under the parent, not ordered by size?

 

[Dirk Goldgar]

Thank you, Dirk! I have the data set up like that in excel, so it will be
easy to import like that.

One more question, then, please. If I need a report that shows the Parent
companies listed by size, can I have the child companies show up somehow
under the parent, not ordered by size?

Normally you'd do that sort of thing with a subreport. For the subreport,
the Link Master Field would be the CompanyID (whatever you call it in your
table), and the Link Child Field would be the ParentCompany field.