Calculation 1

[debbieg]

I have no idea how to start with this one.

I have a subform where records could look similar to:

Infraction Date    Points
01/01/2000         3
06/01/2002         1

Somehow, I've got to calculate the points the driver has as of the current date. For instance, in the above example:

On 01/01/2000 three points added to the driver's record.
On 01/01/2001 the points were reduced by 1/3 (2).
On 01/01/2002 the remaining points were reduced by 1/2 (1).
On 06/01/2002 one point is added to the remainder (2).
On 06/01/2003 the remaining points were reduced by 1/3 (1.33).
On 06/01/2004 the remaining points were reduced by 1/2 (0.65).

On 06/01/2005 the remaining points will be 0 if no more infractions.

I'm thinking of putting an unbound textbox in the footer of the subform that will show the remaining points but have no idea how to do this calculation.

Can someone point me the right direction.

I'll probably have to create a report with the same calculation.

TIA,
Debbie

 

[MoLaker]

On first blush, I think I would put the calculations in a function. The table should have a column to store point additions (and subtractions if specific reductions ever occur) and a column to store reduction divisors. The table should also be indexed by the date to keep the calculations based on the correct sequence. (The date field may need the time included in the event an addition and reduction occur the same day.)

The function would open a recordset of the table in date sequence and step through each record calculating the points - making a running total of the added points and dividing the running total with a divisor when appropriate.

If the function uses a date parameter, the calculation could be performed for any date, not just the current date - in case you need a report for a previous day.

Use the function for a field on a form or a report.

 

[TheAceMan1]

How are ya debbieg . . . . .

Calculations are not your problem, there very simple.

What you need to do is [blue]hammer out the criteria[/blue] for deciding [blue]when to reduce & by how much[/blue], as well [blue]when to add and by how much.[/blue] Get this done and its all downhill.

For the above, the [blue]equavilent of lookup tables[/blue] should suffice. If it gets too complex, [purple]code can be made to suite your needs.[/purple]

A function for adding and one for reducing appear to be in order as already suggested by [blue]MoLaker[/blue].

See Ya! . . . . . .

 

[debbieg]

Thank you for your responses. My table is set up as follows (* Primary Key):

*SSN
*InfractionDate
*InfractionID (AutoNumber)
 Infraction
 PointsAssessed

MoLaker,

I have never created a Function before so I don't know even where to start.

TheAceMan1,

I thought I had hammered out the criteria for when to add and when to reduce by how much. Do I need to know more than what I originally stated? The example I gave is basically:

If 1 year passes with no more points then reduce by 1/3;
if 2 years pass with no more points then reduce the remainer by 1/2;
if 3 years pass with no more points then reduce to 0.
If points assessed before the 1 year, 2 year, or 3 year passes then the points are added to the remainder.

How would I set up lookup tables since the dates and points will differ? I guess I don't know what you mean by lookup table.

Thanks so much,
Debbie

 

[TheAceMan1]

debbieg . . . . .

Thats fine . . .

Do you want to see a history as time passes or are you basically updating each record?

See Ya! . . . . . .

 

[TheAceMan1]

Sorry debbieg , that wasn't clear.

Ary you basically updating a [blue]single record for each person[/blue] or intend to [blue]generate a history list[/blue] as the years pass?

Either way, you do realize it'll be [purple]up to user to do the math when adding points?[/purple]

See Ya! . . . . . .

 

[MoLaker]

Debbie,

Writing a function is not difficult, but it would require you to write some VBA code and I don't know if you have ever attempted that. The function should be written in a module to make it available for reports as well as the form.

Your project sounds like a driver's license point system and your last response to TheAceMan1 indicates you have specific trigger points (1 year, 2 years, etc.) for applying reductions. So, you should not need any additional fields.

Pardon me for reverting to DAO, but it works best for me in this case. Perhaps someone else can convert it to ADO. This is untested code.

Public Function CurrentPoints(ssn As String, rptDate As Date)
' calling the function would look something like 
' Me!TotalPoints = CurrentPoints("555-55-5555",#2/28/2005#)
' because this uses DAO, DAO must be added to references (Tools|References)
' select latest version of Microsoft DAO 3.x Object Library
    Dim rs As DAO.Recordset, db As DAO.Database
    Dim strSQL As String, runTot As Double
    Set db = CurrentDb
    ' enter your table name where indicated
    strSQL = "SELECT * from [b][your table name goes here][/b] WHERE "
    ' qualify records by SSN and InfractionDate
    strSQL = strSQL & "SSN = " & ssn & " AND InfractionDate <= #" & rptDate & "# "
    ' order recordset by InfractionDate ascending
    strSQL = strSQL & "ORDERBY InfractionDate"
    ' open the recordset
    Set rs = db.OpenRecordset(strSQL)
    runTot = 0
    ' step through each record totalling points with each step
    Do While Not rs.EOF
        ' pointsassessed added to running total
        runTot = runTot + rs!PointsAssessed
        'select statement applies reduction based on
        'number of years that have passed since the last
        ' point was assessed
        Select Case DateDiff("yyyy", rs!infractiondate, rptDate) + Int(Format(Date, "mmdd") < Format(rs!infractiondate, "mmdd"))
            Case 0
            
            Case 1
                runTot = runTot * 2 / 3
            Case 2
                runTot = runTot / 2
            Case Else
                runTot = 0
        End Select
        rs.MoveNext
    Loop
    rs.Close
    CurrentPoints = runTot
End Function

