Tables Design

This is similar to an earlier thread about Teams and Games. I have a table
for Firms which contains Subcontractors, Suppliers, Owners, Designers,
Insurance Providers, etc. A Firm Type Code field identifies each firm's type
of work. The primary key for the Firms table is an autonumber field.

I'm trying to add an Insurance Certificate feature to my database, so I've
created a table for Insurance Certificates. Eventually there should be one
Insurance Certificate record for each Subcontractor. Each record will also
have various Insurance Providers and Insurance Carriers. I'm beginning to
think I can't just use Combo Boxes to reference all these firms from my Firms
table. Normalization notwithstanding, should I break my Firms table up into
separate tables for each type of firm?

Thanks

There are advantages to having all the firms in the one table. Typical
example is if you need another table with a foreign key (e.g. for
appointments or payments, where the record could relate to any of these
types of firm.)

Create a query to use as the RowSource for your combo, e.g.:
SELECT FirmID, FirmName
FROM Firm
WHERE FirmTypeID = 'subcontractor'
ORDER BY FirmName, FirmID;
Save it, and use it as the RowSource for the combos throughout your
application where you want a subcontractor selected.

Regarding the insurance certificates, one contractor could have many
certificates over time (as each expires)? If so, it's a one-to-many relation
(unless you only keep the current certificate.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"oldblindpew" wrote in message
...
This is similar to an earlier thread about Teams and Games. I have a
table
for Firms which contains Subcontractors, Suppliers, Owners, Designers,
Insurance Providers, etc. A Firm Type Code field identifies each firm's
type
of work. The primary key for the Firms table is an autonumber field.

I'm trying to add an Insurance Certificate feature to my database, so I've
created a table for Insurance Certificates. Eventually there should be
one
Insurance Certificate record for each Subcontractor. Each record will
also
have various Insurance Providers and Insurance Carriers. I'm beginning to
think I can't just use Combo Boxes to reference all these firms from my
Firms
table. Normalization notwithstanding, should I break my Firms table up
into
separate tables for each type of firm?

Thanks

Yes, I'm only concerned about the current certificate. I don't care about
past certs.

I do have a query for RowSource, but I'm thinking there's a bigger problem.
The problem is not with identifying the insured firm (Subcontractor). The
problem is with other firms in the same certificate record, e.g. the
Certifcate Provider and the Insurance Carrier. Can I have a keys for the Sub
and the Provider and the Carrier all in the same record and all pointing back
to the Firms table?

Also, I'm not really interested in having the user see the autonumber value
for these firms, but I can't figure out how to display the firm name on the
form rather than the key value.

I'm sorry to ask such basic questions; never have I had to struggle so hard
with a programming language to get it to do such basic operations.

Thanks

"Allen Browne" wrote:

There are advantages to having all the firms in the one table. Typical
example is if you need another table with a foreign key (e.g. for
appointments or payments, where the record could relate to any of these
types of firm.)

Create a query to use as the RowSource for your combo, e.g.:
SELECT FirmID, FirmName
FROM Firm
WHERE FirmTypeID = 'subcontractor'
ORDER BY FirmName, FirmID;
Save it, and use it as the RowSource for the combos throughout your
application where you want a subcontractor selected.

Regarding the insurance certificates, one contractor could have many
certificates over time (as each expires)? If so, it's a one-to-many relation
(unless you only keep the current certificate.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"oldblindpew" wrote in message
...
This is similar to an earlier thread about Teams and Games. I have a
table
for Firms which contains Subcontractors, Suppliers, Owners, Designers,
Insurance Providers, etc. A Firm Type Code field identifies each firm's
type
of work. The primary key for the Firms table is an autonumber field.

I'm trying to add an Insurance Certificate feature to my database, so I've
created a table for Insurance Certificates. Eventually there should be
one
Insurance Certificate record for each Subcontractor. Each record will
also
have various Insurance Providers and Insurance Carriers. I'm beginning to
think I can't just use Combo Boxes to reference all these firms from my
Firms
table. Normalization notwithstanding, should I break my Firms table up
into
separate tables for each type of firm?

Thanks

I think I've got things working. My Combo Box had a typo in the RowSource
query and the wrong Bound Column. One tiny step forward; at this rate I'll
be retired before I get this application done.

"oldblindpew" wrote:

Yes, I'm only concerned about the current certificate. I don't care about
past certs.

I do have a query for RowSource, but I'm thinking there's a bigger problem.
The problem is not with identifying the insured firm (Subcontractor). The
problem is with other firms in the same certificate record, e.g. the
Certifcate Provider and the Insurance Carrier. Can I have a keys for the Sub
and the Provider and the Carrier all in the same record and all pointing back
to the Firms table?

Also, I'm not really interested in having the user see the autonumber value
for these firms, but I can't figure out how to display the firm name on the
form rather than the key value.

I'm sorry to ask such basic questions; never have I had to struggle so hard
with a programming language to get it to do such basic operations.

Thanks

"Allen Browne" wrote:

There are advantages to having all the firms in the one table. Typical
example is if you need another table with a foreign key (e.g. for
appointments or payments, where the record could relate to any of these
types of firm.)

Create a query to use as the RowSource for your combo, e.g.:
SELECT FirmID, FirmName
FROM Firm
WHERE FirmTypeID = 'subcontractor'
ORDER BY FirmName, FirmID;
Save it, and use it as the RowSource for the combos throughout your
application where you want a subcontractor selected.

Regarding the insurance certificates, one contractor could have many
certificates over time (as each expires)? If so, it's a one-to-many relation
(unless you only keep the current certificate.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"oldblindpew" wrote in message
...
This is similar to an earlier thread about Teams and Games. I have a
table
for Firms which contains Subcontractors, Suppliers, Owners, Designers,
Insurance Providers, etc. A Firm Type Code field identifies each firm's
type
of work. The primary key for the Firms table is an autonumber field.

I'm trying to add an Insurance Certificate feature to my database, so I've
created a table for Insurance Certificates. Eventually there should be
one
Insurance Certificate record for each Subcontractor. Each record will
also
have various Insurance Providers and Insurance Carriers. I'm beginning to
think I can't just use Combo Boxes to reference all these firms from my
Firms
table. Normalization notwithstanding, should I break my Firms table up
into
separate tables for each type of firm?

Thanks