Form To Bring up Specific Fields For Each Company

[Mani Green via AccessMonster.com]

I 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.

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)
and (EMP4 is a yes/no field)

1. 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]

Please Help!
I have 3-tables so far. Profile (detail table), custom package table,
master table of companies (60 company names). See set up below:


Profile tbl | Custom Pkg tbl | Mstr Co’s tbl
drug screen (y/n) 1 - adp
coName employment 10yrs (y/n) 2 - boa
candidate Annex1 (y/n) 3 - Aetna
ssn Annex2 (y/n) 4 - schwab
startdt Exhibit A (y/n)
endDt Exhibit b (y/n)
status
notes

Many companies can use “drug screen” custom package and some will not use
it. For instance the master company ADP uses drug screen only, but BOA
uses drug screen and Exhibit A.

1. How can I link these relationships?

2. On my form the drop-down list is a list of company names tied to coID.
When I click on a company I would like the custom package for that company
to populate the form. The form also has all the fields for profile.

3. When I store the data, I’m confused about storing everything in profile
because the profile table can end up with a lot of fields (50- 60), and
some of the fields will be blank for a record if that custom package is not
used (wasted space)

I’m totally stuck. I don’t know where to go from here. Please help.

Thanks,
Mani

 

[Duane Hookom]

At least one reply in another group where you posted the same question.