multiple criteria in query using three tables

[trainbow2]

I haven't worked with Access in a decade, so I'm rusty.
I have a table of wage increases that reflect a compensation polocy of
raises; table titled Raises.
An employee's raise is a function of (a) wage type and (b) step level
[a.k.a. seniority classification].
An employee's wage type is in the Staff table.
An employee's Step Level is in the Senority table.
Staff and Senority are linked by "Employee #".
I have a qury that can link Staff and Senority to present the wage type and
the step level.
What I am having difficulty with is how to get from those elements to the
correct raise they are due.
What's the process for selecting the correct raise from Raises based on the
wage type from Staff and the step level from Senority?

Dave

 

[MGFoster]

I haven't worked with Access in a decade, so I'm rusty.
I have a table of wage increases that reflect a compensation polocy of
raises; table titled Raises.
An employee's raise is a function of (a) wage type and (b) step level
[a.k.a. seniority classification].
An employee's wage type is in the Staff table.
An employee's Step Level is in the Senority table.
Staff and Senority are linked by "Employee #".
I have a qury that can link Staff and Senority to present the wage type and
the step level.
What I am having difficulty with is how to get from those elements to the
correct raise they are due.
What's the process for selecting the correct raise from Raises based on the
wage type from Staff and the step level from Senority?

Dave

Depends on what's in Raises and what the compensation policy formula is?

Describe the Raises table and some sample data & what you expect to get
from the query.

 

[trainbow2]

Raises table:
three columns
Column 1: Step (in hours worked)
Column 2: Wage Type (Dept, Shift or Staff)
Column 3: Raise (in decimal)
There are 18 rows; each cluster of 6 rows is for a different Wage Type.

Ex:
Step /Wage Type / Raise
1000 / Dept / 0.0785
4000 / Staff / 0.045

Each emplyee has a Step (assigned by one table) and a Wage Type (assigned
from a different table).
I have a query to display an employee's Step and Wage Type.
I also want it to display the associated Raise for that Step level and Wage
Type.
That way, I can inform management about proected raises for the upcoming
fiscal year.

So, my goal is to display the Raise fr each emplyee based on two factors:
Step and Wage Type.

Thanks for reviewiong this!

Dave

I haven't worked with Access in a decade, so I'm rusty.
I have a table of wage increases that reflect a compensation polocy of
raises; table titled Raises.
An employee's raise is a function of (a) wage type and (b) step level
[a.k.a. seniority classification].
An employee's wage type is in the Staff table.
An employee's Step Level is in the Senority table.
Staff and Senority are linked by "Employee #".
I have a qury that can link Staff and Senority to present the wage type and
the step level.
What I am having difficulty with is how to get from those elements to the
correct raise they are due.
What's the process for selecting the correct raise from Raises based on the
wage type from Staff and the step level from Senority?

Dave

Depends on what's in Raises and what the compensation policy formula is?

Describe the Raises table and some sample data & what you expect to get
from the query.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably, a query like this:

SELECT Sf.employee_no, Sr.step_level, Sf.wage_type, R.raise
FROM (Seniority As Sr INNER JOIN
Staff As Sf
ON Sr.employee_no = Sf.employee_no) INNER JOIN
Raises As R
ON Sr.step_level = R.step AND Sf.wage_type = R.wage_type
ORDER BY Sf.employee_no, Sr.step_level, R.raise DESC
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9BaBoechKqOuFEgEQJZGACePiLbR6bqwnFR6z5G4VAyqQD8iJ0AniLJ
/OGAMQ52PobNAhgWlsVrjdqJ
=oRaK
-----END PGP SIGNATURE-----

Raises table:
three columns
Column 1: Step (in hours worked)
Column 2: Wage Type (Dept, Shift or Staff)
Column 3: Raise (in decimal)
There are 18 rows; each cluster of 6 rows is for a different Wage Type.

Ex:
Step /Wage Type / Raise
1000 / Dept / 0.0785
4000 / Staff / 0.045

