My problem is creating a "Salary Change" report that shows all
employees during a specific time period who have had a pay change. The
query below gets the current and previous salary for a specific
employee who had a change in the specified time period, but I haven't
figured out how to do this for all employees in that time period. I've
tried using "make table" queries, Joins (inner, left, right), etc. I'm
even open to using some programming code to do this, however, I'm a
novice with MS Access. Any suggestions you can provide or even
pointing me to specific resources would be greatly appreciated.


SELECT [dbo_PS_JOB].[FILE_NBR], [dbo_PS_JOB].[DEPTID], [dbo_PS_JOB].
[EFFDT], [dbo_PS_JOB].[ACTION], [dbo_PS_JOB].[ACTION_DT], [dbo_PS_JOB].
[ACTION_REASON], [dbo_PS_JOB].[COMPRATE] AS [Current],
(SELECT TOP 1 Dupe.COMPRATE FROM dbo_PS_JOB AS Dupe
WHERE Dupe.FILE_NBR = dbo_PS_JOB.FILE_NBR AND Dupe.EFFDT <
dbo_PS_JOB.EFFDT
ORDER BY Dupe.EFFDT DESC ,Dupe.FILE_NBR) AS PriorValue
FROM dbo_PS_JOB
WHERE ((([dbo_PS_JOB].[FILE_NBR])="112") And (([dbo_PS_JOB].[ACTION])
="PAY") And (([dbo_PS_JOB].[ACTION_DT]) Between #2/5/2009# And
#2/24/2009#));


Thanks,

David

david.moore (AT) charter (DOT) net wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't name the column that holds the employee identifier! If you
want to get a specific employee you'd do something like this in the
criteria:

WHERE employee_id = 114

If you wanted to get all employees you'd just eliminate the employee_id
criteria; then all employees would be selected.


I'd re-do your SQL like this:

SELECT P1.file_nbr, P1.deptID, P1.effDt, P1.action, P1.action_dt,
P1.action_reason, P1.compRate As Current, P2.CompRate As PriorValue

FROM dbo_PS_JOB AS P1 INNER JOIN dbo_PS_JOB As P2 ON P1.file_nbr =
P2.file_nbr And P1.EffDt < P2.EffDt

WHERE P1.file_nbr = '112'
AND P1.action = 'Pay'
AND P1.action_dt BETWEEN #2/5/2009# And #2/24/2009#
AND P2.EffDt = (SELECT MAX(EffDt) FROM dbo_PS_JOB WHERE file_nbr =
P1.file_nbr AND EffDate < P1.file_nbr)


Using this: "...And P1.EffDt < P2.EffDt" in the FROM clause's JOIN
expression will prevent you from switching back to the Design Grid 'cuz
Access can't graphically display that relationship.

I like to use the MAX() function to get the last previous date instead
of the TOP predicate 'cuz TOP in a sub-query in the SELECT clause can
slow down the query. Also, Access reports sometimes reject any queries
that have a sub-query in the SELECT clause. Microsoft recommends using
a domain aggregate function instead (like DLookUp()).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSb+9loechKqOuFEgEQLtgwCgq5gCFw4nj4CPs3TjNkkhb8 s55AgAoNTe
eB8gEf1xQxUmYRodz/Oc7z1p
=otjg
-----END PGP SIGNATURE-----

Thanks for the suggestion of using MAX. However, I'm not sure just
removing the File_NBR = '112' would provide me with ALL employees who
have had this change.
I think it would be a many to many problem. What I'm trying to
accomplish is something like:

Name Nbr Dept. EffDt Previous New
Bill Jones 112 110 3/11/09 12.50 13.75
Sue Smith 132 105 3/12/09 15.25 16.23
Joe Williams 105 110 3/12/09 12.65 14.40
Andy Wills 145 102 3/12/09 35,000 42,000

David

On Mar 17, 11:11*am, MGFoster <m... (AT) privacy (DOT) com> wrote:

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

Have you tried eliminating the file_nbr from the criteria? The other
criteria will select only those employees that fit the remaining
criteria - the date and action criteria. You don't have to include a
column in the criteria to get it to show in the results.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBScGMKYechKqOuFEgEQJl8gCgjMVtuariClUzNEhrjs16Uo Zk/HYAnirJ
RU1Cwon0bE+pvoVbrBVqnHun
=Zp1X
-----END PGP SIGNATURE-----

david.moore (AT) charter (DOT) net wrote:

I was able to solve my problem, so thanks for your help.
I did end up using MAX, but I also broke things down into multiple
queries.
Qry_Pay_change_1 selects all employees who had some kind of change
during the time period.
Qry_Pay_change_2 selects those same employees MAX record that has an
effective date less than the effective date of the row selected in
Qry_Pay_Change_1.
Qry_Pay_change_result then joins the two with the criteria that the
payrate from Qry_1 <> payrate from Qry_2 (otherwise, some other kind
of change occurred - like a dept. change, job code change, etc.).

David


On Mar 18, 8:05*pm, MGFoster <m... (AT) privacy (DOT) com> wrote: