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 "".
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.