Reports

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

I have a database that keeps record of a series of 12
tests done by students. There is one main table for the
test records that records the student's basic information
as well as the scores for each test and the date each
test was taken. I need to print a report that lists all
the students who took tests on a certain date and the
tests that were taken that day. That is, it would list
information for 22 October 2003 as:

John
Test 1 - 100%
Test 2 - 100%

Bill
Test 4 - 100%

Rob
Test 8 - 100%
Test 9 - 100%

Can anyone tell me how I can do this? I've designed a
report, but so far it just lists all the students in the
database and all the test scores.
 
Please provide your table structure. I hope you don't have repeating columns
but I can tell from what you have stated.
 
The table fields are as follows:

StudentID - Autonumber (Primary Key)
Student - Text
Test1Score - Text
Test1Date - Date/Time
Test2Score - Text
Text2Date - Date/Time . . .

and so on for 12 Tests.

I have figured out how to build a query that is linked to
the report. I enter a date in one form [Date_Form] which
runs the query and loads the report. The date I enter
[txtDate] is supposed to be the date in which all the
tests I want to be printed were taken. However, the
query returns all the test scores for the all the
students where any one of the their test dates equals the
date entered. The query searches for all the student
name, test score, and date fields from the table and
returns the values where Test1Date = [Forms]![Date_Form]!
[txtDate] or Test2Date = [Forms]![Date_Form]![txtDate]...
etc.

Thanks for your help,
Nathan
 
Your table isn't normalized which causes your issues. There should be a
table of test scores that has one record per test score. I wouldn't do much
more work on the application until (and if) it was normalized. If you can't
normalize, then you can create a union query like:
SELECT StudentID, 1 As TestNum, Test1Score as TestScore, Test1Date as
TestDate
FROM tblTests
UNION ALL
SELECT StudentID, 2, Test2Score, Test2Date
FROM tblTests
UNION ALL
SELECT StudentID, 3, Test3Score, Test3Date
FROM tblTests
UNION ALL
etc.

Then use the union query as the source for your report.

Duane Hookom
MS Access MVP


The table fields are as follows:

StudentID - Autonumber (Primary Key)
Student - Text
Test1Score - Text
Test1Date - Date/Time
Test2Score - Text
Text2Date - Date/Time . . .

and so on for 12 Tests.

I have figured out how to build a query that is linked to
the report. I enter a date in one form [Date_Form] which
runs the query and loads the report. The date I enter
[txtDate] is supposed to be the date in which all the
tests I want to be printed were taken. However, the
query returns all the test scores for the all the
students where any one of the their test dates equals the
date entered. The query searches for all the student
name, test score, and date fields from the table and
returns the values where Test1Date = [Forms]![Date_Form]!
[txtDate] or Test2Date = [Forms]![Date_Form]![txtDate]...
etc.

Thanks for your help,
Nathan
-----Original Message-----
Please provide your table structure. I hope you don't have repeating columns
but I can tell from what you have stated.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
Back
Top