Month to Date Average Count of New Records

[Melagan]

Hello! Using Access 97, I need to figure out how do get a month-to-date average of new records added to a table. The table has an "Opening Date" field which defines what day a new record is added. In order to accomplish my goal, there are several pieces of SQL that I'm going to need to put to use, some of which I already know. First, to pull up a whole month's worth of new records:

SELECT Format([Opening Date],"mmm yyyy") AS rMonth
FROM Escrows
WHERE (((Format([Opening Date],"mmm yyyy"))=[Enter Month/Year]));

This will prompt the user to enter a month, like dec 2005, and will give us all of the new records created for that month. Next, I have a custom module that will count the difference in business days between two dates (BusDays([stdt], [enddt]), also accounting for holidays (located in another table.) I'm hoping to use that module to give me the current count of business days in the month that the user has entered to use as the divisor of the total count of records for that month to give me an average per business day figure. However, I do not know how to tell my module that [stdt] = "The First of the Month".
I will be more than happy to supply more information as needed, but I'd like to try and write the details of the code out for myself as much as possible instead of having someone write the whole thing for me =) Please advise, and thank you in advance!

P.S. If there is an easier way to do this, I'm open for moving in a different direction as well. Thanks again!

 

[lupins46]

Asking the user to enter month/year in a parameter prompt is inviting problems.
If you need to do this then you should use a form to capture the month - maybe use a drop down list to let the user pick from it.

 

[Melagan]

Yes I realize that, but I wanted to keep that part of the example simple since that is not the focus of my question.

 

[Melagan]

Well heeding lupins46's advice, I decided to build a form that will launch the query I'm trying to build. I'll go ahead and go into more detail now:

Here is the code I use to calculate business days between two dates:

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim NumDays2 As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (WeekDay(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
               rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                    NumDays2 = NumDays - 2
                 Else
                    'Do Nothing, it is NOT a Workday
                End If
        End Select
        MyDate = DateAdd("d", 1, MyDate)
    Next Idx
    DeltaDays = NumDays2
End Function

Here is the SQL I'm using to execute this function based on two dates entered on my criteria form (frmAvgOpenings). The form has two text boxes, txtStDt and txtEndDt, another text box which will hold the current month (=Format[txtStDt], "mmm yyyy") and the command button to launch the following query. The query, unfortunately, is still asking for the Parameter Value of frmAvgOpenings.txtStdt and frmAvgOpenings.txtEndDt, and frmAvgOpenings.txtMonth I've never tried to launch a query with parameters using a form as the parameter-source before, so please excuse my mediocre programming skills =) Any ideas are always apprecited!

SELECT Count(Escrows.[Escrow Status]) AS oCount, DeltaDays2([frmAvgOpenings].[txtStDt],[frmAvgOpenings].[txtEndDt]) AS BusDays, Escrows.[Opening Date]

FROM Escrows

WHERE (((Format([Opening Date],"mmm yyyy"))=[frmAvgOpenings]![txtMonth]))

GROUP BY Escrows.[Opening Date];

 

[PHV]

Provided that frmAvgOpenings is an open mainform when the query is launched:
SELECT Count([Escrow Status]) AS oCount, DeltaDays2([Forms]![frmAvgOpenings]![txtStDt],[Forms]![frmAvgOpenings]![txtEndDt]) AS BusDays, [Opening Date]
FROM Escrows
WHERE Format([Opening Date],'mmm yyyy')=[Forms]![frmAvgOpenings]![txtMonth]
GROUP BY [Opening Date];

BTW, why BusDays is not calculated in the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

PHV, that fix works. I forgot that since I'm working with a query, I had to be specific in my reference using [Forms]. Getting this to work was really step one. Ultimately, the form will be launching a report using this query as a recordsource. I figure that BusDays needs to be in the query so it will work on the report correctly...or more likely, I'm not good enough with form design to make it work correctly that way. I'll play around with both options though. For know, we can call this thread question solved =) Thanks guys!

 

[Melagan]

PHV I take it back...I have one more question for you. What if I wanted to do something like this (Escrows.[Escrow Number]) is primary key)

SELECT Count(Escrows.[Escrow Number]) AS oCount, Escrows.[Opening Date]

FROM Escrows

WHERE (((Escrows.[Opening Date]) Between [Forms]![frmAvgOpenings]![txtStDate] And [Forms]![frmAvgOpenings]![txtEndDate]))

GROUP BY Escrows.[Opening Date];

By the way, I am now calculating BusDays on the form as suggested and it works well.

 

[Melagan]

Alright, the problem is now totally solved. I was able to figure out how to pass through date criteria from my form to the query using PHV's advice in thread701-1043086. I'm not sure why the Nz() function helps to make it work but obviously it does.