how to check the consecutive month

[tehchino]

Hi there, i have a problem here, i posted this in some places too just in case..

I need to do up a report for generating Staff alertness
The report is to show how much each team will get by the end of the month.
If they meet the criteria to perform well, each member in that team will get $10 for the 1st month(eg Oct).
If the next month(eg Nov) they did well too, the money will roll and each will get $15 for Nov.
For the 3rd month (eg Dec) and above they will get $20 each

However the money will only add on if they have staff alertness for a consecutive period of 3 months and above.

If for example they failed to perform well, they will get nothing for that month, and next month they have to start from only $10 dollars again.

How should i set the query to calculate for me?
Any help is appreciated.

 

[The_Doc_Man]

You need to read help files on the DateAdd, DateDiff, and DatePart functions to see the tools Access gives you to manipulate dates.

Another part of your problem is that whatever records you are keeping have to support this query by tracking those months in which a team qualifies. I.e. a table with team and date for each month. You can make it "sparse" by having entries only for those months in which the team qualifies.

Then, the answer is to take the current date, do a DateAdd function in which you add -3 (i.e. subtract 3) from the MONTH. Then use the resulting date as a filter in a query that counts all "qualifier" records with a date greater that the critical date.

This might have to be done in a loop. The idea would be that you loop through consecutive months backwards. In your "qualifier" record, include a field that tells you how many months prior to that date were also qualified. Then write an update query that updates that information when the record is stored.

I.e. if you do a DateAdd of -3 to the month, then if you get a DCount of 3, you have 3 months in a row. If you only get 2, then at least one of those months did not qualify.

Now, if you only store records for teams that qualify in a given month, then to find who gets a bonus, you run a query for teams that have qualified this month, then look at the number of months they qualified.

The above is a little confusing, perhaps, but it should work once you get it figured out.

Remember, keep a separate table of qualifier records with team identifiers, current date, and consecutive qualifier months. Compute and store the "consecutive qualification" count when you store the record. Then only look at the months for this record to see who gets the bonus. (Presumably, this would mean that you presented last month's bonus last month.)

 

[tehchino]

Errr..i know nuts about DateAdd, DateDiff, and DatePart functions. But i will go try to read it up myself tomorrow morning. Thanks and keep you update!

 

[raskew]

If you had tblTeams, which looked like this:

<table>
Team	strMoYr	Qualify
Alpha	01/2002	Yes
Bravo	01/2002	No
Charlie	01/2002	Yes
Alpha	02/2002	Yes
Bravo	02/2002	Yes
Charlie	02/2002	No
Alpha	03/2002	Yes
Bravo	03/2002	Yes
Charlie	03/2002	No
Alpha	04/2002	Yes
Bravo	04/2002	Yes
Charlie	04/2002	Yes
</table>

….you could use this code to return your results

Function TeamComp()
Dim db As DATABASE
Dim rs As Recordset
Dim strSQL As String
Dim strTeam As String
Dim curAmt As Currency
Dim TB, fmt, header, n, i, NL, Msg


TB = Chr(9) ' Define tab.
fmt = "$###,###,#00.00"  ' Define money format.
header = "Team" & TB & "Month__" & TB & "Qual." & TB & "Awarded" & Chr(13)
NL = Chr(13) & Chr(10)  ' Define newline.
Msg = header & NL


Set db = CurrentDb
strSQL = "SELECT Team, Format(DateValue([strMoYr]),'mm/yyyy')" _
    & " AS tmMonth, Qualify FROM tblTeams" _
    & " ORDER BY Team, Format(DateValue([strMoYr]),'mm/yyyy');"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
   curAmt = 10
   n = 1
   strTeam = rs!Team
   Do While rs!Team = strTeam
      Msg = Msg & strTeam & TB & rs!tmMonth & TB & rs!qualify & TB
      Msg = Msg & IIf(rs!qualify, Format(curAmt, fmt), Format(0, fmt)) & NL
      curAmt = Switch(Not rs!qualify, 10, n < 3, curAmt + 5, True, curAmt)
      n = Switch(Not rs!qualify, 1, n < 3, n + 1, True, n)
      rs.MoveNext
      If rs.EOF Then Exit Do
   Loop
   Msg = Msg & NL
Loop
MsgBox Msg, vbInformation, "The Results Are In"
rs.Close
db.Close
Set db = Nothing
            
End Function

 

[tehchino]

Hi Doc_Man...hmm i tried to read up on datepart datediff and date add and work it out myself but i cant see how it relate to what i want...i cant see how it works. Can you explain it in more details please?

Hi Raskew, hmm they wont have a field to specific yes or no if whether they are qualified. I dont know how to write VB basic code at all.. thanks anyway.

Is there any other way everbody?