Count records in any 40 day period

[Blueberries2013]

Hi, I have some Access skills but I am new to Access VBA. I wonder if someone could help. I'm not sure if the solution requires VBA or a query, etc.

I have a table that contains a person ID, a start date for that person, and a GroupType. I want a way to count the number of people who started within ANY 40 day period. Then depending on that number , assign a value to the GroupType field (ie. if the no. of people is 1-15 then groupType = ‘groupA’, if its 16-30 then groupType = groupB, if its over 31+ then grouptype = GroupC. This needs to be re-counted every time a new record is added. As well as having the groupType visible on the form for that person, I also want to be able to run a report showing all people, their start date and group type. Note that when start dates are entered these may be either future dates as well as dates in the past.

If possible, I would also like to track /store when a person's group type changed in another or the same table perhaps.

Thanks.

 

[billmeye]

1. Not really a good idea to store calculated fields. (GroupType)
2. How do you determine what the 40 day period is? How can you compare it to a previous 40 day period when the 40 day periods are not defined?
3. In VBA you can create a function to return your GroupType:

Public Function GetGrType(FrmDate as Date, ToDate as Date) as String
Dim GetCnt as Integer
GetCnt = Dcount("*","tblYourTable","[StartDate]>=#" & FrmDate & "# And [StartDate]<=#" & ToDate & "#")
If GetCnt = 0 Then
GetGrType = ""
ElseIf GetCnt <16 Then GetGrType = "groupA"
ElseIf GetCnt < 31 Then GetGrType = "groupB"
Else
GetGrType = "groupC"
End If
End Function

To keep track of changes you could either create an additional memo field that you update to include the date and what changed or create another log table to do the same.

 

[Blueberries2013]

Thanks for the reply BillMeye.

1. Yes, I thought it was bad to store a calculated value but its such a crucial field
that I need to keep a record of it! I just thought, I may try to do something with a make table query. Thanks for the inspiration.

2. The 40 day period - Ah this one has had me thinking, still not sure I've got my head around it!!! but my initial thought was to start with the earliest startdate in the table (call this fromDate) and add 40 (call this ToDate). This would give the inital 40 day period. I would then count any StartDate that fell within this period. For the next round of counting, I would add 1 to the fromDate and also to the ToDate and again count each startdate only if it fell within this period. And so on..... Hope that makes sense.

3. Functions, yes.... didn't think of using a function. Being a newbie, I will have a think about this and a play tomorrow when back at work.

Thanks for your help thus far. Much appreciated.

 

[billmeye]

So for this 40 day period thing, each day you will run your update? If you forget a day what then? Or, will you have some form or way of inputting what date range you will want to update? For the first date of data entry, it would only get updated during the first 40 days and on day 41 it would be dropped from needing updating? And so on with each successive date? So you would only be updating the last 40 day period? A 2nd Function could be written to cycle through all records within the date range and update the GroupType as needed and also record the changes in a log or memo field.

 

[DavidAtWork]

You haven't indicated how many records per day/week/month you're dealing with, but I can see some problems you might want to give thought to.
If you have multiple new records (starters) per day, that could mean a persons group type changing more than once, per day. Do you really want to keep track of all group changes in this scenario.
Would a persons historical group type value be of any use when it could be changing daily/hourly even, if not then you may only want to consider what a persons current group type is at any given time and this can be stored as an attribute in the person table and updated each time a new starter is added.
Regarding the 40 day period, I would have thought it would better to use date of last starter as your TO date and FROM date would be [date of last starter] - 40
OR you could use the Input box method of getting the FROM or TO date.
As to the counting of starters and assigning their group types, Billmeye's function above should work fine.

David