contacts template- adding a second table

[Slushpump]

I have a number of contacts. I also have a second table, with a number of
groups. In each group, I have an "owner", "alternate owner", and a number
of "members" (01 thru 10). I want to not duplicate all the email addresses
in the group table, but refer to the existing email address in my contacts
table.

So I'm attempting to record only the contact ID in each of the groups
fields, then display the email address in the form (rather than the contact
ID).

I'm an absolute rookie with access, but have some sql background. Can this
be done? My contacts reconciliation process will ultimately (try to) mail
out the groups details to their primary contact for verification/update, then
I'll send out emails to every contact to verify their details.

One contact may be in multiple tables; possibly in different roles.

Am I missing something easier than attempting to do this with the office
2007 template?

 

[techrat]

In general, what you have described is very doable. Each of the Fields
in the group table (owner, alt_owner) should be set up in the group
table as long integers and will hold the ContactId for the related
contact. On the form, tie the form recordset to the group table. For
the Owner and Alt_Owner field use a combo box to show pick and display
the email address of the associated contact. The RowSource for each
combo box should be something like "Select Contact_id,
Contact_Email_Address from Tbl_Contact;" The number of columns for
each combo box should be 2. If you set the column widths to 0;1, you
will only show the email address and the contact_id will be hidden.
(The first column width set to 0).
Finally the cotrol source for the combo boxes should be something like
Group_Owner_Contact_Id and Group_Alt_Owner_Contact_id.

Once this is all together, you can pick out the owner and alt owner by
selecting the email address from the combo box.

P.S. In the rowsource sql for each combo box you may want to add an
order by statement to sort the results.

HTH

 

[Slushpump]

Excellent, techrat... thanks. I have a "bonus question" if you are game.

On my "wish list" would be a function that, when the
attestation/confirmation email is received from the contacts, that we'd
automagically capture a "last attestation date". I've been told elsewhere
that this isn't possible. Got a second miracle up your sleeve?

appreciate the help, regardless of the second answer! thanks.

 

[kim]

I have a number of contacts. I also have a second table, with a number of
groups. In each group, I have an "owner", "alternate owner", and a
number
of "members" (01 thru 10). I want to not duplicate all the email
addresses
in the group table, but refer to the existing email address in my contacts
table.

So I'm attempting to record only the contact ID in each of the groups
fields, then display the email address in the form (rather than the
contact
ID).

I'm an absolute rookie with access, but have some sql background. Can
this
be done? My contacts reconciliation process will ultimately (try to) mail
out the groups details to their primary contact for verification/update,
then
I'll send out emails to every contact to verify their details.

One contact may be in multiple tables; possibly in different roles.

Am I missing something easier than attempting to do this with the office
2007 template?