Update query for Boolean flag?

[fredalina]

i have a set of criteria where the flag is checked if five or more things happen two consecutive months. In order for the flag to be unchecked, two consecutive months need to pass with less than five things happening.

At the moment, i have a query that looks at the previous 4 months, but it is possible that a client will meet the criteria with 2 months > 5, then have 4 consecutive months with a patter of <5,>=5,<5,>=5, so that it should qualify, but will appear not to qualify.

What is the best way to either query back to the first month, or to have an update query or VBA that will check or uncheck the box only if the previous two months have been >=5 or <5? I also need it to output a report fitting into certain buckets:
Applies
Applies - Added to list
Applies - First month <5 lines
Does not apply - First month >5 lines
Does not apply
Does not apply - Drops off list

Thanks!

 

[fredalina]

Apoligies in advance that this is long.

This is to assess a penalty to our suppliers if they send us overages or shortages. If they send us 5 or more different parts each month for two consecutive months with incorrect amounts, we assess a penalty to them that month and each month that they remain on the list. The only way they get on the penalty list is by having 2 consecutive months with >=5 parts, and the only way they then drop off the list is by having 2 consecutive months with <5 parts. If they later have 2 consecutive months >= 5 parts again, they will again be added to the list a second time.

Currently i have a query which looks at the current month and the 3 previous months (4 months total) and counts the parts that are incorrect, and assesses one of the following category labels to that supplier: Applies
Applies - Added to list
Applies - First month <5 lines
Does not apply - First month >5 lines
Does not apply
Does not apply - Drops off list

This works for the majority of suppliers on a monthly basis. However, imagine a situation where the following incorrect parts are received:

January: 6
February: 5
(at this point, the supplier is added to the list if they were not already on it)
March: 1
April: 8
(at this point, had they had less than 5, they would drop off, but since they only had one month <5, they are still on the list)
May: 0
June: 6
At the end of June/first of July, we run this query. It looks back at June, May, April, and March, and determines that in that query time frame, there was no occurrence of 2 consecutive months >=5 parts. Since it does not look back at February and January, it has no way of knowing that this supplier has previously been added to the list and has been assessed the penalty for several months now, and therefore it will assign the supplier a value of "Does Not Apply" rather than "Applies".

Of course, i could expand the query to include more than 4 months, but there would always be the potential that at some point prior to those months queried, an "Applies" value had previously been assessed and the supplier has simply never dropped off the list.

To prevent the user having to cross-reference previous lists, i think i need to do one of two things:
1) Determine the beginning of the "file"/data similarly to an EOF evaluation in a query/module, and query back until that point, or
2) Assign a "flag" field to whether or not the supplier is on the list, and systematically flip those flags on or off depending on the results of this month's query; of course this requires the query to be run monthly, which it will be.

What do you recommend? i do not know how to accomplish either, i'm afraid. I can accomplish the EOF code with difficulty. Perhaps there is a third option as well.

Thank you!

 

[MSAccessRookie]

Why SET the flag based on the activities in that time frame? Why not RESET it instead using something like this:

IF the Flag is NOT SET THEN
....IF there are 2 consecutive months > 5 THEN
........Set the Flag
....ENDIF
ELSE
....IF there are 2 consecutive months < 5 THEN
........Clear the Flag
....ENDIF
ENDIF

This approach only requires looking at the last two months (I think).

 

[Brianwarnock]

At the end of June/first of July, we run this query. It looks back at June, May, April, and March, and determines that in that query time frame, there was no occurrence of 2 consecutive months >=5 parts. Since it does not look back at February and January, it has no way of knowing that this supplier has previously been added to the list and has been assessed the penalty for several months now, and therefore it will assign the supplier a value of "Does Not Apply" rather than "Applies".

As the supplier has not had 2 consecutive months >=5 or<5 surely it is a no change situation?

I think that you probably need to have a flag to indicate on or off list.

Brian

OK rookie beat me to it, and helped with code.

 

[fredalina]

Thanks, everyone. i will give it a try!

 

[MSAccessRookie]

As the supplier has not had 2 consecutive months >=5 or<5 surely it is a no change situation?

I think that you probably need to have a flag to indicate on or off list.

Brian

OK rookie beat me to it, and helped with code.

Actually, it was just Pseudo-Code, but it should help to get fredalina started toward a solution. Always glad to help out

 

[fredalina]

I'm kind of stuck again because I'm not sure how to both use a field's value in a query and change that field's value. In this case, i don't know how to look at the flag field to determine if the flag is already set, then determine through the logic if the flag needs to be turned on (if it's off) or turned off (if it's on), and then to actually reset the value of the flag field according to the above without messing with the query which uses that logic.

Can you get me beyond this snag? It seems it should be easy, but my brain argues otherwise. Thanks!