Find personnelIDs previous record based on date

[Timothy via AccessMonster.com]

I have a table of agent moves & other HR related transactions in the company
(transfers, promotions, demotions etc.) in Access 2003. I need to somehow
reference a move, say a promotion, and pull the data from the previous record.
I need to show where the agent was promoted from, (department, supervisor,
position, etc.). There are certain instances that I may not want to track
(pay changes, leave of abscence, etc), but I may still need to pull data from
that record as it may have a promotion after it, so I would need to reference
it's department, sup and what not. So an agent may have 6 records in a year,
(2 pay changes, 1 transfer, 1 out on leave, 1 return from leave, and 1
promotion for instance). In this instance I would only need to report on the
transfer and the promotion. The transfer an promition record only have the
new agents data on it. New position, new department etc. I need to note the
two transactions, but attach the previous records data to it. I have
personnelID and Date of transaction (a personnelID will never have more than
one transaction on a given date)

I went in to this thinking it would be fairly simple... I started with
queries, but could not make anything work for every instance. I am sure
there is some bit of VBA out there that can do this, but I have been unable
to make anything I have come across in the forums get the results I need.
Any help would be greatly appreciated.

Thanks,

Tim

 

[Gary Walter]

Hi Timothy,

I may not fully understand (w/o table structure/sample data), but...

It sounds like you need to start a query with 2 instances
of your table.

Give one an alias of "Current" and other "Prev"
(in query designer, right-mouse click on table,
choose "Properties," and type in Alias).

Join the 2 instances on personnelID.

Double-click on top of "Current" table
to select all fields, then drag selection
down to grid.

Double-click on "Prev.[Date of transaction]"
to send it also to grid. As well as other "previous"
fields you will want in record. In the field row of
the grid for these, add a "prev alias" and colon, i.e.,

Field: PrevDept: department
Table: Prev
Sort:
Show: <checked>
Criteria:
Or:

So all that is left is to create a correlated subquery for
Criteria row under "Prev.[Date of transaction]" to pull
the correct previous record for each current record.

something like (all one line in Criteria box)....

(SELECT Max(q.[Date of transaction]
FROM yurtable As q
WHERE
q.personnelID = Current.personnelID
AND
q.[Date of transaction] < Current.[Date of transaction]
AND
......
)

This subquery is saying

when we look at a record in "Current,"
find the record in "Prev" for that person
whose trans date was the latest date
but was earlier than date of this "Current" record.


the "...." will be any other q criteria that will eliminate
choices from Prev, i.e.,

if you have a field "transaction" that could be

"promotion"
"pay change"
"transfer"
"leave of abscence"

and you only want to see previous "promotion"

(SELECT Max(q.[Date of transaction]
FROM yurtable As q
WHERE
q.personnelID = Current.personnelID
AND
q.[Date of transaction] < Current.[Date of transaction]
AND
q.transaction = 'promotion')

Clear as mud now?

If you need further assistance, please reply back with
your table fields/type, some sample data, and what
you expect your query result to look like.

good luck,

gary

 

[Gary Walter]

was missing ")" in Max() of subquery...

(SELECT Max(q.[Date of transaction])
FROM yurtable As q
WHERE
q.personnelID = Current.personnelID
AND
q.[Date of transaction] < Current.[Date of transaction]
AND
q.transaction = 'promotion')

Hi Timothy,

I may not fully understand (w/o table structure/sample data), but...

It sounds like you need to start a query with 2 instances
of your table.

Give one an alias of "Current" and other "Prev"
(in query designer, right-mouse click on table,
choose "Properties," and type in Alias).

Join the 2 instances on personnelID.

Double-click on top of "Current" table
to select all fields, then drag selection
down to grid.

Double-click on "Prev.[Date of transaction]"
to send it also to grid. As well as other "previous"
fields you will want in record. In the field row of
the grid for these, add a "prev alias" and colon, i.e.,

Field: PrevDept: department
Table: Prev
Sort:
Show: <checked>
Criteria:
Or:

So all that is left is to create a correlated subquery for
Criteria row under "Prev.[Date of transaction]" to pull
the correct previous record for each current record.

something like (all one line in Criteria box)....

(SELECT Max(q.[Date of transaction]
FROM yurtable As q
WHERE
q.personnelID = Current.personnelID
AND
q.[Date of transaction] < Current.[Date of transaction]
AND
.....
)

This subquery is saying

when we look at a record in "Current,"
find the record in "Prev" for that person
whose trans date was the latest date
but was earlier than date of this "Current" record.


the "...." will be any other q criteria that will eliminate
choices from Prev, i.e.,

if you have a field "transaction" that could be

"promotion"
"pay change"
"transfer"
"leave of abscence"

and you only want to see previous "promotion"

(SELECT Max(q.[Date of transaction]
FROM yurtable As q
WHERE
q.personnelID = Current.personnelID
AND
q.[Date of transaction] < Current.[Date of transaction]
AND
q.transaction = 'promotion')

Clear as mud now?

If you need further assistance, please reply back with
your table fields/type, some sample data, and what
you expect your query result to look like.

good luck,

gary

 

[Timothy via AccessMonster.com]

Gary,

That worked perfectly!!! I knew it could be done, I just couldn't wrap my
head around the subquery. Thanks so much, you saved me much grief.

Tim

was missing ")" in Max() of subquery...

(SELECT Max(q.[Date of transaction])
FROM yurtable As q
WHERE
q.personnelID = Current.personnelID
AND
q.[Date of transaction] < Current.[Date of transaction]
AND
q.transaction = 'promotion')

Hi Timothy,

[quoted text clipped - 117 lines]