Database to track multiple subsidiary companies- Please help!

[amongangels]

I am trying to create a database that will track company information.



Some of the specifics to be considered:

1. My company has over a hundred subsidiary companies across the globe.
2. Each company has either Officers, Directors, Managers, or any combination of those.
3. An individual that is an Officer, Director, or Manager of one company can also be an Officer, Director or Manager of any number of our other companies.

I am attempting to capture as much information for each company as I can that I can add by form and generate a subsequent report.

So far, I have tables for Company, People & Roles. I thought about creating a table for jurisdictions, but not sure how I would connect one company to several different jurisdiction.

Some of my questions (besides the issue in the previous paragraph :

-Can a form be connected to more than one table?
-Does a report have to contain the same information as the form, or can it be pared down to only contain certain fields?
-After someone helps me figure out this tangled little web, can I come back to beg for help with relationships and queries next?

I appreciate any knowledge that you are willing to share.

Thank you in advance, Angel.

[June7]

You are describing many-to-many relationships. This requires 3 tables.

As example:
A company can have multiple jurisdictions and each jurisdiction can apply to multiple companies.
Tables needed would be Companies, JurisdictionTypes, CompanyJurisdictions.
CompanyJurisdictions is a "junction" table that will contain records associating companies and jurisdiction types.

Generally, a form is to enter/edit data for one table. Use form/subform arrangement for data entry into related tables. Example being Orders as "parent" and OrderDetails as "dependent" where user would select product(s). This arrangement represents at least 4 tables: Suppliers, Orders, OrderDetails, Products.

Report can contain whatever data you want, not defined by form.

Ask as many questions as you want.

[madpiet]

So what if you add ParentCompanyID and make it optional (default null)?

One of the fun parts of this is that Abscess doesn't have hierarchies. You have to drill into them yourself. When I did it back before pollution, I was doing something a little bit simpler maybe (but very similar) I had a list of courses and a list of prerequisites for those courses (NextCourseID, RequiresCourseID), and I added a "level" column to the Course table and then did something like this:

1. find all the courses with no prerequisites... SELECT CourseID FROM Course c LEFT JOIN Prerequisite p ON c.CourseID = p.NextCourseID WHERE p.NextCourseID IS NULL (I think you could just do WHERE NOT EXISTS and use a correlated subquery, but ...)
2. Mark those as Level = 1.
3. Find all courses where all the prerequisite courses are in the Level = 1 group. Those are level 2... lather rinse repeat until you run out of courses.

Each company may have a parent company. Any without a ParentCompanyID would be the "owning"/"Top" company. then you'd just walk down the tree.

In that big boy pants database, SQL Server, you can use hierarchies and recursive CTEs to solve this kind of thing. (I asked about the courses question a million years ago... my post is still there.)

[jojowhite]

If you have a Big Company and have many subsidiaries world-wide, why bother with this.
just hire an expert and be done with it.

[CJ_London]

Who will be using this app? A few people? Hundreds of people? One location? Many locations? In one country? Many countries?

it may be access is a suitable app to use, perhaps with sql server or azure- or maybe not - depends on what your userbase will be

[amongangels]

Who will be using this app? A few people? Hundreds of people? One location? Many locations? In one country? Many countries?

it may be access is a suitable app to use, perhaps with sql server or azure- or maybe not - depends on what your userbase will be

I will be the only person using the database, in one location.

[xps35]

I suggest a structure like this:

[NoellaG]

Be careful, some years ago we had the same idea of tracing companies and people, but the legal department stopped the project because of legal consequences (privacy rules). We could only retain information about customers and had to ask the consent of individuals working for those companies.