Get the next record after match

  • Thread starter Thread starter Marcie
  • Start date Start date
M

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.
 
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.
Click to expand...

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.
 
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)
 
Back
Top