Solved

Irish Girl in need of help!

Posted on 2002-03-22
7
178 Views
Last Modified: 2010-05-02
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
Comment
Question by:Heads
7 Comments
 
LVL 15

Expert Comment

by:lyonst
ID: 6890071
What code have you written so far ??
0
 
LVL 24

Expert Comment

by:SunBow
ID: 6890223
> 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
 
LVL 18

Expert Comment

by:mdougan
ID: 6894422
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Accepted Solution

by:
mdougan earned 50 total points
ID: 6894484
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7634280
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
 
LVL 24

Expert Comment

by:SunBow
ID: 7636257
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
 
LVL 1

Expert Comment

by:kodiakbear
ID: 7666185
Moving to the Paq

kb
Experts Exchange Moderator
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now