List person twice on report from same query

[Timoty]

I have a list of students that have to take a course. If they fail the course they get a second attempt. The course is taken at one of two locations. If they fail the first attempt they get a check under fail and then they are scheduled for a second attempt, not always at the same location. My problem is this:

I have a class report that lists the course first by location and then by date. This is done by filtering the query under Location_ID with “Like 1 Or Like 2 Or Like 3”

The numbers obviously correspond with the locations from a drop down list that is used to select the locations.

The report ends up looking like this:

Location A.
Date 1
Person 1
Person 2

Date 2

Person 1 etc.

Location B
Date 1
Person1
Person2

Date 2
Etc.

If a person fails the first course and then takes a second course, they only show up under the first class. I have to create a second query that filters the second location the same way.

How can I get the report to list a person twice so that they show up in the first class that they failed and the second class that they passed…or hopefully passed.

The table fields are listed below.

Surname, GivenName, Date, Location_ID, Passed, Failed, RescheduleDate, Newlocation_ID, 2ndPassed, 2ndFailed

Thanks

 

[wazz]

you need to break up your subjects (the 'subjects' (entities) of your database, not the subjects that are taught) into separate tables something like this:

tblStudent
StudentID (primary key)
Surname
GivenName

tblSubject
SubjectID (PK)
SubjectDesc

tblLocation
LocationID (PK)
LocationDesc

tblTest
TestID (PK)
SubjectID (foreign key)
Date
Location_ID (FK)

tblResult
ResultID (PK)
Result (example: pass, fail, flying colours, failed miserably, absent)

tblTestDetails
TestDetailsID (PK)
TestID (FK)
StudentID (FK)
ResultID (FK)

not necessary: RescheduleDate, Newlocation_ID, 2ndPassed, 2ndFailed
(i did this quickly. check for other feedback and search this forum, i'm pretty sure there are similar examples).

 

[Timoty]

Thanks, I will give it a whirl.