Need with database

[dharmacloud]

I'm trying to complete this project on student records database. However, I'm a bit stuck on a few problems. Anyone willing to look at my database and offer some help?



many thanks in advance!

[Robeen]

Do you have some specific questions that you can post?

I'll certainly try & help.

[TheShabz]

I do believe that is precisely what this forum is designed for. You post a question and we Google it on your behalf (KIDDING!). What are you stuck on?

[dharmacloud]

I have an SQL that is currently a UNION JOIN but need to change it to LEFT JOIN. How to go about doing this? Here's the SQL:

SELECT Register.Reg_ID, Register.CourseOfferingsID, Register.StudentProgramID,
                StudentProgram.StudentID, StudentProgram.ProgramID, Register.RegisterDate, Register.RegisterUnit,
                Register.RegisterGrade,
                Register.RegisterGradingBasis, Register.RegisterType, Register.RegisterNote, 0 AS Transferred
FROM Register, StudentProgram
WHERE Register.StudentProgramID = StudentProgram.StudentProgramID and Register.TxStudentProgramID is Null

UNION SELECT Register.Reg_ID, Register.CourseOfferingsID, Register.TxStudentProgramID as StudentProgramID, 
                StudentProgram.StudentID, StudentProgram.ProgramID, Register.RegisterDate, Register.RegisterUnit,
                Register.RegisterGrade,
                Register.RegisterGradingBasis, Register.RegisterType, Register.RegisterNote, 1 AS Transferred
FROM Register, StudentProgram
WHERE Register.TxStudentProgramID = StudentProgram.StudentProgramID;

[Robeen]

One really quick way would be for you to split the Union query into two separate queries.

1. Create a new query.
2. Don't select a table. Close the 'Show Table' dialog box & SQL View.
3. In the SQL window - paste the first part of the Union query.
4. Save & name the query.

Then create another query in the same way for the second part of the Union query.

Create a 3rd query in Query Design mode and select the two above queries that you created.

In the graphical interface - create the Left Join.

I'm not sure if this is what you really WANT to do. If it is not, give us some more details.

[dharmacloud]

For my student records database, I need help to figure out how I should go about creating tables and queries for course registration. The complicated part of the course registration is keeping track of the students and their program of study and the courses that they take during that program. For example, a student in a bachelor's program for 4 years takes X number of courses. After she graduates, she then continues to take courses but is no longer under the bachelor program. Her status is "non-degree" or "special". The courses she takes under the special status would need to be identified as such. She then enters the Master's program and all her courses taken as MA status need to be tracked.

The reason I need to make sure that the courses is tracked by the program is because students are allowed to transfer the courses which they take under "special" status into the degree program (bachelor or masters). That's the other complicated part. How should I go about creating a table which allows the user to select the courses that they want to transfer into the degree programs. Not all courses are transferable. The limit is 12 units of coursework can be transferred. So, I need to be able to choose which courses to transfer into the degree program.

Currently, I have the following tables:
1. Courses [CourseID, coursecode, courseName, Course Unit, course type]
2. Courses Offered [CourseOfferedID, CourseID, CourseName, Year, Term, instructor, location]
3. Student [studentID, name, address, gender, DOB, SSN, Date enrolled, ethnicity, student number]
4. Program [ProgramID, ProgramName, ProgramAbbreviation, Level]
5. Student Program [studentProgramID, studentID, ProgramID, date start, date end]
6. Register [RegisterID, StudentProgramID, CourseOfferedID, register date, register unit, register grade, register grading basis.]

Queries:
1. CourseRegistration [registerID, studentID, studentprogramID, coursename, courseID, register unit, register date, register grade, register grading basis, course term, course year]
2. student program [studentprogramID, studentID, ProgramID, date start, date end] date end is null to show only the current program that the student is in right now.

Please give me suggestions on how to go about this process.

[Robeen]

Where are you recording the Student's Status?

[dharmacloud]

the student status will be in the student program table

[dharmacloud]

The student program table will have the start date and end date of the program(s) that they have been in or the current program that they are in. For example:
John Doe start date end date
bachelor 6/1/1997 to 6/5/2001
Special 9/1/2001 to 6/5/2002
Master 6/1/2002 to

Not sure if that's the best way to do it but I was thinking that I could then make other queries based on this. The current program that the student is in does not have an end date. So when I query for the current program, then I just put Is null under the end date criteria.