I'm doing nothing special, just drop these tables into MS ACCESS visual designer mode.
To build this thing takes about 8 minutes...
I suggest have a look into ACCESS table and SQL designing.
Notice that, this is not a clean version, becouse you lost the sub-scores from the test results.
If you have a users table:
userid username
1 a
2 werr
3 rrr
You have a test_table table
test_id test_name
1 qwe
2 e
3 sdfsdf
4 ffsdf
And you have a scores table:
user_id test_id summed_score
1 1 4
1 1 66
1 1 323
1 2 3
2 1 3
2 1 444
2 2 3
A query, which selects all the scores joining the test_table and the users all details:
(the user can fill the tests unlimited times, and the scores will be aggregated)
SELECT users.username, users.userid, test_table.test_id, test_table.test_name, Sum(scores.summed_score) AS SumOfsummed_score
FROM users INNER JOIN (test_table INNER JOIN scores ON test_table.test_id = scores.test_id) ON users.userid = scores.user_id
GROUP BY users.username, users.userid, test_table.test_id, test_table.test_name;
The data is:
username userid test_id test_name SumOfsummed_score
a 1 1 qwe 393
a 1 2 e 3
werr 2 1 qwe 447
werr 2 2 e 3
How to select the high scores from these three tables:
SELECT users.userid, users.username, test_table.test_id, test_table.test_name, Max(scores.summed_score) AS MaxOfsummed_score
FROM users INNER JOIN (test_table INNER JOIN scores ON test_table.test_id = scores.test_id) ON users.userid = scores.user_id
GROUP BY users.userid, users.username, test_table.test_id, test_table.test_name;
userid username test_id test_name MaxOfsummed_score
1 a 1 qwe 66
1 a 2 e 3
2 werr 1 qwe 444
2 werr 2 e 3
