Effective Sequence

[Guest]

Hi...thanks for your constant help in my Access queries.

I need some help in putting effective sequence in my reports.

I have a HISTORY table that captures all the history job data of an employees.
It contains the following fields : EmpID,
EffectiveDate,EffectiveSeq,Actions,DepartmentID,Status and other fields
involving monetary terms.

Ive done a test data containing the following datas,

EmpID EffectiveDate EffectiveSeq Actions
DepartmentID Status
8706 21 Oct 2005 1 hire
01 Active
8706 21 Oct 2005 2 probation
02 Active

The queries im churning is on the manpower figures as of the date entered in
the prompt

The query design is as follow

The query will do these in order
- select EffectiveDate which is < or equal to the date entered in prompt
- select the maximum EffectiveDate in that range
- ensure that that particular record, its Action is not Terminated, if it
is, it will exclude the record

the question is that, if the selection of maximum EffectiveDate churns out 2
results, in which the actions occur on the same date, with effectivesequence,
how do i go about selecting the max effectivesequence.

Not all records have effective sequence unless if 2 or more actions occur on
the same day.

This is an issue as the results will churn out both record as shown above,
and count it as 2 head count.

I tink there should be "if...else.." clause somewhere... but where do i put
the clause in...?

Your help id deeply appreciated... Thanks

 

[John Vinson]

the question is that, if the selection of maximum EffectiveDate churns out 2
results, in which the actions occur on the same date, with effectivesequence,
how do i go about selecting the max effectivesequence.

You need some other field to break the tie. If it doesn't matter which
event gets the higher EffectiveSequence if they're on the same date
you can use the Primary Key field of the table (I hope it has one...)


John W. Vinson[MVP]