Each emplyee has a Step (assigned by one table) and a Wage Type (assigned
from a different table).
I have a query to display an employee's Step and Wage Type.
I also want it to display the associated Raise for that Step level and Wage
Type.
That way, I can inform management about proected raises for the upcoming
fiscal year.

So, my goal is to display the Raise fr each emplyee based on two factors:
Step and Wage Type.

Thanks for reviewiong this!

Dave

:

I haven't worked with Access in a decade, so I'm rusty.
I have a table of wage increases that reflect a compensation polocy of
raises; table titled Raises.
An employee's raise is a function of (a) wage type and (b) step level
[a.k.a. seniority classification].
An employee's wage type is in the Staff table.
An employee's Step Level is in the Senority table.
Staff and Senority are linked by "Employee #".
I have a qury that can link Staff and Senority to present the wage type and
the step level.
What I am having difficulty with is how to get from those elements to the
correct raise they are due.
What's the process for selecting the correct raise from Raises based on the
wage type from Staff and the step level from Senority?

Dave

Depends on what's in Raises and what the compensation policy formula is?

Describe the Raises table and some sample data & what you expect to get
from the query.

 

[trainbow2]

Here's the query sql with all of the database table names inserted and other
query elements included:

SELECT [Step hours].[Employee #], Staff.[Last Name], Staff.[First Name],
Staff.Position1, Staff.Supervisor1, Staff.[Wage Type], Staff.Wage,
Staff.[Biweekly hours], [Step hours].[Hours carried over], [Step hours].Q1,
[Step hours].Q2, [Step hours].Q3, [Step hours].Q4, [Step hours].[Current step
level], [Step hours].[Next step level], [q1]+[q2]+[q3]+[q4]+[hours carried
over] AS [Total Hours], IIf([total hours]+80>[next step level],"Raise
Due","") AS [Raise Due], Raises.Raise
FROM ([Step Hours] INNER JOIN
ON [Step Hours].[Employee #] = Staff.[Employee #]) INNER JOIN
ON [Step Hours].[Current Step Level] = Raises.step AND Staff.[Wage
Type] = Raises.[Wage Type]
ORDER BY Staff.[Employee #], [Step Hours].[Current Step Level], Raises.raise
DESC;

I get a syntax error in the FROM statement; the first ON gets highlighted.
I can't see the problem.

Dave

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's the correct FROM clause (read the Access Help on "From clause" or
a good book on SQL for more info on how to set up a query):

FROM ([Step Hours] INNER JOIN Staff ON [Step Hours].[Employee
#]=Staff.[Employee #]) INNER JOIN Raises ON [Step Hours].[Current Step
Level]=Raises.Step AND Staff.[Wage Type]=Raises.[Wage Type]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9FJCYechKqOuFEgEQJyOgCgkFQOwmJ3Ki8hmklDl92iJ8iGga4AoObC
pwRVHH0quuF/YT+HBIpI6efu
=22QL
-----END PGP SIGNATURE-----

Here's the query sql with all of the database table names inserted and other
query elements included:

SELECT [Step hours].[Employee #], Staff.[Last Name], Staff.[First Name],
Staff.Position1, Staff.Supervisor1, Staff.[Wage Type], Staff.Wage,
Staff.[Biweekly hours], [Step hours].[Hours carried over], [Step hours].Q1,
[Step hours].Q2, [Step hours].Q3, [Step hours].Q4, [Step hours].[Current step
level], [Step hours].[Next step level], [q1]+[q2]+[q3]+[q4]+[hours carried
over] AS [Total Hours], IIf([total hours]+80>[next step level],"Raise
Due","") AS [Raise Due], Raises.Raise
FROM ([Step Hours] INNER JOIN
ON [Step Hours].[Employee #] = Staff.[Employee #]) INNER JOIN
ON [Step Hours].[Current Step Level] = Raises.step AND Staff.[Wage
Type] = Raises.[Wage Type]
ORDER BY Staff.[Employee #], [Step Hours].[Current Step Level], Raises.raise
DESC;

I get a syntax error in the FROM statement; the first ON gets highlighted.
I can't see the problem.

Dave

:

 

[trainbow2]

I had reviewed Access Help, but was blind to my errors.
Thank you for your patience.