Get the next record after match

[Marcie]

I have 2 tables one has a salary structure with sequentail steps. The other
has personal information with current step. When a person meets certain
critieria they move up a step. Attached to that step is a dollar amount I
would like to get the new amount for forecasting purposes but I do not want
to change the step. I have a relationship built on salary structure step and
persons current step.

 

[John W. Vinson]

I have 2 tables one has a salary structure with sequentail steps. The other
has personal information with current step. When a person meets certain
critieria they move up a step. Attached to that step is a dollar amount I
would like to get the new amount for forecasting purposes but I do not want
to change the step. I have a relationship built on salary structure step and
persons current step.

What's the actual structure of your table? Please post the relevant fieldnames
and datatypes. I'm GUESSING that you can use a "Non Equi Join" - join the
employee table to the salary table; initially join Step to Step, but then edit
the SQL to something like

....
INNER JOIN SalarySteps
ON SalarySteps.Step = Employees.Step + 1
....

You can't do this in the query grid - use View... SQL. Post the SQL of the
simple join if this isn't helping.

 

[Marcie]

Thanks John, this almost worked. The only problem is I have one table which
contains current step and if they are not at the top step I create newstep.
IIf not 7 (which is max) they go up one, else stay at 7. I then have a
salary structure table that I want to match new step and get new salary. If
I match on step plus one it never matches because there is no step 8. So
what I really want to do is match on the iif created NewStep field. I tried
NewStep = SalaryStructure.Step and got an error. I then tried
CalcStep.NewStep = SalaryStructure.Step and got an error (CalcStep being the
name of the query).
Thanks again for your help (I did learn that you can do some expressions in
a join which I have never done before)