Question change id value depending on date of event

[Pavl]

Hello from an occasional amateur user of 2010.
I have a functioning accdb for the display of historical military information.
One part of this is the display of a persons rank by using [tblpersons.id0rank] passed to [tblranks.id] via a query on a subform. This works as expected. ([tblranks] fields are in numerical and text sequence)

I would now like to add a feature that allows for promotions (ie change of rank on a specific date).
I am using a limited date range so could have a default date somewhere in the future for comparison purposes so that all calculations are in the same direction (negative).

This box wont give more room to explain problem .... so can I compare dates and subtract "1" from a field value using an IIF statement in subform query ?

 

[jdraw]

How about posting a jpg of your relationships window (tables and relationships)?
Good luck.

 

[Pavl]

Thank you jdraw.
Atteched relationships and query from subform showing [tblpersons] and [tblranks] - all have enforced referential integrity.
I would like to keep the existing ranks as currently selected by [tblpersons.id0rank] linked to [tblranks.id] but be able to display the correct (modified) rank on my various forms/subforms "on the fly".
So that as I select an event there will be a date comparison between EventDate and RankDate and then make a reduction of "1" to the value of [id0rank] being passed to [tblranks.id]. (These two items are not in the accdb yet).
Reason for reduction as follows: will need a default date set in future so that existing ranks can be displayed on other forms. This FutureDate could be on a new table with just one record ? Or is that wrong way to go?
Can all that be done on existing queries with an IIF ?

 

[gemma-the-husky]

can't you just have a "promotions table", linked to the personnel table, with personnelID, rank and date.

In some databases, I have a "base date" setting for the initial setting. 1/1/2000, say.

 

[Pavl]

Thank you Dave.
I was indeed hoping to use a date beyond the range of my records to provide a default against which I could compare - and that would allow all calculations to be in the same direction (negative).
So if I read this correctly - I just have a date against each current rank in the current table but create a new table as a copy but with different dates as per promotion.
Then compare EventDate with ? to get the correct rank from the correct table ?
So is that some form of If Else statement in the form/subform query to get to the correct table ?
Something like If EventDate is after RankTableOneDate then use RankTableTwo Date Else use RankTableOneDate ?
As you can see - I dont know how to do this .......

 

[gemma-the-husky]

you probably want to store all the appointments in a separate table including the current one.

so the appointments table looks like this

personnelID, rank (more likely rankID), date appointed, etc

for personnelId 1 you get

1, RSM, 8/11/2016
1, sergeant, 2/6/2012
1, corporal, 12/1/2007

The current rank is given simply by finding the rank for the top item when the table is sorted by date descending.

You can find a rank at any other point in time, by finding the record with the greatest date which is less then your search date - so for 1/1/2014 say, it returns 2/6/2012, and hence "sergeant", or maybe do a find first action on a query.

You don't really need if then else logic. What you are doing is searching the "appointments" table to find the row you need based on a little bit of logic.

This can be done by a combination of dlookups, dmax, perhaps, or by using a query, and performing a "find first" operation. This is why having the right indexes on a table make it easier. In this case you need an index sorted on personnelid, and data appointed. Then access can find the record you want very quickly by examining the index structure.

 

[Pavl]

Thank you Dave.
Help much appreciated - I will now try this method.
Regards - Paul

 

[Pavl]

So far unable to get this to work.
I offer this simplified/renamed example to explain what I am trying to achieve.
New table created [tblpromotions] with 3 fields [personnumber] PN, [ranklevel] RL, [effectivedate] ED.
PN: RL: ED:
1: 6: 1948
1: 7: 1950
1: 8: 1952
2: 5: 1948
3: 6: 1948
4: 5: 1948
4: 6: 1951

Have main form with a text control for "EventDate".
Subform - continuous - to display participating persons (variable quantity) including rank taken from RankLevel.

Using exemplar EventDate of 1952 I want to achieve the following:
PN1 = RL8 (exclude RL6: ED1948 and RL7: ED1950)
PN2 = RL5
PN3 = RL6
PN4 = RL6 - (exclude RL5: ED1948)

But I can only achieve a single person record if I use "TOP 1" query.
Other query options I have tried get all person records but with all other RL values for the prior EffectiveDates.
This gives duplicate persons on the subform with different ranks.
I cannot get just the highest/latest ED and RL value for ALL persons against the EventDate criteria.
Have tried to read and understand subqueries but am not proficient enough to get them to work.

 

[Pavl]

Apparent success with nested queries.
First query selects against the text control with EventDate.
This used within second query with Totals of Max on PN and ED.

Is this the correct method or will it bite back later ?

 

[gemma-the-husky]

you could use a function to find the record you want.

function activerank(person as long, applicableyear as year) as long

dim effectivedate as long

'step 1. read the effective date
'the date you want is the highest date not greater than the date you are checking
effectivedate = dmax("ED","tblpromotions","pn = " & person & " ED<=" & applicabledate)

'step2, using this date read the rank.
activeank = dlookup("RL","tblpromotions","pn = " & person & " ED = " & effectivedate)

end function

'note 1. this needs some "null checking", in case there is no rank found.
'note 2. so perhaps you an write this as a single query - eg your top1 query do this in a single process.

 

[Pavl]

Thank you again Dave.
Will have a go at that.
I 'should' not get the null / no rank issue as all have a default rank or an "unknown".