Consecutive Numbers

[Eddie Mason]

Hi All,

I apologise if what I am asking cannot be done.

I want to show a range from 1 to 1500 on a table with the minimum and maximum on each record running consecutively.
Eg:

Table 1 Minimum Maximum
Record 1 1 149
Record 2 150 249
Record 3 250 349 etc.

As the range in each record and the number of records can be controlled by the user, I believe the best way t guarantee that records run consecutively is to have a maximum field only on the table, and calculate the minimum field by adding 1 to the previously highest record. Is this possible and if it is can a lookup be done from the calculated range as follows?

Table 1 Maximum Grade
Record 1 149 A
Record 2 249 B
Record 3 349 C

Table 2 Score Look-up Grade
Record 1 27 A
Record 2 125 A
Record 3 272 C

If what I’m asking is not possible can someone suggest a workaround?

Regards,

Eddie

 

[dcx693]

Yes, this is all possible. You can use the Dmax function to calculate the largest maximum value and add 1 to it to create the next minimumn value. Then allow the user to key in the next maximum.

The lookup you desire can definitely be done. The "classic" usage for this type of lookup is student grades and marginal tax rate lookups.

The way to do the lookup is to create a normal select query using your two tables. First, have your table of data like this:
Grade
27
125
272
Bring down that [Grade] field to the QBE grid. I'll assume you're calling it [tblData].[Grade].

Then add your table of grade ranges to your query. The data should look like this:
Record 1 1 149 A
Record 2 150 249 B
Record 3 250 349 C
Bring down the letter grade field to the QBE grid. I'll assume you're calling it [tblGrades].[LetterGrade].

Do NOT join the tables using a normal join (by drawing a line between them).

Instead, use this criteria for the [tblData].[Grade] field: Between [tblGrades].[Minimum] And [tblGrades].[Maximum]

 

[Eddie Mason]

Many thanks for your help with this, it is now doing exactly what what I want it do, that is the grade is being changed when the score is edited. Again many thanks for your help.

Regards

Eddie