Help With a Query

[Susan]

I have what I think is a simple problem, but after two days, I'm exhausted
trying to find the answer. Here goes. I have two tables:

tblProspect Information Table:
EID (primary key)
Last Name
First Name
Manager

tblContact Activity Table:
ID (primary key)
EID (foreign key)
Last Name
First Name
Manager
Type of Contact
Date of Contact
Notes

I have a form based on the Contact Activity Table. On that form, I want to
be able to choose the last name (from the Prospect Information Table) and
then have Access automatically populate the EID, First Name and Manager
fields on that form. I figured out a way to DISPLAY those the information in
those fields, but I need to store the information in the Contact Activity
Table, because I need to be able to run reports by those fields.

As I said, I think I'm just missing something simple. But all the solutions
I've tried from this and other Web sites never seem to work. Thanks for your
help.

 

[MGFoster]

I have what I think is a simple problem, but after two days, I'm exhausted
trying to find the answer. Here goes. I have two tables:

tblProspect Information Table:
EID (primary key)
Last Name
First Name
Manager

tblContact Activity Table:
ID (primary key)
EID (foreign key)
Last Name
First Name
Manager
Type of Contact
Date of Contact
Notes

I have a form based on the Contact Activity Table. On that form, I want to
be able to choose the last name (from the Prospect Information Table) and
then have Access automatically populate the EID, First Name and Manager
fields on that form. I figured out a way to DISPLAY those the information in
those fields, but I need to store the information in the Contact Activity
Table, because I need to be able to run reports by those fields.

As I said, I think I'm just missing something simple. But all the solutions
I've tried from this and other Web sites never seem to work. Thanks for your
help.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table naming conventions are incorrect. Just use Contacts instead
of "tblContact Activity Table" and Prospects instead of "tblProspect
Information Table." You don't have to add a suffix of "Table" nor a
prefix of "tbl").

Your Contacts table has redundant data (Last Name, First Name, Manager).
You only need the EID in Contacts to link back to the Prospects.

Prospects:
EID (primary key)
Last Name
First Name
Manager

Contacts:
EID (foreign key)
Type of Contact
Date of Contact
Notes

The correct Primary Key for Contacts is EID & Type of Contact. That way
you can have the same person entered in the Contacts table as different
types of contacts.

You can use a form/subform set up to ensure the EID gets inserted into
the dependent table (Contacts):

1. Set up a form for the Prospects.
2. Create a form for the Contacts.
3. Insert a subform (the Contacts) into the Prospects form.
4. Set up the Child/Master Link data to use the EID column as the link
between the 2 forms (this usually gets done automatically, but check it,
just in case).

Read a good book on DB design. I usually recommend _Database Design for
Mere Mortals_ by Hernandez.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKGieoechKqOuFEgEQIDywCgvFE4+zCh3XrShgEytmIvM/DR5MgAn3MB
ue2C5Pdk+PsuoqJOLtau3cW3
=CaBD
-----END PGP SIGNATURE-----