Should I split this table?

[sbooth]

I am using Access 2000.

Currently, I have the following table:

tblPointsChart
PointsNum_pk
EntryCountMin
EntryCountMax
PlacementNum
Points


It is used to assign points based on:

tblShowClassEntry
ShowClassNum_fk
EntryNum_fk
PlacementNum_fk

and qryEntriesPerClass that returns the number of entries in a class.

My question is should I keep tblPointsChart as one table? tblPointsChart does not link easily with qryEntriesPerClass and tblShowClassEntry.

I have used the following code to get the results I need:

SELECT tblShowClass.ShowClassNum_pk, tblShowClassEntry.EntryNum_fk, tblShowClassEntry.PlacingNum_fk, qryEntriesPerClass.CountOfEntryNum_fk, tblPointsChart.Points
FROM (tblPlacement INNER JOIN (tblPointsChart INNER JOIN (qryEntriesPerClass INNER JOIN tblShowClass ON qryEntriesPerClass.ShowClassNum_fk = tblShowClass.ShowClassNum_pk) 
ON ( qryEntriesPerClass.CountOfEntryNum_fk between tblPointsChart.EntryCountMin  and tblPointsChart.EntryCountMax))
ON tblPlacement.PlacementNum_pk = tblPointsChart.PlacementNum_fk) INNER JOIN (tblEntry INNER JOIN tblShowClassEntry ON tblEntry.EntryNum_pk = tblShowClassEntry.EntryNum_fk) ON (tblShowClass.ShowClassNum_pk = tblShowClassEntry.ShowClassNum_fk) AND (tblPlacement.PlacementNum_pk = tblShowClassEntry.PlacingNum_fk)
WHERE (((qryEntriesPerClass.CountOfEntryNum_fk) Between [tblPointsChart].[EntryCountMin] And [EntryCountMax]))
ORDER BY tblShowClass.ShowClassNum_pk, tblShowClassEntry.PlacingNum_fk;
 

I am just not sure how to change tblPointsChart to make this work more smoothly. Any suggestions would be greatly appreciated.

[Stewart Ross]

Hi. This is one occasion when you don't need to join the table at all. It is effectively a lookup table, and in this case you lookup the number of points to assign based on the entries per class count being between the specified minimum and maximum values. The WHERE clause of your SQL does the selection of a specific row (as there can only be one match if the table is set up correctly) regardless of the join. As you have found, joining the table on an artificial primary key value simply makes the query more complex.

There is an example of the use of an unjoined lookup table in this thread, complete with a simple zipped database with the table and a few queries.

-Stewart