Link to home
Start Free TrialLog in
Avatar of Pdeters
Pdeters

asked on

Access 2003 - count number of records depending on when date is

I have  form that is used to add training activities that an employee attends or does.  I want to create a subform on the form that lists all the past dates of traning and counts them like this.  SInce the employees need to do a certain amount of training and within a certain amount of time we want it to count like this but if there is a laps of 60 days to start the count over from the date that the training started again.

2 Mar 02          1
4 Mar 02          2
10 Mar 02        3

9 July 02         1
10 July 02       2

Avatar of harfang
harfang
Flag of Switzerland image

The first part of your question would be answered with a simple subform based on a query like this:

SELECT klngEmployeeID, datTraining, Count(*)
FROM tblTrainingLog
GROUP BY klngEmployeeID, datTraining

With the "link master field(s)" pointing to the main form's klngEmployeeID (supposing that the form shows information for one employee at a time), and the "link child field(s)" pointing to klngEmployeeID of the subform.

As for the second part, about "if there is a laps of 60 days to start the count over", I don't understand what you mean.

Cheers!
(°v°)
Avatar of Pdeters
Pdeters

ASKER

As for the second part, about "if there is a laps of 60 days to start the count over", I don't understand what you mean

What I mean by this is if there is between Mar and July there are more than 60 days the count will start over starting at 1 instead of 4
Avatar of Pdeters

ASKER

THere is only one employee showing at a time..
Avatar of Pdeters

ASKER

That just gave them all the same number
If for an employee they have three training session - i want them to be numbered that way
Date                     Training Session number
May 1, 2002         1
May 4, 2002          2
May 10, 2002       3
Ah, you want to number your records, not count them at all.

What you are asking is surprisingly complex. Databases are not meant to number records dynamically, and the only solution I see is to use VB to renumber records based on your specifications.

The snippet below will renumber the training sessions (in field lngTrainingNumber) for one employee, identified by an employee number. As you see, it will loop through all records, numbering them as it goes along. Whenever the gap is longer than 60 days, the numbering starts over at 1. You will need to adjust all table and field names. Make sure to use square brackets for any name containing spaces.

I hope this will work for you.
(°v°)
Sub RenumberTraining(plngEmployeeID As Long)
 
    Dim strSQL As String
    Dim lngCounter As Long
    Dim datPrevious As Date
    
    strSQL _
        = " SELECT *" _
        & " FROM [Training Sessions]" _
        & " WHERE klngEmployeeID = " & plngEmployeeID _
        & " ORDER BY datTrainingDate"
    
    With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        Do Until .EOF
            If !datTrainingDate > datPrevious + 60 Then
                lngCounter = 1
            Else
                lngCounter = lngCounter + 1
            End If
            datPrevious = !datTrainingDate
            .Edit
            !lngTrainingNumber = lngCounter
            .Update
            .MoveNext
        Loop
    End With
    
End Sub

Open in new window

Avatar of Pdeters

ASKER

Do I make a field in the form to do this.
Yes, this method requires a new field (numeric, long). I called it lngTrainingNumber in the snippet above. You will need to adjust the code for your own field names, naturally.

(°v°)
Avatar of Pdeters

ASKER

i am not sure where to put this.
Avatar of Pdeters

ASKER

would i make a new field in the table or is it a new field just on the form like a list box or text box
It is a new field in the table. Since there is not good way to calculate the rank (the training number) dynamically, you need to store this information in the table.

Normally, something on a form is called a "control", not a field. Although, naturally, a control is often used to display a field. But I did mean a new *field* (in the table).

(°v°)
Avatar of Pdeters

ASKER

Where do i put the code then. In the form itself to store the data.
Avatar of Pdeters

ASKER

I have created the new field in the table. Now I have trie dputting the code in a subform of the form to run this code and list all the training dates number. I am confused as to where I should be putting this code to have it run right
Avatar of Pdeters

ASKER

I have tried putting it in the form and it does nothing. I put in the form as an event in the lngTrainingNumber field
I suppose you should add a button to call it. On the click event, you call the Sub, passing the current employee number to it. It will then renumber the table, and the numbers will be visible in the subform. I suppose you have little experience with VB, but I'm afraid this is still the best way to go.

I'll throw together a demo, it's going to be easier to understand. But I can't do it right now, you'll have to wait a little.

(°v°)
Avatar of Pdeters

ASKER

thank you . I don't have a lot of experience with VB but am leanng
Thank you again.
Here is the small demo. The main has a button [Renumber], which renumbers the training log displayed in the subform. I hope this helps.

(°v°)
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Pdeters

ASKER

Thank you so much for the help. It is very much appreciated.