1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access data base

Discussion in 'Business Applications' started by elevations2000, Jul 18, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    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 [email protected] or [email protected]
    Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  3. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    I have a total of three tables.

    The two you speak of are:

    TblEvents
    EventID

    EventName
    EmployerContactID
    DateAttended
    CampusCodeID


    TblCampusCode
    CampusCodeID
    CampusCode
    EventID
     
  4. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    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
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I am in the Wales.
     
  6. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    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? [email protected]

    Let me know what you think.
    Thanks
    Joe
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  8. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    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
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  10. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    Sent you an aside with screen shots.
    Thanks
    Joe
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    See my comment at post #9.
     
  12. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    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.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  14. elevations2000

    elevations2000 Thread Starter

    Joined:
    Jul 18, 2012
    Messages:
    8
    OBP, you have helped me once again.(y)(y)(y)(y)(y)(y):)
    Thank you
    Joe
     
  15. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1061563