How do I count the number or entries in a certain month?

[Seth Schrock]

I'm designing a billing database that I bill every six months. One of my customers have a special charge for all items past the third one in each month. Currently, I'm using Excel to do this using an array formula, but I'm not sure how to do this in Access. If it helps at all, here is the formula that I'm using in Excel:

{=IF(SUM((MONTH($A$8:$A$20)=MONTH(A8))*(YEAR($A$8: $A$20)=YEAR(A8))*1)>2,$A$5,$B$5)}

A2:A20 is where the dates are that I'm analyzing. A5 is the price for the third item and B5 is the normal price. I'll try to get a zip file to upload of my Excel file, but currently my compression software isn't working so I'll have to get help from IT before I can do that.

[Rabbit]

SELECT Count(*)
FROM tableName
WHERE Month(someDate) = 8
      AND Year(someDate) = 2011
HAVING Count(*) > 3;

[Seth Schrock]

Is there a way to pull the month and year of the current record? Otherwise, I'll have to change the year every year and the month for each of the 6 months of the billing period.

[beacon]

Hi Seth,

Assuming that you're using forms to capture the data, make the calculation, and then populate the bound table, this shouldn't be too difficult to do with VBA and a query.

You can setup your form to capture a date for each bill that you input. You could add a command button that either calculates the bill prior to submitting it to the table, or you could create a command button that calculates and saves it at the same time...it's personal preference. I'm going to assume you want a calculate button and will handle the saving separately.

First, I would use the Month(), Year(), and DateSerial() functions to capture the month and year from a text box on the form and assign the values to variables to create your date range from the first of the month to the last day of the month(unless it's also assumed that you will always be dealing with the current year, in which case you could use Year(Now) below instead of referencing your billing date). Then I'd use the variables to create a SQL query that I'd use to create a QueryDef in DAO to find the number of records returned. If the number of records is greater than 2, then I'd apply the special conditions to the bill. Once the bill is calculated, you can save it however you see fit.

Here's a bit of code as an example:

Private Sub BillCalculation()
 
     Dim db As DAO.Database
     Dim rstRecords As DAO.Recordset
     Dim startDate As Date, endDate As Date
     Dim strCompany As String
     Dim myBill As Currency
     Dim specialCharge As Currency
     Dim strSQL As String
     Dim monthlyRecords As Long
 
     'Identify the special charge
     specialCharge = 15.75
 
     'Capture the bill from the form
     myBill = Me.txtFormBill
 
     'Capture the month and year from the form to determine date range
     startDate = CDate(DateSerial(Year(Me.BillDate), Month(Me.BillDate), 1)
     endDate = CDate(DateSerial(Year(Me.BillDate), Month(Me.BillDate) + 1, 0))
 
     'I'm assuming you know how to create a SQL SELECT QUERY
     'The fields, tables, and
     strSQL = "SELECT * FROM myTable WHERE BillDate BETWEEN #" & startDate & "# AND #" & endDate
 
     Set db = CurrentDb
     Set rstRecords = db.OpenRecordset(strSQL)
 
     If Not rstRecords.BOF And Not rstRecords.EOF Then
          rstRecords.MoveLast
          monthlyRecords = rstRecords.RecordCount
     End If
 
     If monthlyRecords > 2 Then
          myBill = myBill * specialCharge
     End If
 
     'Reassign the calculation back to the form field
     Me.txtFormBill = myBill
 
     Set rstRecords = Nothing
     Set db = Nothing
 
End Sub
 

This analyzes the bills individually as they are entered into the form. It may not be the best option, but it's something to work with and is somewhat close to what you were using in Excel.

Hope this helps,
beacon

[Seth Schrock]

Sorry it has taken me so long to respond.

Beacon, I would rather do it through a query than to have it put into a field in a table (normalization theory).

Rabbit, that is close to what I want. My problem is that I'll be evaluating 1/1/20xx to 6/30/20xx and I need it to find each month in that range that has three or more records. From what I'm guessing that your SQL code would produce, it would only search for one month out of the 6 month period. Is there a way to test each of the 6 months individually in the same query?

[Rabbit]

Leave out the month criteria in the WHERE clause but add the month to the SELECT and GROUP BY clauses.

[Seth Schrock]

Here is what I've got:

SELECT Count(*), Month(EffectiveDate)
GROUP BY Month(EffectiveDate)
FROM tblACHFiles
WHERE Year(EffectiveDate) = 2011
HAVING Count(*) >3;

It doesn't work. When I did your original query, I got zero results even though there are 3 records that should meet the criteria in the table.

[Rabbit]

That shouldn't even run. The GROUP BY has to be after the WHERE clause. But because you said it ran means that you either posted SQL that you didn't actually use or there was an error and you misspoke when you said it returned no rows.

[Seth Schrock]

It didn't run. The query that did run is the one from your first post. After moving the GROUP BY statement to be right after the WHERE statement and before the HAVING statement, it does run now, but again, no results. I'm not very familiar with SQL code so there might be something I'm missing, but here is what I have:

SELECT Count(*), Month(EffectiveDate)
FROM tblACHFiles
WHERE Year([EffectiveDate])=2011
GROUP BY Month(EffectiveDate)
HAVING Count(*)>3;
 

The field that I'm checking in the query is the EffectiveDate field. I need it to find the third record in each month (that might not exist, but my test records do have a third record). Is there any other information that you need about what I've got?

[Seth Schrock]

Well, I figured out part of the problem. I needed the count to be >= 3, not just >3. I should have caught that before. Now I'm just working on making it find each of the records that is the third of the month. I'll keep working on it. I'm going to go back over your solution now I have the Count function corrected.

[Seth Schrock]

Okay, I've got most of it figured out. Here is what I've got:

SELECT Count(*) AS Count, Month(EffectiveDate) AS Month
FROM tblACHFiles
WHERE Year(EffectiveDate)=2011
GROUP BY Month(EffectiveDate)
HAVING Count(*) >=3

Now, how do I make the year not be equal to 2011, but make it able to change without manually change it every year? The query runs if I take out the WHERE line. Is there any potential problems that I could run up against if I leave that out?

[Rabbit]

Well, if you don't have a year, then it's going to count January of every year together and so on. You can have it prompt for a year that the user types in by using [Enter Year] or something like that. Or if you want it only for the current year, you can use Year(Date()).

[Seth Schrock]

Okay. I think that it will be okay since I'm going to select a date range of only six months, but I'll keep that in mind as a potential problem that I need to plan for.

Thanks for all your help!

[Rabbit]

No problem, good luck.