Query Prob

[jkjmwilliams]

I've been having a problem with billing in that when I run a query,
registrations for courses taken and billed previously show up on the query. I
am even to the point of restructuring my database to solve this problem. Even
after restructuring, these courses from previous billing periods show up.
Please tell me what I'm doing wrong. Here's what I have:

Student Info Table:
RecNum
StudID (primary Key)
all other personal info





Courses Taken Table
RecNum (Primary Key)
StuID (Foreign Key)
Course1
Course2
Course3
MethodOfStudy1
MethodOfStudy2
MethodOfStudy3
RegistrationDate1
RegistrationDate2
RegistrationDate3

I also have payment info and CompletionInfo, but my main problem is with
these two tables. When I run a query, courses that students have taken
previously and paid for previously show up. Here's the query:


SELECT PLE_StudentInfo.StuID, PLE_StudentInfo.Agency,
PLE_CoursesTaken.Course1, PLE_CoursesTaken.Course2, PLE_CoursesTaken.Course3,
PLE_CoursesTaken.Course4, PLE_CoursesTaken.Course5,
PLE_CoursesTaken.RegistrationDate1, PLE_CoursesTaken.RegistrationDate2,
PLE_CoursesTaken.RegistrationDate3, PLE_CoursesTaken.RegistrationDate4,
PLE_CoursesTaken.RegistrationDate5
FROM PLE_StudentInfo INNER JOIN PLE_CoursesTaken ON PLE_StudentInfo.StuID =
PLE_CoursesTaken.StuID
WHERE (((PLE_StudentInfo.Agency)="SECU") AND
((PLE_CoursesTaken.RegistrationDate1) Between #9/1/2008# And #9/30/2008#)) OR
(((PLE_StudentInfo.Agency)="SECU") AND ((PLE_CoursesTaken.RegistrationDate2)
Between #9/1/2008# And #9/30/2008#)) OR (((PLE_StudentInfo.Agency)="SECU")
AND ((PLE_CoursesTaken.RegistrationDate3) Between #9/1/2008# And
#9/30/2008#)) OR (((PLE_StudentInfo.Agency)="SECU") AND
((PLE_CoursesTaken.RegistrationDate4) Between #9/1/2008# And #9/30/2008#)) OR
(((PLE_StudentInfo.Agency)="SECU") AND ((PLE_CoursesTaken.RegistrationDate5)
Between #9/1/2008# And #9/30/2008#));

The results show the students entire history of courses taken in previous
months. I only want what they have taken this month. Can you help?

 

[Jerry Whittle]

It's your Courses Taken Table and its 1,2,3 fields. This is causing you to
create complex AND/OR statements in the Where clause. Very like you have some
sort of logic flaw or the data is just bad.

What you probably need is a bridging (AKA linking or joining) table between
Student and Courses as one student can take many courses and a course can
have many students. Therefore this Many-to-Many relationship needs to be
broken up into two 1-to-Many relationships.

 

[jkjmwilliams]

I'm sorry, but I don't quite understand. Can you give me an example of what
the structure might be?
JoAnn

 

[Jerry Whittle]

Take a look at this 2003 Classroom Management Database Template from MS.

http://office.microsoft.com/en-us/templates/TC010184071033.aspx?CategoryID=CT101426031033