Access 2007 parent-[PARTIAL] child table

[MikeF]

Trying this again, please advise if clarification is necessary.

Have one master called tblCompanies.
This contains name/address/phone and other general info [only] relevant to
its records.

In Companies is a field called CompanyTypes, which is a lookup to
tblCompanyTypes.
As an example, one of those CompanyTypes is Restaurant [there are several
others, but will use just "restaurant" for this example].

Accordingly, there is a another table called tblRestaurantDetails.
It contains an indexed CompanyID field, with the same ID numbers as
tblCompanies for the relevant records.
*** tblCompanies contains a few thousand records. Of those only a few
hundred are restaurants. Those existing records are in this detail table.
Also in this table are detail fields such as type [ie Japanese/Mexican/etc],
tax rate, website, head chef, a memo field for favorite dishes, and so on.

Note – this premise is the same for other CompanyTypes, that each have their
own detail tables, and which only a few hundred relevant records exist in
tblCompanies.

Here’s the question:
How do I bind these tables together?
When I add a record to tblCompanies, and designate it as Restaurant in the
CompanyTypes field, would like a corresponding record to be automatically
added to tblRestaurantDetails, where I can enter the detail info. *** The
companyTypeID for restaurants is 3.

Can this be easily accomplished?

Any assistance will be sincerely appreciated.
Thanx,
- Mike

 

[Fred]

Hello MikeF,

Here are a few thoughts; maybe others will have better ones.

What point is there in automatically creating an empty record in your e.g.
restaurant details table? The purpose of that table is to hold other data
that you put in. Why not just have the record created when you enter data
into it?

Also, depending on your particulars, having your "type" field might be
redundant to the "type" being recorded via existence of a link to the
"details" table for the link, and thus problematic. Potential problems are
that the two could conflict with each other later, that someone might update
one and not the other, and (for better or for worse) you are precluding an
establishment from being recorded as two types.

Here's my low tech idea:

Keep your company table, PK = company ID. Possibly drop the "type" field.

Create details tables as you describe for the various types. Include a FK
CompanyID field in each, and link all of those FK's to the companyID PK
field in your company table.

Make a company form, and put a subform on it for each of the detail tables.
When you enter data in the appropriate subform, a linked record will
automatically be created to the displayed company. The existence of that
link* will document that it is a company of that type.

For better or for worse, this will allow a company to have/be more than one
type of operation.


* There are three phases of creating a link.....table structure, "drawing
the line" and linking two specific records by placement of the PK value of
one record into the FK field of another record... ..this refers to the
latter.

Well, there's one idea. Hope it helps a little.

 

[Michael Gramelspacher]

Trying this again, please advise if clarification is necessary.

Have one master called tblCompanies.
This contains name/address/phone and other general info [only] relevant to
its records.

In Companies is a field called CompanyTypes, which is a lookup to
tblCompanyTypes.
As an example, one of those CompanyTypes is Restaurant [there are several
others, but will use just "restaurant" for this example].

Accordingly, there is a another table called tblRestaurantDetails.
It contains an indexed CompanyID field, with the same ID numbers as
tblCompanies for the relevant records.
*** tblCompanies contains a few thousand records. Of those only a few
hundred are restaurants. Those existing records are in this detail table.
Also in this table are detail fields such as type [ie Japanese/Mexican/etc],
tax rate, website, head chef, a memo field for favorite dishes, and so on.

Note – this premise is the same for other CompanyTypes, that each have their
own detail tables, and which only a few hundred relevant records exist in
tblCompanies.

Here’s the question:
How do I bind these tables together?
When I add a record to tblCompanies, and designate it as Restaurant in the
CompanyTypes field, would like a corresponding record to be automatically
added to tblRestaurantDetails, where I can enter the detail info. *** The
companyTypeID for restaurants is 3.

Can this be easily accomplished?

Any assistance will be sincerely appreciated.
Thanx,
- Mike

Here is a demo, which I think shows what Fred is describing.

www.psci.net/gramelsp/temp/Subtypes_Demo.zip

 

[MikeF]

Fred,
Thanx for the reply.

To answer your questions, and perhaps clarify in the process...

What point is there in automatically creating an empty record in your e.g.
restaurant details table? The purpose of that table is to hold other data
that you put in. Why not just have the record created when you enter data
into it?

An empty record is not the intent. It's to have the Companies table
contain general info about many companes with several CompanyTypes, and have
a corresponding detail table contain proprietary info about each of the
Company Types. Say there are 200 records with CompanyType 3 in the
Companies table of 2000 records - if I add a record to the Companies table
[now 2001 records], and assign 3 in the CompanyType field, would like it to
automatically add a record to the RestaurantDetails table [now 201 records]
so the appropriate detail info for that record can be filled in.

Uncertain what you mean by PK and FK.

Thanx,
- Mike

 

[Fred]

Hello Mike,

I'm with you on the first few sentences in your response. My quibble was
with your defining which instant / action that new record in the detail
table gets added. And my suggested solution did not utilize your "company
type" idea.


By "PK" I meant primary key, by "FK" I meant foreign key. Those are big
topics but.......

Your CompanyID field in your company table is an example of a primary key.
Must be unique, never repeated in the table.

Your CompanyID field in your restaurant table is an example of a foreign
key. Not necessarily unique in that table (although in your case it is)

 

[MikeF]

Fred,

Got it re PK/FK.
....Knew it was a forehead-slapper.

How do I define a foreign key of CompanyID - for only CompanyType 3 - in
tblRestaurantTypes, and will it automatically update records?

Thanx for your assistance.
- Mike

 

[Fred]

Hello Mike

Mike,

My idea is per my original response and was to ditch or sideline your "type"
field idea. But to add some detail:

Let's assume that your PK CompanyID field is a autonumber (and thus long
integer) field.

Put a long integer (NOT autonumber) "CompanyID" field into your
"restaurantInfo" table.

Open the "Relationships" view, add the above two tables to the view, and
draw a line between the above two fields.

Now make "Company" form and make a subform in it whihc uses your
restaurantinfo table as it's data source. (later you'll add more subforms
for other types)

Now open your form in user mode. Find (or type in) the name of a company
which is a restaurant.

In the blank line in the subform, type in the name of the chef. Typing
the chef's name will also cause the following events to automatically
happen:

Creates a new record which contains the chef's name, plus loads the
CopmpanyID of the restaurant into the FK CompanyID field of the restaurant
info table, thus linking that record to the company

 

[MikeF]

Thank you Fred, will give this a go and advise.

 

[MikeF]

Fred,

That worked, I now see exactly what you mean.

Now, need to add an "Add New Record" button on that form.
It needs to add a new record to the Companies table, and put a "4" in the
CompanyTypes field.

Do you have any ideas on that?

Thank you again.
- Mike