Link Meeting with Roster?

[Guinea]

Ok my goal is to create a database with this purpose: I am a member of an Honors program and want to keep a roster table accurate. I also want to create a table or form or something that I can document each meeting with a date...and document the topics discussed/and notes about the topic. I made a table for each meeting, with 3 columns (Topic, Minutes, Notes). I made the roster. Is there a way that I can tie in the roster to each meeting, so I can use check marks for each person on the roster whether they showed up for that meeting? Basically each meeting would pull first and last name info from the roster and allow me to keep track of rosters for every meeting.



With this, I could also set up forms to show which meetings people went to etc... is this possible? And how can I do it?

[Guinea]

Anyone have any ideas?

[RuralGuy]

Have you looked at any of the templates ship with Access or that MS has on-line for something close to what you want? Much easier to modify one of those than start from scratch.

[Guinea]

Great idea. I am using the Student List which fits perfectly for the roster. With this template, I can set up "Attendance" for specific dates. I would like to customize it though, so I can make each date link to a specific meeting. I could use the Events template for the specific meetings, but I can't figure out how to combine templates. The student template works perfectly for the roster because I can create new students easily....and then under each student, I can select attendance, and click a date....and select whether they were there or not.

Now how can I incorporate the Events template into this template? I want to create it so I can click a "New Event" button as well...create an event with details about the meeting, including time records etc (Which I can customize later of course). And then the student record would link to the meetings and for each meeting on the student record I would select either Present or Not present.

[RuralGuy]

One problem at a time. I'm not familiar with the templates so you will need to take it slow. You should be able to copy anything from one template to another. Have you tried that?

[Guinea]

Yea I tried to select all of the elements from the Events template (Forms, Reports, Tables etc) and copy them into the current Student database. When I do, it says "Could not use 'C:\Users\Chris\Documents\Events1.accdb'; file already in use." That file is the events database I am copying the elements from...I tried to close the database, but that would clear out my clipboard.

[Guinea]

Ok nevermind. I don't know how I figured it out, but I did. I just closed it down completely....then opened it fresh, copied all of the elements of the database and then pasted them to the other template as Structure only. So now I have both structures I want to use.

One is a student list. I can click the New student button and enter data (Phone number etc, Student ID etc..) And I can create an Event (Meeting) with details and start time etc... Now the New Student data entering screen has a tab for "Attendance" In it, I can select a date off a calendar and click Present or Not Present. That makes sense. How can I select a meeting instead of a date. So if I create an event for today (If we had a meeting) and that event will be referred by the date 28 August 2010. So when I go to the student sheet, I can select that meeting in one column and then select whether they were present in another column.

Basically I want to be able to have event details (we discuss topics in our meetings and I need to document them all with time slots..) but also need to have a roster on top of that to show who was present. So using this database, I would be able to establish a form or report that would show a roster for all the students who were PRESENT at the meeting...and then meeting details below that.

[RuralGuy]

You can also *import* from another db. You need to determine the relationship of your tables and then place your ForeignKey accordingly.

[Guinea]

Ok the problem I am having is this. For the events table, I set up a new entry for Date. Then under the Student Attendance table, there is also a Date column. I am trying to establish a relationship between the two tables in the regards of the Date. It is saying "No unique index found for the referenced field of the primary table."

I don't want this to be too complicated as I am a novice at this stuff. But I do want the functionality to be simple enough to use it quickly. How can I reference each meeting as a specific event. Like each meeting would have an ID whether it was a general or officer meeting. So Like GM1 and OS3 or something like that. Then when I went to the student attendance tab, I would select a meeting ID for the student and dictate whether they were present or not.

[Guinea]

Ok I figured one part out. I established each meeting's Primary key as an ID. Then set the format to "GM"###" to account for incrementing numbers. With this in mind, I will set up two identical tables, one for General Meetings and one for Officer meetings.

The question I have now is this: Under the New Student form to enter information there is a tab for attendance. Under there is 2 columns, one for a date and another for a Present or Not Present. I want to change the date column to pull data from the Meeting tables. So basically a drop down menu, I click it...and I can select a specific meeting idea, like GM1. Then when I select it a 2nd column displays the date corresponding with that meeting. And the third column would be the same Present/Not Present selection. Then I can go to the next row and do the same thing for a new meeting and it would keep a running tally of the meetings that one person has attended.

[RuralGuy]

I don't know the structures of your tables but I would strongly suggest each table have an AutoNumber as the one and only PrimaryKey of the table.

[RuralGuy]

Done properly, the user need never see the PrimaryKey of the table.

[RuralGuy]

I would think you might consider a MainForm to describe a meeting and a SubForm on that MainForm where you list those in attendence. Just a thought.

[Guinea]

Well I understand that, but I can fine tune later. I just want the basic functionality to work for now. Would a lookup table work for that column? I just want to be able to select individual meetings for a person's attendance tab and choose present or non present like this:

Meeting Date Attendance
GM1 08/25/2010 Not Present
GM2 08/27/2010 Present

The meeting ID would be selectable in that column through a drop down box and then the date would be automatically entered from the actual Meeting table data.

[RuralGuy]

What will your table for this function look like?