Re: Find personnelIDs previous record based on date

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')

"Gary Walter" <gary@xxxxxxxxxxx> wrote in message
news:expMurNDIHA.2004@xxxxxxxxxxxxxxxxxxxxxxx
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? 8-)

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" wrote:
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

--
Message posted via http://www.accessmonster.com





.

Relevant Pages

  • Re: Find personnelIDs previous record based on date
    ... It sounds like you need to start a query with 2 instances ... Table: Prev ... if you have a field "transaction" that could be ... and you only want to see previous "promotion" ...
    (microsoft.public.access.modulesdaovba)
  • Re: System.Transactions stored procedure returns NULL value
    ... being promoted - avoiding promotion at the cost of not enlisting in the ... transaction is probably not what you want. ... price difference to pay when using escalation, ... The reason for the scope_identity was to rule out triggers that may be ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: System.Transactions stored procedure returns NULL value
    ... Enlist=false signifies that the current connection will not enlist within ... That may or may not have any effect on the promotion ... as it doesn't have any affect on the transaction itself. ... But you don't want the new connection to not enlist within the same tx - you ...
    (microsoft.public.dotnet.framework.adonet)
  • IllegalArgumentException mit Hibernate und Tabellenrelation
    ... Als zentrale Tabelle gibts in der Oracle-Datenbank die Tabelle TASK. ... und zwar Task mit Agent. ... Session session = null; ... Transaction transaction = null; ...
    (de.comp.lang.java)
  • Re: Access 2000 & Stored Procedures
    ... Inside the transaction you'll select the record to call, ... >I'm running a application based on Access 2000 and SQL ... >stored procedure with a variable of the agent's ID. ... >record with the agent ID. ...
    (microsoft.public.sqlserver.clients)