140 Fields on a single Record - or What ?

[MyEmailList]

OK, here is the problem. We are designing a database with a record for
each company... putting the stuff in tblAccount

For each account we have several mandatory people for which we require
data be collected and entered...

CEO or Owner - General Manager - Decision Maker - Our Contact Person
There - Accounts Payable Contact - Alternate Contact-1 - Alternate
Contact-2 (so 7 people/positions per company for which we are
requiring data)

For each of these may be at different locations... different phone &
fax... and we may or may not have permission to email, call or fax...
so for each we have...

Fname MI Lname Title Funcion Adr_1 Adr_2 City State Zip Phone Fax
Email OK2Call OKtoMail OKtoEmail Memo, etc.

If we put the names, etc in a tblPeople then how do we create a form
showing all the related fields for all the required people? We need to
each position to show with all the fields associated like title,
phone, etc. We do not want the option just adding names to tblPeople
as needed. We want to specify which people and what data for each.

Did I make the problem clear here?

Thanks for any help.

Mel

 

[Franck]

easy
2 tables
tbl_company
tbl_person

--------
tbl_company
--------
fld_IDauto (auto increment, primary key)
fld_companyname (name of company)
.... etc (other company info like address)

-------
tbl_person
------
all your fields + fld_companyID
the companyid field should be integer type and come from tbl_company
list (second tab on the bottom window in edit mode)

after create a form with a query that take tbl_company info
and after create a sub-form in it that use a query with all the info
from tbl_person and when its done you can select wich feild
dependencies you want
select fld_IDauto (from tbl_company) and second select the
fld_companyID (from tbl_person)
the link done it should work and fill the sub form with all person
with the same id of the company shown at the very moment.

 

[MyEmailList]

The subform will list every person we enter that is linked to that
company.

But that doesn't solve our problem... we want a form such that when we
go to create a new company will have fields for all the mandatory
people we require.

Company:
CEO:
General Manager
Accounts Payable Contact:
Our Account Rep:
Decision Maker:
Etc:

In other words we want mandatory fields to show up for each of the
above with all the fields that go along with each... like address,
phone, email, etc.

If we simply link the tblAccount to tblPeople how can we define all
the People we want entered for each company?

thanks for the help.

Mel

 

[Franck]

in the title field just change it to combobox and put a list wich will
be

CEO:
General Manager
Accounts Payable Contact:
Our Account Rep:
Decision Maker:

=================================================
Company : Microsoft
address : fort knox

=============================================
| Title | Name | Phone |
Address |
|============================================|
| CEO | Mrs.X | 888-8888 | 111 greenvalley
|
| General Manager | Mr.Y | 888-8888 | 222 greenvalley |
| ... | ... | ...
| ... |
|============================================|

=================================================

As you see you have company info and employee info in the same window
main form being filled with the company info and sub form filled from
person list table
where there company id match which mean they are from that company.

just put the title box as a combo box filled whit a list which are
your
CEO:
General Manager
Accounts Payable Contact:
Our Account Rep:
Decision Maker:

try it it work ive done this multiple time before and it work.

 

[MyEmailList]

This won't work Franck. The client could only enter data for one or
two of the required people.

We require they enter about a half dozen key people for each
company... this means a defined field for each person along with
defined fields for their address, phone, fax, email, etc.

Your method is the typical method for adding whoever to each account
record... BUT...

When our client creates a company or account record they must right
then see REQUIRED FIELDS for each REQUIRED PERSON associated with that
company... and enter the data for each...

CEO/Owner
General Manager
Account Rep
Decision Maker
Accounts Payable Manager

With your method they would have the option of entering data for only
a few of the people... or none of the people.

I welcome other idea's.

Thanks.

Mel

 

[Franck]

just do exactly what i told you and put vba code that on the form
close check if he enter the REQUIRED values.

And required fields are easy to make in access btw use the allow null
field in your table and put it to yes user will be required to enter
it in order to save changes.