Searching Multiple Entries in the same Field (2000 SP2)

Topic

New Reply

I am trying to create a new Access database, and I have one question (so far). 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

Reply | Quote

Replies

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…

Reply | Quote

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 , but that’s not hard to do! 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

Reply | Quote

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)

Reply | Quote

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

Thanks again,
Becky

Reply | Quote

Excuse my ignorance, but what is MOUS?

Reply | Quote

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

Reply | Quote

>>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.

Reply | Quote

The MOUS test did cover some of relationships – and I failed miserably in that section. 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.

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.

Thanks for the help!
Becky

Reply | Quote

An example of what mark said is attached. see Meetings.mdb

Reply | Quote

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.

Reply | Quote