Create a count of records that were "open" during a given time period in the past (1 Viewer)

stephaniem · Feb 15, 2013

Create a count of records that were "open" during a given time period in the past

I posted this in the "queries" forum, but I'm thinking it's going to need some code work and you guys are the best:

Here's the setup:

This is a DB of product development records. The date the project is first entered is stored, as well as every date that an update is made. There is an open/closed flag on each record as well.

I need to create a trend chart that will show how many development projects were "open" during given months.

There is not guaranteed to be an update every month, but the project would still be open.

I'm thinking I need an input where the user can select a date range, and for each month within that date range, see if the month is between the first entered and last update of each project; or, if the project is open then if it is between first entered and current date. That record would then add to the count for that month.

Any ideas?

Appreciate your help. I really can't post a db on here, just because of the sensitive nature of the material. I'm pretty comfortable with vba, but can't figure out the best approach.

jdraw · Feb 15, 2013

Re: Create a count of records that were "open" during a given time period in the past

See http://www.baldyweb.com/OverLap.htm for ideas

stephaniem · Feb 25, 2013

Re: Create a count of records that were "open" during a given time period in the past

That was helpful, but how do I work that into a loop where it repeats for every month between the given date range?

For example, for the entire year of 2012, I want to see a breakdown by month of what projects were open.

Is there a way to code so that it adds 1 month to a date and repeats? Then maybe just have it create a table (viewed as a chart) that has the month and the count as records?

stephaniem · Feb 28, 2013

Re: Create a count of records that were "open" during a given time period in the past

Problem solved. Just wanted to give an update.

There are a bunch of loops involved.

There are some projects in our database where the EntryDate isn't populated (not my doing, whoever designed the database originally...)
So, this loop looks at the records and fills in the date by assigning it the entry date of the record before it... Since there is only about 2 weeks between entries, the method works well enough for our needs.

Code:

Set rs = CurrentDb.OpenRecordset("projectqryresults")
'go to the first record
rs.MoveFirst
'set the default date to when the database originated
defaultdate = ("1/1/2010")
'assign any null entrydates to the default date
Do While Not rs.EOF
    If IsNull(rs.Fields("EntryDate")) = True Then
    rs.Edit
    rs.Fields("EntryDate") = defaultdate
    rs.Update
    End If
    'set the default date to the current record EntryDate
    defaultdate = rs.Fields("EntryDate")
    rs.MoveNext
Loop

Next one takes the date range given by the user and populates a table with a record for each month. It also makes sure the end date set by the user is not greater than the current date. I should probably add a check in there to make sure the start date is less than the end date.

Code:

'set variables according to dates entered by user
varDateStart = Forms!frmOpenProjectCount.DateStart
varDateEnd = Forms!frmOpenProjectCount.DateEnd
'If user input date is in the future, set end date to current date.
If varDateEnd >= Now Then varDateEnd = Now
'determine the number of months of data requested. Populate temptable with a record for each month in the given range.
For intMonthEntered = 0 To DateDiff("m", varDateStart, varDateEnd)
temptable.AddNew
temptable.Fields("ProjectMonth") = DateAdd("m", intMonthEntered, varDateStart)
temptable.Update
Next

The next bit runs through all the project records and creates a count of the types I am interested in for each month.

Code:

temptable.MoveFirst
'Loop through temp table (which has a record for each month requested).
Do While Not temptable.EOF
    PDcount = 0
    NBcount = 0
    rs.MoveFirst
    'Loop through query that returned all product development and new business records.
    Do While Not rs.EOF
    'check entry data and last update or open/closed status
    If rs.Fields("EntryDate") <= temptable.Fields("ProjectMonth") And (rs.Fields("MaxofDate") >= temptable.Fields("ProjectMonth") Or rs.Fields("Closed") = "False") Then
        'If classified as development, add a count to Product Development tally
        If rs.Fields("Status") = "Development" Then PDcount = PDcount + 1
        'If classified as new business, add a count to New Business tally
            If rs.Fields("Status") = "New Business" Then NBcount = NBcount + 1
    End If
    'grab the next project record
    rs.MoveNext
    
    Loop
    'Edit the temporary table for selected month. Insert PDcount and NBcount as values in the record for month in temptable
    temptable.Edit
    temptable.Fields("ProdDevCount") = PDcount
    temptable.Fields("NewBusCount") = NBcount
    temptable.Update
'Grab the next month in temptable
temptable.MoveNext
Loop

At the end, I open the "temptable" as a pivotchart so I can see how many of these projects we had open by month for the daterange provided.

I'm pretty proud of this, even though I know it is clumsy... Just thought I would come back and share how the overall problem was solved in pieces.

jdraw · Feb 28, 2013

Re: Create a count of records that were "open" during a given time period in the past

Glad you have it working.

The clumsy you can work on - if you have time, sometime. The key is it's doing what you need.

Create a count of records that were "open" during a given time period in the past (1 Viewer)

stephaniem

Registered User.

jdraw

Super Moderator

stephaniem

Registered User.

stephaniem

Registered User.

jdraw

Super Moderator

Similar threads

Users who are viewing this thread