Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access data base


(!)

elevations2000's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
18-Jul-2012, 10:29 AM #1
Solved: Access data base
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

Last edited by elevations2000; 18-Jul-2012 at 10:35 AM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
18-Jul-2012, 12:23 PM #2
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.
__________________
OBP
I do not give up easily
elevations2000's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
18-Jul-2012, 03:39 PM #3
I have a total of three tables.

The two you speak of are:

TblEvents
EventID

EventName
EmployerContactID
DateAttended
CampusCodeID


TblCampusCode
CampusCodeID
CampusCode
EventID
elevations2000's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
18-Jul-2012, 03:56 PM #4
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
18-Jul-2012, 04:46 PM #5
I am in the Wales.
elevations2000's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
19-Jul-2012, 10:07 AM #6
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
19-Jul-2012, 01:01 PM #7
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's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
19-Jul-2012, 01:16 PM #8
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
19-Jul-2012, 01:35 PM #9
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's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
19-Jul-2012, 04:12 PM #10
Sent you an aside with screen shots.
Thanks
Joe
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
20-Jul-2012, 05:28 AM #11
See my comment at post #9.
elevations2000's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
09-Aug-2012, 11:22 AM #12
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 12:14 PM #13
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's Avatar
elevations2000 elevations2000 is offline elevations2000 has a Profile Picture
Computer Specs
Member with 8 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
09-Aug-2012, 01:57 PM #14
Smile Excellent
OBP, you have helped me once again.
Thank you
Joe
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
access, access 2010, access data base, building tables, relationships

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