[elevations2000]

Building an Access data base
I want to know what employer contact attended our events.
When I look up a contact I want to see the contact name, a list of events attended with an associated date and campus name for each.
Can anyone help me?
The tables constructed are as follows:
TblContacts
EmployerContactID
EmployerName
PrimaryOrSecondaryContact
FirstName
LastName
Title
OfficePhone
Email
CellPhone
Fax
StreetAddress
City
State
ZipCode
Country
EventID
EventID.Value
Notes
CampusCodeID



TblEvents
EventID
EventName
EmployerContactID
DateAttended
CampusCodeID


TblCampusCode
CampusCodeID
CampusCode
EventID



What other information would you need to know?
If you know of a video I can watch that would be great but feel free to answer here or eamil me at jco1@bryant.edu or josephcouellette@msn.com
Thanks

[OBP]

elevations2000, welcome to the Forum.
This looks rather like Course work.

You will need one more table to link the Contacts Table and the Events Table which should have an EmployerContactID and an EventID both set to Non Indexed and Number type Long.

You also do not need the Ids of the other tables in each table.

[elevations2000]

I have a total of three tables.

The two you speak of are:

TblEvents
EventID
EventName
EmployerContactID
DateAttended
CampusCodeID


TblCampusCode
CampusCodeID
CampusCode
EventID

[elevations2000]

Not sure where you are - in RI we are experiencing heavy rain. lightning and tornado warnings.
If you reply I will try to get back to you later.
Thanks
Joe

[OBP]

I am in the Wales.

[elevations2000]

Okay,

I believe my next steps are:

Create a relationship between the TblContacts (using EmployerContactID) to TblCampusCode (using EmployerContactID), is this correct?

Then create a relationship between TblEvents (using EventID) to TblCampusCode (using EventID), is this correct?

TblContacts
EmployerContactID
EmployerName
PrimaryOrSecondaryContact
FirstName
LastName
Title
OfficePhone
Email
CellPhone
Fax
StreetAddress
City
State
ZipCode
Country
Notes


TblEvents
EventID
EventName
DateAttended


TblCampusCode
CampusCodeID
CampusCode
EventID (Index - No)
EmployerContactID (Index - No)

I would like to send some screen shots to you but can't with this applicaiton. Would you be able to accept an email from me? josephcouellette@msn.com

Let me know what you think.
Thanks
Joe

[OBP]

What is the tblCampusCode for is that going to be the Linking table that will give you the Events and the Contacts that attend the events as I suggested, as I am not sure what the Campus part is for.
Is that the Venue for the Events, or where the Contacts come from?

[elevations2000]

The TblCampusCode is a location - in my case which campus the event took place at.
My goal is to have the tblContacts connect with a specific event that is listed in tblEvent which will connect to the tblCampus.
So, when someone is entering a new employer contact they will have the option to select from a drop down list (that keeps a list of events they attend), selecting a date attended (from a built in calendar) and selecting a Campus code (from a list). Each time the employer contact attends another event the person entering - searches for the contact and then updates the new additional event they atteded with date and campus.

Does that help?

Can I send you an email with screen shots off line of this web site?
Thanks
Joe

[OBP]

You can send me the screen shots as I have already emailed you my email address.

I think You need a Campus Table that only has the CampusID and Campus Name/Description.
The Date Attended should be in the Linking table along with the ContactID, EventID and CampusID.
I would change the Name of the CampusCode table to something like ContactsEvents and then create a CampusCode tabel with Just the CampusID and Name.
The ID fields in the ContactsEvents (or whatever you call it) should be number Type Long, they can be Indexed with Duplicates = Yes.

[elevations2000]

Sent you an aside with screen shots.
Thanks
Joe

[OBP]

See my comment at post #9.

[elevations2000]

OBP,

I finally have the database put together.
I have added a from that runs off a query.
In the form I added a button to search for a contacts email.

I am trying to get a button, placed on a form in Access “Search Contact Email” to search for an email address in a Qry. QRYEmployerContacts and return all the information back into the form view.
I do need to know how to code this in VB so it searches.
When I click on the Event in design view I get the following VB code:
=================================================================
Private Sub SearchContactByEmail_Click()

On Error GoTo Err_SearchContactByEmail_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_SearchContactByEmail_Click:
Exit Sub

Err_SearchContactByEmail_Click:
MsgBox Err.Description
Resume Exit_SearchContactByEmail_Click
End Code
=======================================================
The name of the qry is labeled in my Access data base as "QRYEmployerContacts"
Should I have the button look directly at my contacts table instead?
Any help you can provide would be greatly appreciated.
Thanks
Joe
ps I can't find your email address.

[OBP]

The easiest way to do what you want is to use the Combo Wizard to create a "Find" combo, when using the wizard the default setting is to find records in a table or query, you want the 3rd option Find a record on the form matching your combo selection.

[elevations2000]

OBP, you have helped me once again.
Thank you
Joe