Creating a Filter that applies to Certain Records (Adaptive Filter?)

[skidawgs]

Hi All,



I'm having trouble with [what I think is] some complex filtering for a subform I have.

I have a Many-to-many relationship between a Person and a Position, with a 'Join' table between the two. Within the Join table, I have the Person_ID, Position_ID, and Status among other things. Since there can be multiple entries for each Person and each Position, the status field is used when to prevent duplicate records from appearing in my subform for my user. For example, if a position has had 4 different people in it over its lifetime, there would be 4 different records appearing in my subform, when really there should just be one. The Status field has potential values of: Current, Past, or Future.

Currently I have the following filter:

strFilter2 = strFilter2 & "([Status] Is Null or [Status] = 'Current') AND "

This Filter prevents any People/Position that are 'Past' or 'Future' from being in the database, while also allowing null values to populate the subform because a position does not always have a person currently employing it.
That is what leads me to my problem, when a position is empty and has previously been occupied, there is no Current record, and the status is not null since there is already already a matching Position_ID with a status value. Therefore the subform does not show a record for this position even though my user would like it to.

Desired Output: Instead, I would like to change the filter to show all positions where status = null or current, but if neither are present, to show the most recent 'Past' record. My current filter listed above works this way in a sense, so long as it is a newly added position and there has never been somebody occupying it yet.

My thought process leads me to think I would need to loop through the records in the table to figure out which ones have no 'Current' status, that also have 'Past' records already in the Join table... Not sure how to do this however, and don't know if its feasible.


I hope I explained this sufficiently, if any of you have any advice on how to handle this, it would be greatly appreciated!

Thank you!
Skid

[ranman256]

If the master form is PERSON, then the subform would be bound on the PersonID,
then the subform would only show THAT persons Positions.

subform properties:
LINK MASTER FIELDS: personID

LINK CHILD FIELDS: personID

[skidawgs]

Thanks for your reply, ranman. The Master is an unbound form which is solely used for filtering and displaying data from just about every table in the database.
Do you happen to know of another method being that the Master is unbound?

Thank you.

[orange]

Please tell us more about

The Master is an unbound form which is solely used for filtering and displaying data from just about every table in the database.

.
Readers can not guess what your real requirement and set up are.
Ranman has provided the most common construct, but your "requirement" seems different.
Can you show us a graphic of your relationship window?

[skidawgs]

Hi Orange,

Unfortunately I will be unable to provide a graphic, but I will happily describe its functionality.
the Form has three main aspects: a Filter Section, a Navigation Section, and a Display Section. The filter section contains a dozen or so [unbound] combo boxes which allow the user to filter the records populating the navigation subform. For example, if the user selects the 'Skill Level' combobox and selects 'Senior', then the datasheet subform will populate only those records where the skill level is 'Senior'. And if the user Selects 'Senior' and also selects 'XYZ Corp' for Company, then it filters by both criteria. This can be filtered as much/ little as the user likes to allow them to see exactly what they are looking for. Note: the user only sees the Department, Skill Level, and Employee Name in the datasheet subform, as this serves as a brief summary that the user can click to get more information.

After the subform is populated, the user can select one of its records, which then populates the Display tab in a user-friendly Tab Control with all the related information (also not limited to one table.. e.g. shows the person occupying the position, info about that person, the company of that person, as well as position data, etc... a.k.a all the data from the query). To get this functionality, the subform query outer joins 7 or so tables to get all the data in one subform.
As you know however, this will result in multiple records all with the same Position_ID which leads me to my problem. To prevent getting multiple of the same Position appearing in the subform, I have the "Status" field in the Person_Position table.

The user would like to see one of each position appearing in the subform. My current Filter almost does this perfectly. It shows only the current positions, and the positions without a matching record in the Person_Position table, which is the case when no person is currently occupying the position. However, since it prevents records from appearing that are 'Past', once a person leaves a position and we are searching for a new employee, the record does not appear in the subform because the critera weeds out the 'Past' Status values.

Hopefully I explained this well, if not please let me know and I can elaborate further if needed.

I really appreciate the help.
-Skid

[orange]

Interesting, but I think this is one of those cases where 1 picture is worth more than 1000 words.

[accesstos]

Hi Skid!

If I have understood well, it's not necessary a "Status" field.
The max ID of each position in the 'Join' table, with a date earlier than tomorrow, indicates the 'Current' record of each position, even if the person has left the position.
The records with [ID]<'Current' belongs in the past and the records with [ID]>'Current' belongs in the future. So, you don't have to edit the "Status" field everytime you add a new record.

SELECT Max(PosPerID) AS CurrentPosPerID, PositionID 
FROM tblPositionPersons 
WHERE occDate<=Date() 
GROUP BY PositionID;

The query above returns the most recent record for each Position.
Replace the names as required and join this to the Position-Person table, on PK and PositionID, to retrieve the details of most recent occupied Positions.

I hope it helps!

Cheers,
John

[skidawgs]

Hi John,

Thanks for the insight! Relying on Start/End Dates is a thought I had previously mentioned to my users, and their response was that sometimes they don't know an exact date for somebody leaving/ starting a job and may temporarily leave it blank. I can see this causing problems with the query if some of they are sometimes null, unless I make them required fields. After hearing your feedback however, I am going to talk with them again and strongly ask that they reconsider their data entry practices to allow for this enhancement.

Thanks again, I really appreciate it.
Skid