MS SQL Data Warehousing - Storing attendace

Author

Storing attendace - design question

Henrik

2006-03-16, 8:23 pm

I'm new to database design and I have a design question that I hope I can get
some ffedback on. I want to setup a table structure to accomplish the
following scenario:

The goal is to create a tracking system for tracking attendance in meetings
for members of a project group. I want the project manager to be able to
create meetings and report who attended the meeting. I then need to retrieve
who was on what meeting.

I have a table that holds the data of each memeber of the group (name,
position, ... ) and i thought of adding a "attended-meetings" field but how
do I handle the fact that the attended meetings field is acctually a number
of fields depending on how many meetings the member has attended.

I hope someone can give me some advice

Thanks in advance

Wei Lu

2006-03-17, 3:23 am

Hi henrik,

Welcome to use MSDN Managed Newsgroup Support.

Based on my experience, I'd like to give you some advice:
1. Create a table named tblAttendMeeting which store the data of
attendance of each meeting with the field: MeetingID, memberID. (I assume
you have a uniq ID for each Member in the group and a uniq ID for each
Meeting )

2. Using the following SQL statement to get the attended-meetings number
for each member.

SELECT memberID, COUNT(*) AS AttendMeetingNum FROM tblAttendMeeting GROUP
BY memberID

3. Also, you can use the UPDATE statement to update the attended-meetings
field.

you could first get the actual Attend meeting num for a member and then
update the field.

Hope this will be helpful.

Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Henrik

2006-03-17, 3:23 am

Thank you. I'll try that

Sponsored Links