advice about using Dlookup or not

[CarlyS]

I have several tables of test scores(Test1, Test2, etc.). I want the report to contain these scores as well as their percentile ranks and descriptions. I have created a table of all the possible scores and their corresponding percentiles and descriptions(NormsTable). I have been having problems (inconsistently) with the report and I am wondering if I should change the query that it is based on to improve the report's performance.
Right now I have a query for each test with the following fields:
Test1Query:
T1Score1 (from Test1 table)
T1S1Percentile: Dlookup([Percentile],NormsTable,Test1!T1Score1=NormsTable!StandardScore)
T1S1Description: Dlookup([Description],NormsTable,Test1!T1Score1=NormsTable!StandardScore)

And so on for all the scores in Test1. Then there is another query of this sort for Test2 and so on. These queries form the control source for my subreports on my ScoreReport.

If I used query joins instead of Dlookup I would have to draw relationships from each score in the Test tables to the the StandardScore field in the NormsTable, which would mean making a new query for each field, essentially. Is this still preferable to Dlookup or is there another alternative I haven't thought of?
Thanks for any advice!

 

[Rich]

You'd find it much easier if you normalised your db. Search here for articles on normalis(z)ation

 

[Pat Hartman]

It is the DLookup() that runs a query for every test for every row in the recordset! The joins will be far more efficient. However, because your table is not normalized, you may run into join limits in the query. As Rich suggested, your best bet is to normalize the tests table. It should end up looking something like:

tblTestResults
TestID (primary key fld1, foreign key to tblTests)
StudentID (primary key fld2, foreign key to tblStudents)
Score

Then your query for ALL tests would look like:
Select t.StudentID, t.TestID, t.Score, p.Description, p.Percentile
From tblTests as t Inner Join tblPercentile as p on t.Score = p.Score;

If you wanted only a specific test, you would include a where clause.
Select t.StudentID, t.TestID, t.Score, p.Description, p.Percentile
From tblTests as t Inner Join tblPercentile as p on t.Score = p.Score
Where TestID = [enter test id];