• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Irish Girl in need of help!

I've got a web based application where students submit which subjects they want to take for the next
semester and Administrators submit what subjects are available and what grade is required for each.
All this info is then stored in an Access Database then after a date specified by the administrator
which is also stored in database, I want a procedure to run which will allocate modules to the students
according to there Grade.

Any ideas, help or links?
Bearing in mind that my knowledge in this area is limited.

Thanks
0
Heads
Asked:
Heads
1 Solution
 
lyonstCommented:
What code have you written so far ??
0
 
SunBowCommented:
> Irish Girl in need of help!
;-)  TGIF, try Guiness!

> All this info is then stored in an Access Database

Thus, this is also an Access question. IMO more access than VB since Access can schedule better, is easy to patch, and has a scaled down version of VB with it.

So I recommend you also post in Access topic, 0 points, and ask them to help out here. If it turns out well from Access experts, you can always increase the points there. But you should provide them the link to this page, so here is one you can copy:

http://devx.experts-exchange.com/visualbasic/Q.20280533.html
0
 
mdouganCommented:
This is a fairly simple problem.  You have to define your tables in Access.  So, keeping it simple let's say we have the following tables:

Student
Name     SchoolYear     Semester      Grade   Subject  DateAdded
Bob      2002           Fall          11      Calculus 3/24/02
Sally    2002           Fall          10      Algebra  3/25/02
Tim      2002           Fall          11      Calculus 3/25/02

Subjects
Subject  Grade    MaxStudents
Calculus 11       20
Algebra  10       20
Pottery  9        10

StudentSubjects
Student  Subject  SchoolYear     Semester      
Bob      Calculus 2002           Fall
Tim      Calculus 2002           Fall
Sally    Algebra  2002           Fall


The only tricky part comes in writing the query to insert records into the StudentSubjects table.  Let's say that you want to keep historical information for past semester/years, then you will probably want to pass along the year/semester to insert to the query.  Your query will look like:

PARAMETERS SCHOOLYEAR NUMBER, SEMESTER TEXT;
INSERT INTO STUDENTSUBJECTS (STUDENT, SUBJECT, SCHOOLYEAR, SEMESTER) FROM (
SELECT A.Name,
       B.Subject,
       A.SchoolYear,
       A.Semester
FROM   Student A, Subjects B
WHERE  A.Subject = B.Subject
AND    A.Grade = B.Grade
AND    A.SchoolYear = [SCHOOLYEAR]
AND    A.Semester = [SEMESTER]
AND    B.MaxStudents > (
SELECT count(C.Student)
FROM   StudentSubjects C
WHERE  C.Subject = B.Subject
AND    C.SchoolYear = [SCHOOLYEAR]
AND    C.Semester = [SEMESTER])
)

If you save this query in the Access database, then it is possible to write code to run the query from an ASP page.

So, I'd assume that you'd have one ASP page to allow students to add records to the Student table, another to allow administrators to add records to the Subjects table, then you'd have a third ASP page that allows someone to run this query, passing the school year and semester.

(by the way, I haven't syntax checked the query above, as I'm making this up outside of the database, but if it doesn't syntax check, we can fix it pretty quickly).

Let me know if you need more information about how to actually call such a query and/or how to code the updates to the student and subjects table.
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mdouganCommented:
A quick test showed some minor problems with the query above.  This is the syntax that Access finally allowed:

PARAMETERS SCHOOLYEAR IEEEDouble, SEMESTER Text ( 255 );
INSERT INTO STUDENTSUBJECTS ( STUDENT, SUBJECT, SCHOOLYEAR, SEMESTER, DATEADDED )
SELECT A.Name, B.Subject, A.SchoolYear, A.Semester, A.DateAdded
FROM Student AS A, Subjects AS B
WHERE A.Subject = B.Subject
AND    A.Grade = B.Grade
AND    A.SchoolYear = [SCHOOLYEAR]
AND    A.Semester = [SEMESTER]
AND    B.MaxStudents > (
SELECT count(C.Student)
FROM   StudentSubjects C
WHERE  C.Subject = B.Subject
AND    C.SchoolYear = [SCHOOLYEAR]
AND    C.Semester = [SEMESTER])
ORDER BY A.DATEADDED;

I added a DateAdded column to the StudentSubjects table because I was trying to get the query to start scheduling students using the requests in the order that they were entered in the database.  The thought was that if the Max number of students for a class was reached, then you wouldn't schedule any more for the class.  However, it looks like the clause in the query that is checking to ensure that B.MaxStudents > .... doesn't work.

I'd hoped that as you are inserting rows, then each time, the "count" part of the query would return a higher number until at some point the count = the MaxStudents, and then it wouldn't allow any more inserts for that subject.  But, appearantly, the inserts are not committed until all records have been processed, so, the count is the same for all records as it is at the start.  But, if this isn't part of your requirement, then it will work as coded above, or, you can simplify it to:

PARAMETERS SCHOOLYEAR IEEEDouble, SEMESTER Text ( 255 );
INSERT INTO STUDENTSUBJECTS ( STUDENT, SUBJECT, SCHOOLYEAR, SEMESTER, DATEADDED )
SELECT A.Name, B.Subject, A.SchoolYear, A.Semester, A.DateAdded
FROM Student AS A, Subjects AS B
WHERE A.Subject = B.Subject
AND    A.Grade = B.Grade
AND    A.SchoolYear = [SCHOOLYEAR]
AND    A.Semester = [SEMESTER]
ORDER BY A.DATEADDED;

Depending on the version of Access you're using, you might have trouble with this line:

PARAMETERS SCHOOLYEAR IEEEDouble, SEMESTER Text ( 255 );

If so, just use the line as I first provided it above:

PARAMETERS SCHOOLYEAR NUMBER, SEMESTER TEXT;
0
 
DanRollinsCommented:
Hi Heads,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept mdougan's comment(s) as an answer.

Heads, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
SunBowCommented:
I agree that it appears that Heads has acknowledged benefit of support for the coding tasks, has since become unable to respond to the above, and concerning cleanup, here's the other of hers that remains on that subject:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20284049.html
                                            (...unsubscribing)
0
 
kodiakbearCommented:
Moving to the Paq

kb
Experts Exchange Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now