External/Internal tables

[m stroup]

Hi,

I have an external tblStaff which includes contact info, name, etc.
I have an internal tblContact which includes contact info, name, etc.
I have tblDocuments which includes title, publishdate, author, etc.
I would like to have a combo box on my form which would allow me to select
the author from either the tblStaff or tblContact.
tblStaff and tblContact would be exclusive to each other.

Is this possible?

 

[Jeff Boyce]

It sounds like you are saying you have contact information (name, etc.) on
more than one type of contact (i.e., "Staff", ...).

While you might need to keep two separate lists if you were using Excel, in
Access you can use a single table and add a single field to designate the
"type" of contact, if that distinction is important.

Besides, if you ever come up with a THIRD contact type, you'd need to add
another table, modify your queries, forms, reports, macros, code, ... -- a
maintenance nightmare!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[m stroup]

Thanks Jeff,

I do have different types of contacts. Authors/approvers/staff... But
information on the staff already resides in a table in a different database.
I just don't want to recreate that particular list.

What I would love to do is create a tblPeople once with their "type" of
contact, importing from the tblsStaff in the second database. Then I would
like to be add additions I make to tblStaff to the tblPeople dynamically.
And be able to add others using the NotInList event.

There is a possiblity that each person I enter could be an author or an
approver.

Talking thru this I am thinking I need to add code to the db that has the
tblStaff so when a record is added it also adds a record to the document db.

Is this a good approach?
--
Teach me to fish! Thanks for the help.
Pax, M

It sounds like you are saying you have contact information (name, etc.) on
more than one type of contact (i.e., "Staff", ...).

While you might need to keep two separate lists if you were using Excel, in
Access you can use a single table and add a single field to designate the
"type" of contact, if that distinction is important.

Besides, if you ever come up with a THIRD contact type, you'd need to add
another table, modify your queries, forms, reports, macros, code, ... -- a
maintenance nightmare!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Aha! One person could serve in many roles.

If you add a tlkpRole (to define possible roles, now and in the future), and
trelRolePlayed (to hold valid PersonID/RoleID pairs), you should be able to
have one person serve in multiple (simultaneous) roles.

As for the "some People here" and "some People there", that's an ...
opportunity!

One approach would be to refresh an "internal" (this application only) list
of persons each time the app starts up, using that other source you
mentioned. The problem is keeping your internal persons from being wiped by
data coming in from outside.

Another approach would be to persuade the owner of that other source to
allow you to store "persons" in there.

If you absolutely, positively cannot follow either of these, a third
alternative might be to keep your own person table internally, except for
those persons you look up on the external table. Then you'd need to create
a UNION query to get a full list of people (and hope that the external
table's PersonID doesn't accidently match up with your internal table's
PersonID!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff,

I do have different types of contacts. Authors/approvers/staff... But
information on the staff already resides in a table in a different
database.
I just don't want to recreate that particular list.

What I would love to do is create a tblPeople once with their "type" of
contact, importing from the tblsStaff in the second database. Then I
would
like to be add additions I make to tblStaff to the tblPeople dynamically.
And be able to add others using the NotInList event.

There is a possiblity that each person I enter could be an author or an
approver.

Talking thru this I am thinking I need to add code to the db that has the
tblStaff so when a record is added it also adds a record to the document
db.

Is this a good approach?

 

[m stroup]

Thanks for the input Jeff. I actually own the other database. So that seems
like the simplest resolution. I did have a table for each role, but you are
right...I need to have a tble of roles and a crosstable that links the
person/role, and the document.

My simple database is giving me many opportunities!