Grouping tables together in one query to extend a field...

[akshatagarwal93]

Hi,



I suppose my title doesn't particularly makes sense but that's because I'm not sure how to describe and probabaly why I couldn't find anything similar to this...

I have a table which contains data on students eg: Name, Age. Another table consists of all the test scores throughout the year(s) for a particular student ie: there is one table per student. Therefore I made a relationship between all of the individual tables to the main one with the students name (as this is, for now, the primary key).

Every now and then, I have to print off a 'mark sheet' style report which is easy enough to design however I want it to consist of the student's name followed by his score on a particular test. Each report consists of the test scores for one test for all the students. Currently I have to go to each student's table and copy the respective test score into a new table and paste it next to the corresponding student's name; this is a very long and mistake-prone process.

My question then: Is it possible to design a query which picks out the students name and their test score for a certain test and puts them all 'underneath' each other in a record and if not what is the best way to do this? Should I put all the data into one table then query out individual tests?

I tried to make a simple table-style query below to try make it clearer...

Name Test 24 Score
John Smith 19
John Smith2 24
...... ..
...... ..
...... ..
...... ..

Thanks in advance
AA93

[jzwp11]

there is one table per student...Should I put all the data into one table then query out individual tests

All of the test scores should be in 1 table. That table should be joined to your student table. Like this:

tblStudents
-pkStudentID primary key, autonumber
-txtFName
-txtLName
other fields

tblStudentTestScores
-pkStuTestScoreID primary, key, autonumber
-fkStudentID foreign key to tblStudents (must be a long integer number data type field)
-score
-dteScore (a date field identifying the date of the test score)
other fields

You can then create a form based on the student table and then create a subform based on the score table.

[akshatagarwal93]

Are you sure there is no way to just append the data below one another?? The problem is that I don't directly enter the data into the DB. We import all the data from a text file into the database for each test.

[jzwp11]

Having a normalized table structure is critical to a succesful relational database application. You would base all of your forms, reports & queries on these tables. My recommendation is to create that normalized structure first. Then import your data into temporary tables and then use append queries to move the data into the normalized tables. Once the data has been moved, remove the temporary tables.