Table Design

[Mani Green via AccessMonster.com]

I’m trying to build a table with custom fields specific to each company.
I have 60 company names. Each company has fields that only belong to that
company. But also, several companies can share the same fields. For
instance:

Wells – companyid, candidate, DTS, DTR, EMP4
ADP – companyid, candidate, training, reference
BOA – companyid, candidate, DTS, DTR, EMP4, An1, An2, An3
Schwab – companyid, candidate, training, DTS, An2, An3

The problem that I having is that some fields should be set up as date
fields and some as yes/no fields, text, etc. (DTS & DTR are date fields)
(EMP4 is a yes/no field)

Right now, I have one table “tblMaster” with company name and companyid
that holds all 60 names. I have a “tblCandidates” with companyid, and
candidateName with other fields that are standard fields for every
candidate. The different fields would be company specific as noted above.

How can I best set up a table structure that when I pull up Wells, it only
pulls up a list of Wells-candidates with those fields specific to Wells,
and when I pull up BOA it only pulls up a list of BOA-candidates with those
fields specific to BOA?

Thanks,
Mani

 

[Mani Green via AccessMonster.com]

More background info...............

Where I'm going with this is: Ultimately, I will need a form that when I
click on the companyName drop down it will populate the form with records
only pertaining to that company. I've already built the form, but I have
not captured the specific fields for each company. Right now all the
companies have the same fields.

How do I build into the form those specific fields for each company? I'm
thinking it would need to be a subform, and I need to build a custom table
for it. Thus, why I am asking for some help to get around building 60
small tables.

Thanks,
Mani

 

[Duane Hookom]

I would attempt to create a more normalized structure similar to:

tblCompanies
=============
CompanyID
CompanyName
.....

tblAttributes
===========
AttributeID
AttributeName
AttributeDataType

tblCompanyAttributes
===============
CompanyID
AttributeID
AttributeValue

Rather than adding fields to a company table, add records to tblAttributes
that would describe the "field" and then add records to
tblCompanyAttributes.