# Thread: Display next record in a query,and apply conditions

1. Registered User
Join Date
May 2012
Posts
2

## Unanswered: Display next record in a query,and apply conditions

Hi, first post!
Goal: To display the next "Step" and next "Pay Rate" from Table2.

Table1 has the following fields: Schedule, Pay Grade, Step, SUM of worked Hours, and Pay Rate.

Table1 is joined to Table2 by Schedule, Pay Grade, and Step.

Table2 has the following fields: Schedule, Pay Grade, Step, HOURS to Next Step, and Pay Rate. I need to be able to show that says:IF Schedule, Pay Grade, and Step in Table1 match Schedule, Pay Grade and Step in Table2, then then display the next step and next pay rate in Table2, otherwise "".

Table 1
Schedule Pay Grade Step Hours Pay Rate
NonContract 200 5 450 \$12.50

Table 2
Schedule Pay Grade Step Hours Pay Rate
NonContract 200 1 2080 \$10.00
NonContract 200 2 2080 \$11.25
NonContract 200 3 2080 \$11.75
NonContract 200 4 2080 \$12.00
NonContract 200 5 2080 \$12.50
NonContract 200 7 4160 \$13.50
NonContract 200 10 6240 \$15.75

Any help would be much appreciated. I have over 6300 records where I need to find the next step and next pay rate, so having some automation or formula applied here would be most helpful.

2. Moderator
Join Date
Mar 2009
Posts
5,442
As you don't clearly state what the index(es) on both tables can be, I can only provide a solution from the sample data you posted.

You could try:
Code:
```SELECT TOP 1 [Table 2].Step,
[Table 2].[Pay Rate]
FROM         [Table 1] INNER JOIN
[Table 2] ON ([Table 1].Schedule = [Table 2].Schedule) AND
WHERE       ([Table 2].Step > [Table 1].[Step])
ORDER BY     [Table 2].Step;```

3. Registered User
Join Date
May 2012
Posts
2

## Display next record in a query,and apply conditions

This was not working, so I researched how to do this in Excel to start with and I will eventually migrate this to Acess or Crystal once I figure out how it translates in those environments. In case you are curious, here is what my final formula looks like to get the next step, and it works perfectly:
=IF(R3="Max","Max",INDEX('Sched-Grade-Step-Hours-Rates'!D,1+MATCH(K3&L3&M3,'Sched-Grade-Step-Hours-Rates'!G:G,0))). The tables names are different than in my example above, but same concept. I use this same formula to get the next pay rate, except I changed the columns D and G:G to the columns where the pay rates were sitting in each table. Now, if you know how to translate this formula in an Access query, you'd be my best friend today!

I see there are smiley faces where formulas are suppose to be, both smiley faces are referencing column D, hense, smiley face.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•