Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Irish Girl in need of help!

Posted on 2002-03-22
7
Medium Priority
?
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Accepted Solution

by:
mdougan earned 200 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

730 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