Automatic inserts (given count variables from another table)

[m_houllier]

STUDENT TABLE
StudentReference [pk]
Student Name
etc
ATTENDANCE TABLE
AttendanceID [pk]
CourseID [fk]
StudentReference [fk]
Session_Date
Session_Start_Time,
Session_End_Time,
Total_Hours
COURSE TABLE
CourseID [pk]
CourseName
CourseDescription
HoursRequired

COURSE STUDENT LINK
CourseStudentLinkID [pk]
CourseID [fk]
StudentReference [fk]

It's work I've been doing for a learning center.
Voluntary stuff - but I said I'd get it working.

Anyway, originally, they just wanted to put students and what courses
they've been on. But they wanted to extend this to show the times of
sessions they're in and then if they've done enough hours for the course.

Anyway, there's about 1500 odd students, so attaching each student to each
course is going to be a nightmare. People have been entering session times
[to the attendance table]
in (data entry) over the summer - but because people are only assigned onto
a course if they've attended at least 1 sessions - then we get a problem.
So, what we need is.

Insert StudentReference CourseID (from the attendance table) into Course
Student Link IF there are >=1 occcurences of StudentReference CourseID.

SO for example,

if Dave Winchester has listed 1 or more attendance of a course grapefruit
engineering, then

Dave Winchester [StudentReferecence] and[ CourseID] need to be placed into
the COURSE STUDENT LINK table.

I've tried this

