Solved Table set up (1 Viewer)

[SuperSonicSam]

Hi, I want to produce a report exactly like the one in attachment 1. I also want the associated subform to look like the handdrawn outline shown in attachment 2. (N.B. There are a few fields shown on the report but not on the form outline e.g . The 'National Benchmark' field which will be automatically populated (when year level selected) and the 'School Year Level Rank' field which would be automatically calculated.) Could someone please provide some advice as to how to set up this table/query. I'm thinking the fields in the table design view will look like this:

EnglishID
Year level (1-6)
Timepoint (semester 1 or semester 2)
General Test Type
General Achievement Level
General National Benchmark
General School Year Level Rank
General Test Date
Reading Test Type
Reading Achievement Level
Reading National Benchmark

etc...

Is that correct? I also don't know how to manage the form data input setup as for every year level and timepoint (e.g. Grade 4, Semester 1) there should only be one set of 4 test results (e.g. General, Word Building, Lang skills & Reading). Advice appreciated!

 

[XPS35]

You should certainly not store the data you mention in one table.
Moreover, what you show is probably not the whole story. There are probably more subjects than English and I think you want to record the results of students.
So start with a good database design with tables like:
- subject
- testing type
- student
- result
- year level
- time point
Next thing is to relate these tables.

 

[Pat Hartman]

Once the table is properly normalized, you can use a crosstab query to pivot the data so you see the three level in the same row.