Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching Multiple Entries in the same Field (2000 SP2)

    I am trying to create a new Access database, and I have one question (so far). <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I have tried to outline below what I need to do, but I'm not sure if Access can even handle this.

    A company has the following 5 employees:
    Adam, Barney, Cary, David, and Edgar

    The company has a lunch meeting and Cary, David, and Edgar attend. The company has a dinner meeting and Adam and David attend.

    I want to be able to create a table that I can enter the type of meeting and attendees into. I know I can take care of the type of meeting (lunch or dinner), but I can't figure out how to add my meeting attendees into the meeting. The boss wants to be able to do a search down the road and see how many meetings each employee has attended (i.e. David has been to 2 meetings and Barney has been to 1), so I don't know if I can just lump all of the names into the same field.

    Would I be able to enter the employee names into another table and do a lookup for each of them? I'd like to avoid the possibility of someone not being counted properly because their name was wrong (Dave instead of David, or Carey instead of Cary).

    Any ideas?

    Thanks,
    Becky

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Searching Multiple Entries in the same Field (2000 SP2)

    To Store the data, You could set up a table containing all employee names. (Suggest using the Employee ID or some other unique ID as part of the Primary Key). Link the Employee table to a new table, say meetings, as a one to many with the link based on the employee id. You could then populate the meeting table with a date field (i.e., current date) and meeting name. Once done, you could execute queries linking the tables and provide a review on who attended meetings by date, by meeting type, number of meetings, etc.

    Just some ideas...
    Regards,

    Gary
    (It's been a while!)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching Multiple Entries in the same Field (2000 SP2)

    Gary,

    First of all, thank you for the ideas...and for the lightning quick response! I tried to do what you said, but I'm having trouble with the part of adding the attendees to the meeting table. I can't figure out how to make that work. I understand the part about running queries to see who attended meetings and all that part, but how do I tell the table who came? I have one table called employees (all employees listed), and I have one called meetings. Do I have to enter enough blanks in the meeting table for each of my attendees to show up? I think I'm very <img src=/S/confused.gif border=0 alt=confused width=15 height=20>, but that's not hard to do! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I couldn't figure out how to make the relationship one-to-many, but I know I've done it before. It's just been a while.

    I've attached my very small test database, if anyone wants to take a look at what I'm doing wrong.

    Thanks,
    Becky
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Searching Multiple Entries in the same Field (2000 SP2)

    I would have 3 tables. An employees table, a meetings, and an attendance table. The employee table is obvious. The Meetings table defines each meeting (date, time, place, purpose, etc.) and assigns each a MeetingID. The attendance ttable merely lists each employee that attended each meeting. It only has 2 fields: MeetingID and EmployeeID. The relationships are:
    Meetings --> attendance (one-to-many, based on MeetingID)
    Employees --> attendance (one-to-many, based on EmployeeID)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching Multiple Entries in the same Field (2000 SP2)

    An example of what mark said is attached. see Meetings.mdb
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks! (2000 SP2)

    Mark,

    Perfect instructions for an Access dummy like me! (You'd never know I'm MOUS Master certified) With a little monkeying around, I got it to work. As soon as I got it almost figured out (I was having a little trouble with the query), aap2 posted back a database for me with a working query. I really appreciate your help (and Gary's, too!!!). Let's just see if I can finish getting this database together without any more questions <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks again,
    Becky

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Thanks! (2000 SP2)

    Excuse my ignorance, but what is MOUS?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: MOUS

    Microsoft Office User Specialist - see the official MS MOUS site for more info....

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: MOUS

    >>Microsoft Office User Specialist <<

    I'm usually pretty good at decyphering acronyms, but this one got by me. Your question, and my response, really dealt with relational database design theory, which I doubt was part of your MOUS curriculum.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Searching Multiple Entries in the same Field (2000 SP2)

    Hopefully, you got the help you needed and all is well. I was offline all weekend and didn't get a chance to follow up on your question.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MOUS

    The MOUS test did cover some of relationships - and I failed miserably in that section. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Fortunately, I did really well in the other sections, so I passed the test nicely. I had hoped that I would never have to deal with them again, but every database I've had to create since them has needed lots of relationship stuff in it. <img src=/S/drop.gif border=0 alt=drop width=23 height=23> <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    It's amazing how many other things I've forgotten about Access since I studied for and passed that dumb ol' test. It doesn't help that my husband and brother are Access programmers - they don't know how to dumb it down to my level. But I can run circles around them in Word and Excel. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks for the help!
    Becky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •