Table design

[crowegreg]

I need to create a table which contains rates per mile. An example would be Rate A, B, etc. Within each rate, for each mile between 1 to 500, their will be an individual amount. Within a form, the user will have a list of records which contains distances. The user will select a rate, then the correct rate per mile will be retrieved based on the distance. Here's the idea:



Rate A Rate B
1 .50 .55
2 .55 .58
3 .59 .64
4 .63 .65
5 .70 .72


On the form: user selects the Rate, I'll use B


Distance Rate

3 .64
5 .72
1 .55

With the mamimum number of fields within a table 255, I'm having trouble coming up with the most efficent design.

[John_G]

You need to look at using properly normalized data. Your table needs to contain only three fields:

Rate Code (A, B, ...)
Distance
Rate Paid (.72, .55, ....)

You could use DLookup to retrieve the values for any given Rate Code / Distance.

John

[crowegreg]

That's what I couldn't put my head around. Thanks