Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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 
                              ([Table 1].[Pay Grade] = [Table 2].[Pay Grade])
    WHERE       ([Table 2].Step > [Table 1].[Step])
    ORDER BY     [Table 2].Step;
    Have a nice day!

  3. #3
    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
  •