insert into Course_Student_Link (StudentReference, CourseID)
select StudentReference, CourseID
from Attendance_Table
where
(select count(distinct StudentReference, CourseID)
from Attendance_Table) => 1
insert into Course_Student_Link (StudentReference, CourseID)
select StudentReference CourseID
from Attendance_Table
where Total_Hours => 1
but the syntax is wrong :-(
Anyone got any ideas?

[Marc]

"m_houllier" <mh********************@nildram.co.uk> wrote in message news:<41***********************@mercury.nildram.ne t>...

STUDENT TABLE
StudentReference [pk]
Student Name
etc
ATTENDANCE TABLE
AttendanceID [pk]
CourseID [fk]
StudentReference [fk]
Session_Date
Session_Start_Time,
Session_End_Time,
Total_Hours
COURSE TABLE
CourseID [pk]
CourseName
CourseDescription
HoursRequired

COURSE STUDENT LINK
CourseStudentLinkID [pk]
CourseID [fk]
StudentReference [fk]

It's work I've been doing for a learning center.
Voluntary stuff - but I said I'd get it working.

Anyway, originally, they just wanted to put students and what courses
they've been on. But they wanted to extend this to show the times of
sessions they're in and then if they've done enough hours for the course.

Anyway, there's about 1500 odd students, so attaching each student to each
course is going to be a nightmare. People have been entering session times
[to the attendance table]
in (data entry) over the summer - but because people are only assigned onto
a course if they've attended at least 1 sessions - then we get a problem.
So, what we need is.

Insert StudentReference CourseID (from the attendance table) into Course
Student Link IF there are >=1 occcurences of StudentReference CourseID.

SO for example,

if Dave Winchester has listed 1 or more attendance of a course grapefruit
engineering, then

Dave Winchester [StudentReferecence] and[ CourseID] need to be placed into
the COURSE STUDENT LINK table.

I've tried this

insert into Course_Student_Link (StudentReference, CourseID)
select StudentReference, CourseID
from Attendance_Table
where
(select count(distinct StudentReference, CourseID)
from Attendance_Table) => 1
insert into Course_Student_Link (StudentReference, CourseID)
select StudentReference CourseID
from Attendance_Table
where Total_Hours => 1
but the syntax is wrong :-(
Anyone got any ideas?

I think you don't need a course_student_link table, if you generate it
dynamically using a Query of the following type: (in not fully
qualified form)

"Select StudentReference, CourseID, sum(Total_Hours), Hours_Required
from Attendance_table inner join Course_Table on
(Attendance_table.courseID = Course_table.CourseID)
Group by StudentReference, CourseID, Hours_Required
having sum(Total_hours) => 1"

or "having sum(total_hours) >= Hours_required" when only qualified
students should be filtered

A query in this form can be easely created without extensive SQL
knowledge through the query editor using the totals-option (the
sigma-button) and working out the filter- and aggregation options in
the QBE-fields.

The query above could be used in a create-table query and the
result-table could replace the link-table.

Marc

[Pieter Linden]

"m_houllier" <mh********************@nildram.co.uk> wrote in message news:<41***********************@mercury.nildram.ne t>...

STUDENT TABLE
StudentReference [pk]
Student Name
etc
ATTENDANCE TABLE
AttendanceID [pk]
CourseID [fk]
StudentReference [fk]
Session_Date
Session_Start_Time,
Session_End_Time,
Total_Hours
COURSE TABLE
CourseID [pk]
CourseName
CourseDescription
HoursRequired

COURSE STUDENT LINK
CourseStudentLinkID [pk]
CourseID [fk]
StudentReference [fk]

I think you're missing a table. First off, I would split
Session(SessionID, CourseID, SessionStartDateTime,SessionEndDateTime,
[totalHours]) off from Attendance.

Attendance might look like this:
CREATE TABLE Attendance(
SessionID,
StudentID,
....
PRIMARY KEY (SessionID, StudentID)
FOREIGN KEY (SessionID) REFERENCES (Session.SessionID),
FOREIGN KEY (StudentID) REFERENCES (Student.StudentID);

Then you'd record hours on the Class/Section level and not the student
level, unless they can get something like partial credit for going to
half the class.

[m_houllier]

The hours work,
I mean, a query based on StudentID, CourseID along with some calculations on
the form side provide the user with the information of how many hours have
been attended on each specific course and if they've passed (based on
hours).

In the way it works at the moment - the problem is creating this table of
course_student_link

I'll strip the data out of the database (data protection and all that), then
upload so you can see what I mean :-)

if you'd be kind enough to look that is :D

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf*************************@posting.google.co m...

"m_houllier" <mh********************@nildram.co.uk> wrote in message

news:<41***********************@mercury.nildram.ne t>...

STUDENT TABLE
StudentReference [pk]
Student Name
etc
ATTENDANCE TABLE
AttendanceID [pk]
CourseID [fk]
StudentReference [fk]
Session_Date
Session_Start_Time,
Session_End_Time,
Total_Hours
COURSE TABLE
CourseID [pk]
CourseName
CourseDescription
HoursRequired

COURSE STUDENT LINK
CourseStudentLinkID [pk]
CourseID [fk]
StudentReference [fk]

I think you're missing a table. First off, I would split
Session(SessionID, CourseID, SessionStartDateTime,SessionEndDateTime,
[totalHours]) off from Attendance.

Attendance might look like this:
CREATE TABLE Attendance(
SessionID,
StudentID,
...
PRIMARY KEY (SessionID, StudentID)
FOREIGN KEY (SessionID) REFERENCES (Session.SessionID),
FOREIGN KEY (StudentID) REFERENCES (Student.StudentID);

Then you'd record hours on the Class/Section level and not the student
level, unless they can get something like partial credit for going to
half the class.

[m_houllier]

For the time being, hours attained is calculated on the form side. For
instance, timediff is used to record how many hours each session was, and
then in the form they are added together

eg,

HEADER date of session, start time , end time, total hours
DATA 19/04/03 9am 12pm 3
DATA 20/04/03 10am 12pm 2 (late git!)
DATA 21/04/03 9am 12pm 3

Hours required 40 Hours attained 8

If the hours aren't attained I use conditional formatting (make it a really
mean red), and if they're inside then it's green.

"Select StudentReference, CourseID, sum(Total_Hours), Hours_Required
from Attendance_table inner join Course_Table on
(Attendance_table.courseID = Course_table.CourseID)
Group by StudentReference, CourseID, Hours_Required
having sum(Total_hours) => 1"
The last part of that Group by ..... (Total_hours) => 1" I'm not so sure
about. I was convinced that I'd need count or dcount as part of this
solution.

But I see where you're going with the sum(Total_Hours) and dynamic table
idea. I'll give that a bash as one deviation of attempted solutions :-)

cheers Marc,

"Marc" <M.***********@uva.nl> wrote in message
news:ae**************************@posting.google.c om... "m_houllier" <mh********************@nildram.co.uk> wrote in message

news:<41***********************@mercury.nildram.ne t>...

STUDENT TABLE
StudentReference [pk]
Student Name
etc
ATTENDANCE TABLE
AttendanceID [pk]
CourseID [fk]
StudentReference [fk]
Session_Date
Session_Start_Time,
Session_End_Time,
Total_Hours
COURSE TABLE
CourseID [pk]
CourseName
CourseDescription
HoursRequired

COURSE STUDENT LINK
CourseStudentLinkID [pk]
CourseID [fk]
StudentReference [fk]

It's work I've been doing for a learning center.
Voluntary stuff - but I said I'd get it working.

Anyway, originally, they just wanted to put students and what courses
they've been on. But they wanted to extend this to show the times of
sessions they're in and then if they've done enough hours for the course.
Anyway, there's about 1500 odd students, so attaching each student to each course is going to be a nightmare. People have been entering session times [to the attendance table]
in (data entry) over the summer - but because people are only assigned onto a course if they've attended at least 1 sessions - then we get a problem.

So, what we need is.

Insert StudentReference CourseID (from the attendance table) into Course
Student Link IF there are >=1 occcurences of StudentReference CourseID.

SO for example,

if Dave Winchester has listed 1 or more attendance of a course grapefruit engineering, then

Dave Winchester [StudentReferecence] and[ CourseID] need to be placed into the COURSE STUDENT LINK table.

I've tried this

insert into Course_Student_Link (StudentReference, CourseID)
select StudentReference, CourseID
from Attendance_Table
where
(select count(distinct StudentReference, CourseID)
from Attendance_Table) => 1
insert into Course_Student_Link (StudentReference, CourseID)
select StudentReference CourseID
from Attendance_Table
where Total_Hours => 1
but the syntax is wrong :-(
Anyone got any ideas?

I think you don't need a course_student_link table, if you generate it
dynamically using a Query of the following type: (in not fully
qualified form)

"Select StudentReference, CourseID, sum(Total_Hours), Hours_Required
from Attendance_table inner join Course_Table on
(Attendance_table.courseID = Course_table.CourseID)
Group by StudentReference, CourseID, Hours_Required
having sum(Total_hours) => 1"

or "having sum(total_hours) >= Hours_required" when only qualified
students should be filtered

A query in this form can be easely created without extensive SQL
knowledge through the query editor using the totals-option (the
sigma-button) and working out the filter- and aggregation options in
the QBE-fields.

The query above could be used in a create-table query and the
result-table could replace the link-table.

Marc