Go to the Modules objects tab and create a new module.
Copy and paste this function into the module.
Be sure to put your table name in the function where indicated.
Save the module as whatever name you choose.
In your form, you could have an unbound field whose Control Source would be = CurrentPoints(Me!SSN,Date) assuming SSN is the name of the control displaying the individual's SSN.

I hope I haven't screwed this up too much. I did it with more than an occasional interruption.

 

[MoLaker]

Well, I screwed that one up. Don't go away. I'll build a table and be back with one that works.

 

[debbieg]

Wow! You're almost there. I'd say we but...

It's not quite calculating correct.

Here's another example:

01/02/2002   1
10/17/2002   2 (didn't make it a year) calculation 1 + 2 = 3
10/17/2002   1 (had a bad day) calculation 3 + 1 = 4
06/02/2003   1 (didn't make it a year) calculation 4 + 1 = 5
[red]on 06/02/2004 (a year without points) calculation 5 * 2 / 3 = 3.33[/red]
10/22/2004   1 (didn't make it a year) calculation 3.33 + 1 = 4.33

The function calculated this as 2.33

Another example:

12/26/2001    2 
[red]on 12/26/2002 (a year without points) calculation 2 * 2 / 3 = 1.33[/red]
11/24/2003    1 (didn't make it a year) calculation 1.33 + 1 = 2.33
[red]on 11/24/2004 (a year without points) calculation 2.33 * 2 / 3 = 1.553[/red]

The function calculated it as .667

One more example:

05/06/2001    1
12/31/2001    1 (didn't make it a year) calculation 1 + 1 = 2
06/26/2002    1 (didn't make it a year) calculation 2 + 1 = 3
[red]on 06/26/2003 (a year without points) calculation 3 * 2 / 3 = 2
on 06/26/2004 (a 2nd year without points) calculation 2 / 2 = 1[/red]

The function calculated it as .5.

I think the InfractionDate needs to be compared to the previous InfractionDate and after going through all the InfractionDates THEN compare the most recent InfractionDate against today's date.

I've tried to make sense of what you wrote so wonderfully but can't figure out how to do what I just described above.

I really appreciate what you did! I could have worked on this til the day I die and never come up with what you did.

Thanks,
Debbie

 

[MoLaker]

You're exactly right, we have to look ahead to the next record to make our "time passed" check. Let's try this one

Function CurrentPoints(ssn As String, rptDate As Date)
' calling the function would look something like Me!TotalPoints = CurrentPoints("555-55-5555",#2/28/2005#)
' because this uses DAO, DAO must be added to references (Tools|Refernces)
' select latest version of Microsoft DAO 3.x Object Library
    Dim rs As DAO.Recordset, db As DAO.Database, strSQL As String, runTot As Single
    Dim pArray(), Yrs As Integer, RC As Integer, Cnt As Integer
    Set db = CurrentDb
    ' enter your table name where indicated
    strSQL = "SELECT * from PointTrack WHERE "
    ' qualify records by SSN and InfractionDate
    strSQL = strSQL & "SSN = '" & ssn & "' AND InfractionDate <= #" & rptDate & "# "
    ' order recordset by InfractionDate ascending
    strSQL = strSQL & "ORDER BY InfractionDate"
    
    Set rs = db.OpenRecordset(strSQL)
    runTot = 0
    Do While Not rs.EOF
        ' redimension array as each record is accessed
        ReDim Preserve pArray(1, RC)
        ' store points assessed in array
        pArray(0, RC) = rs!PointsAssessed
        ' store infraction date in array
        pArray(1, RC) = rs!InfractionDate
        rs.MoveNext
        RC = RC + 1
    Loop
    rs.Close
    ' now go through array to calculate running total
    For Cnt = 0 To RC
        ' add points to running total
        runTot = runTot + pArray(0, Cnt)
        If Cnt < RC Then
        On Error Resume Next
            ' compare date of infraction to next record's date of infraction
            ' if there is no next record, compare with rptDate
            If IsNull(pArray(1, Cnt + 1)) Then
                Yrs = Abs(DateDiff("yyyy", pArray(1, Cnt), rptDate) + Int(Format(rptDate, "mmdd") < Format(pArray(1, Cnt), "mmdd")))
            Else
                Yrs = Abs(DateDiff("yyyy", pArray(1, Cnt), pArray(1, Cnt + 1)) + Int(Format(pArray(1, Cnt + 1), "mmdd") < Format(pArray(1, Cnt), "mmdd")))
            End If
            ' perform reductions
            If Yrs >= 1 Then
                runTot = runTot * 2 / 3
            End If
            If Yrs >= 2 Then
                runTot = runTot / 2
            End If
            If Yrs >= 3 Then
                runTot = 0
            End If
        End If
    Next
    CurrentPoints = runTot
End Function

At least this agrees with your calculations on only 2 records (except for rounding issues). Certainly, you would want to test several more cases.

 

[debbieg]

You are absolutely the smartest person in the world! That works absolutely beautiful!

I thank you from the bottom of my heart, from the top of my heart, from the middle of my heart, from every part of my heart!

Now I'm going to try to figure out what you did.

Anyone who reads this please give MoLaker a star!

Thanks so much!
Debbie

 

[MoLaker]

I think I've been thanked, so you're welcome.

 

[MoLaker]

Debbie,

There is a bit of housekeeping that should be added to the function.

Add the very end, just before "End Function" or immediately after the line "rs.close", insert these 2 lines:

set rs = nothing
set db = nothing

This cleans up the mess left behind. Sorry I left those out.