hi, im basically looking some advice here for a small project we have.
basically im looking at automating an attendance register
in short want i want to do i build a matrix of students attendance on a
course. this table of data will then be used to populate a Datagrid. Course
Duration can vary but i will only ever report on the attendance for any one
course at a time. basically i will pass in the courseID as a parameter to
return the attendance for all the students on that course
what all would i need?
tbl_Students.(StudID, Name)
tbl_Course (CourseID, CourseDesc, CourseDuration)
tbl_Attendance(ID, StudID, CourseID, W
Num, Attended)
what im thinking is that i will need a record for each student for each w
the course runs
im getting a bit stuck on how i would return the records in the way i want
Student Name W
1, W
2 etc......
Craig G 1 0 1 1 1 1 1
Jim B 1 0 0 1 1 1 1
Sam A 0 1 1 1 1 1 1
basically i would like my output to be as above where each W
is a column
in a temp_table. and the 1 & 0 simply represent whether they attended or not
This may not be the best way to do it, its only the early stages of the
project so i am open to an ideas on how you would go about it
Cheers for the help,
CraigCross tabulations are best done using a report writer or a client tier
programming language. To get this done as a resultset, refer to:
http://support.microsoft.com/?scid=kb;EN-US;q175574
Anith|||On Tue, 22 Feb 2005 21:28:14 -0000, Craig G wrote:
>hi, im basically looking some advice here for a small project we have.
>basically im looking at automating an attendance register
(snip)
>This may not be the best way to do it, its only the early stages of the
>project so i am open to an ideas on how you would go about it
Hi Craig,
For the cross-tab query, check out the link Anith posted. But since
you're in the early stages of the project, allow me to post some
comments on your table design.
>what all would i need?
>tbl_Students.(StudID, Name)
Rename this table to "Students". No need to use the "tbl_" prefix; if
the name is in a FROM clause, you'll know it's a table anyway.
See if you can find a better key for students - something that can be
verified in the real world. The student's SSN would be ideal; you can
also check the school administration to find out if they have some
registration number.
>tbl_Course (CourseID, CourseDesc, CourseDuration)
The tbl_ prefix on this one should go as well. Most people prefer plural
names for tables, except for tables that hold only one row, so consider
renaming to "Courses".
Doesn't your school use mnemonic abbreviations for courses? These are
often an ideal candidate for primary keys: they are short enough, they
are easy verifiable and they are already in use and known.
>tbl_Attendance(ID, StudID, CourseID, W
Num, Attended)
W
Num? What will you do after december? If you start using w
1
again, you'll get duplicate data. I think you should add year as well,
or you should use a datetime column holding the first day of the w
(that way, you'll have date and year in the same column, the downside is
that you have to do some calculations to extract w
number).
I don't think you need a seperate ID column in this table. Just use the
compound primary key on StudID, CourseID, W
Num and Year.
Get rid of the Attended column. Only record the students that attend a
course. If there's no row for a student in a specific w
, (s)he did
not attend the course.
I also think you're missing a table. It seems logical that students
enlist for some courses, probably picking a starting w
. So you also
need a table
Enlistments (StudID, CourseID, StartW
, StartYear)
With (StudID, CourseID) the primary key. StudID and CourseID are each a
foreign key to Students resp. Courses. And the combination (StudID,
CourseID) in Attendance will be a foreign key to Enlistments.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I can't believe you are telling someone to use a user editable field as a
key. You need to do a little research on database design before you start
telling people how to screw up their design. Don't you know that SSN's can
be mistyped? Proper PK's should have NO real world meaning.
JIM
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke9r115eavtte6r7hjgpt6i14vjb5a3vke@.
4ax.com...
> On Tue, 22 Feb 2005 21:28:14 -0000, Craig G wrote:
>
> (snip)
> Hi Craig,
> For the cross-tab query, check out the link Anith posted. But since
> you're in the early stages of the project, allow me to post some
> comments on your table design.
>
> Rename this table to "Students". No need to use the "tbl_" prefix; if
> the name is in a FROM clause, you'll know it's a table anyway.
> See if you can find a better key for students - something that can be
> verified in the real world. The student's SSN would be ideal; you can
> also check the school administration to find out if they have some
> registration number.
>
> The tbl_ prefix on this one should go as well. Most people prefer plural
> names for tables, except for tables that hold only one row, so consider
> renaming to "Courses".
> Doesn't your school use mnemonic abbreviations for courses? These are
> often an ideal candidate for primary keys: they are short enough, they
> are easy verifiable and they are already in use and known.
>
> W
Num? What will you do after december? If you start using w
1
> again, you'll get duplicate data. I think you should add year as well,
> or you should use a datetime column holding the first day of the w
> (that way, you'll have date and year in the same column, the downside is
> that you have to do some calculations to extract w
number).
> I don't think you need a seperate ID column in this table. Just use the
> compound primary key on StudID, CourseID, W
Num and Year.
> Get rid of the Attended column. Only record the students that attend a
> course. If there's no row for a student in a specific w
, (s)he did
> not attend the course.
>
> I also think you're missing a table. It seems logical that students
> enlist for some courses, probably picking a starting w
. So you also
> need a table
> Enlistments (StudID, CourseID, StartW
, StartYear)
> With (StudID, CourseID) the primary key. StudID and CourseID are each a
> foreign key to Students resp. Courses. And the combination (StudID,
> CourseID) in Attendance will be a foreign key to Enlistments.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
