Why doesn't my count field work?

[Seth Schrock]

I'm trying to count the number of records in a table for each individual month, for a certain customer, in a given date range. I've created sample data so that I can test the query and the sample data has the dates 6/10/11, 6/20/11, 7/1/11, 7/15/11, 7/31/11, 8/18/11, 8/20/11, and 8/25/11. In other words, 2 in June, 3 in July, and 3 in August. My problem is that the count is being 1 for every file. Here is my SQL code:

SELECT Count(*) AS CountOfFiles, EffectiveDate, CustomerName, Entries, PerFileCharge, Entries*PerEntryCharge AS EntryCharge, [PerFileCharge]+[PerEntryCharge]*[Entries] AS TotalCharge, Month(EffectiveDate) AS MonthOfFile
FROM tblCustomer INNER JOIN tblACHFiles ON tblCustomer.CustomerID = tblACHFiles.ACHCompanyID
GROUP BY EffectiveDate, CustomerName, Entries, PerFileCharge, Entries*PerEntryCharge, [PerFileCharge]+[PerEntryCharge]*[Entries], ACHCompanyID, Billed, PerEntryCharge, Month(EffectiveDate)
HAVING (((EffectiveDate)>=Forms!frmCustomer!txtStartDate And (EffectiveDate)<=Forms!frmCustomer!txtEndDate) And ((ACHCompanyID)=Forms!frmCustomer!CustomerID) And ((Billed) Is Null)) AND Count(*) < (Forms!frmCustomer!NumToGetSpecialRate)
ORDER BY EffectiveDate;

What I want is that the counts would be 1,2,1,2,3,1,2,3 for the dates given above so that I can charge a special price for the third record of every month, but not the first 2. I was given some code that came closer, but still not what I wanted:

SELECT Count(EffectiveDate) AS [Count], Month(EffectiveDate) AS [Month]
FROM tblACHFiles
GROUP BY Month(EffectiveDate), CompanyFileID;
 

This just gives three lines telling me that I had 2 files in the month of 6, 3 in the month of 7, and 3 in the month of 8.

If I can get the first query to work like I want it to, I can use the iif-then-else statement to apply the special rate to the files that have count >= 3. Is this possible? I'm using Access 2010

[nico5038]

Your [EffectiveDate] in the "Group by" will cause the "single count" as you're not counting months, but days.

I also wouldn't use only the Month() function, but also Year like: Format([EffectiveDate],"yyyymm")
to get the correct GroupBy.

I guess the [EffectiveDate] has been added to be able to distinguish the different records. So using a "YearMonth" GroupBy wouldn't solve your need for the individual records.
It would be better to have a sequencenumber for each record.
For that you can use the Dcount() function with a where clause limiting the count for the specific month and the customer like:

SELECT DCount("*","tblACHFiles","[EffectiveDate] <= [EffectiveDate] and Format([EffectiveDate],"yyyymm")=Format([EffectiveDate],"yyyymm") and ACHCompanyID = ACHCompanyID) AS [Count], Month(EffectiveDate) AS [Month]
FROM tblACHFiles
 

The [EffectiveDate] <= comparison will cause the count to start with 1 and increase for each extra record. This will only work when there's a max of 1 record per day. Where there are multiple you'll need a unique identifier in the table and use that field instead of the [EffectiveDate].

Getting the idea ?

Nic;o)

[Seth Schrock]

I'll try this on Tuesday when I get back to work. Thanks.

I was thinking that I didn't need the year() function because I'm going to be selecting 1/1/xx to 6/30/xx and 7/1/xx to 12/31/xx. I'll never be crossing the year end. Do you think that I should still use it?

[NeoPa]

That depends on what you mean by that Seth. What happens to the data in the table for year 2010 when you move along to 2011. If it's not cleared or filtered out then it needs to be handled.

[nico5038]

I always try to code "foolproof".
Having just a month gives the risk that when your table holds two years of data that the count is wrong, as the "Month" count works on two years instead of one. Selecting just a period within a year won't correct the Dcount() when you use Month() instead of the Format([datefield],"yyyymm").
But don't take my word for it, just try Format([datefield],"mm") and see the effect when having two years of data in your table.

Keep me posted :-)

Nic;o)

[Seth Schrock]

I'm going to be billing every six months. The query will pull those six months and then assign an invoice ID to them. That will put them into a bill so the if I ever have to look at them again, it will be in the invoice, not as a separate record. Does that make sense?

[Seth Schrock]

nico5038, I'll give it a try. Foolproof code would be best. It would certainly make it easier if the company later changed the billing period to cross the year end. I wouldn't have to change the query.

I don't have access to the database at home, but I'll give it a try sometime either Tuesday or Wednesday I hope and I'll let you know if I have anymore questions. Thanks again.

[Seth Schrock]

Nico5038, I just copied your SQL code (I had to remove one of the double quotes to make it work) but the results have the count for each line as 15 (There are 15 records in the table). It does give the month correctly though.

In your first post, you said that the [EffectiveDate]<= comparison would cause the count to start at 1. Will it restart at 1 when the month changes? That is what I'm needing.

[nico5038]

Sorry, you need to change all the double quotes before and after the yyymm: "yyyymm"
into single quotes like: 'yyyymm'

It will restart counting for each month, as you can see the comparison of the YearMonth ("Format([EffectiveDate],'yyyymm')=Format([EffectiveDate],'yyyymm')") in the WHERE parameter of the Dcount() function, but just try to see :-)


Nic;o)

[Seth Schrock]

It still does the same thing. The results show the Count and the month. The month result is correct, but the count for each line is 15 (the number of records in the table). Here is what I have:

SELECT DCount("*","tblACHFiles",[EffectiveDate] <= [EffectiveDate] and Format([EffectiveDate],'yyyymm')=Format([EffectiveDate],'yyyymm') and ACHCompanyID = ACHCompanyID) AS [Count], Month(EffectiveDate) AS [Month]
FROM tblACHFiles
WHERE ("Format([EffectiveDate], 'yyyymm')=Format([EffectiveDate],'yyyymm')")
ORDER BY Month(EffectiveDate);
 

Once I get the count to be correct I'm going to add the WHERE clause to get only the data for the current CompanyID, but I figured I'd keep the query as simple as possible for now. Am I copying too exactly what you have and should be putting in fields from what I have to replace some names in your code? I know I'm missing something that is probably right under my nose, but I can't figure it out.

Note: I get the same results with the WHERE statement as I do without the WHERE statement.

[nico5038]

Hmm, strange, but I see you use reserved words as fieldnames.
Don't use "Count" and "Month" as fieldnames, as they are too function names. Change them to e.g. CountEffectiveDate and MonthEffectiveDate.

Nic;o)

[Seth Schrock]

I changed the names to FileCount and FileMonth and same results. I wish I new more about SQL so that I could analyze it on my end, but I'm affraid that this one is way over my head. Just to be sure, here is what I've got:

SELECT DCount("*","tblACHFiles",[tblACHFiles.EffectiveDate] <= [EffectiveDate] and Format([EffectiveDate],'yyyymm')=Format([EffectiveDate],'yyyymm') and ACHCompanyID = ACHCompanyID) AS FileCount, Month(EffectiveDate) AS FileMonth
FROM tblACHFiles
WHERE ("Format([EffectiveDate], 'yyyymm')=Format([EffectiveDate],'yyyymm')")
ORDER BY Month(EffectiveDate);
 

[Rabbit]

I haven't been following this thread too closely but the third parameter in the DCount should be surrounded by double quotes while the WHERE clause should not.

Also, I didn't read the prior posts so I assume there's a reason why DCount is used as opposed to a subquery.

[Seth Schrock]

I just tried that, and it didn't change anything. I don't know if there is a reason to not use a subquery. How would you suggest doing it with the subquery? I've used them once or twice, but I'm not too familiar with them. I'll give it a try as what I've got right now doesn't work yet.

[Rabbit]

It would help to see what it looks like after you've made the changes.

Also, after a closer look, the WHERE clause is not needed. And the where parameter in the DCount is incorrectly syntaxed.

[Seth Schrock]

I'm at home now, but it looks something like this:

FileCount FileMonth
15 6
15 6
15 6
15 6
15 6
15 7
15 7
15 7
15 8
15 8
15 8
15 8
15 8
15 9
15 9


Now that you mention the WHERE clause, I realize that it isn't needed. I added it because of post #9. I didn't think about it that it was already part of the DCount syntax.

What needs changed in the WHERE clause of the DCount () function?

[nico5038]

Can you attach a .mdb with (part of) the table here so I can try it tonight at home ?

Nic;o)

[NeoPa]

Remember to follow the instructions in Attach Database (or other work) when you do so to avoid unnecessary complications and delays.

[Seth Schrock]

Okay, because the real database has confidential information, I just created a new database and copied the design of the tables over and then added some fake data that is in the same format as the real thing. I also included the query (qrySpecialQuery). Just to make sure, I ran the query in the test database and it did the same thing. I also converted it to an Access 2003 file just incase. I have and use Access 2010, so if you need to convert it back to a higher version if you need to. Tell me if you need anything else. In the real database, the ACHCompanyID is related to my tblCustomer, but I didn't think that you would need that. If you do need anything else, just let me know.

[Rabbit]

Your where parameter in your DCount function is

"[tblACHFiles.EffectiveDate] <= [EffectiveDate] and Format([EffectiveDate],'yyyymm')=Format([EffectiveDate],'yyyymm') and ACHCompanyID = ACHCompanyID"

But if you're trying to reference the value in the query, then those references need to be outside of the quotes and appended to the string. Also, that first qualification of EffectiveDate is incorrect.

[Seth Schrock]

I've think I understand a bit of what you are saying, but I don't know how to fix it. I can move the first quote to be after the <=, but then wouldn't it try to match [tblACHFiles.EffectiveDate] <= the rest of the criteria? So should I put a quote after the last Format statement? Also, I'm not sure what you mean about the first qualification of EffectiveDate. Can you type out the code for what it should be?

[NeoPa]

Your Criteria parameter suffers from trying to refer to two separate items by the same name. [EffectiveDate] within the DLookup() call is not the same as the [EffectiveDate] of the current record within the query. As far as the call to SQL that the DLookup() function makes on your behalf goes, it has no understanding of the current state of the record within the query. Hence, the way to get this to work is to change the string to include the values as literal values. This is all a little more complicated to do of course. The other matter was that your reference to [tblACHFiles.EffectiveDate] is invalid as the square brackets ([]) must surround each element of the name individually if they're used at all. In this case [tblACHFiles].[EffectiveDate], tblACHFiles.EffectiveDate or even just [EffectiveDate] are equally valid (as there is no ambiguity of the source).

SELECT DCount('*',
              '[tblACHFiles]',
              '([EffectiveDate] <= #' & Format([EffectiveDate], 'm/d/yyyy') & '#) AND ' &
              '(Format([EffectiveDate],''yyyymm'') = ''' & Format([EffectiveDate],'yyyymm') & ''' AND ' & 
              '([ACHCompanyID] = ' & [ACHCompanyID] & ')') AS [Count]
     , Month(EffectiveDate) AS [Month]
FROM   ...

Some may prefer the use of double-quotes when quotes need to be embedded within quoted strings in SQL, but I'm a purist and prefer to use the SQL standard quotes when dealing with SQL strings (See Quotes (') and Double-Quotes (") - Where and When to use them).

Please bear in mind there are many (darn) quote chars in this code. I've tried to ensure they're all in the right place and balance up, but test it out to make sure and let me know if it doesn't work correctly.

[Seth Schrock]

Okay, it works almost perfectly. My only problem now is that if there are two records on one day, it counts them both as the same number. For example, if I have records on 9/7/11, 9/15/11, 9/16/11, and 9/16/11 the count is 1, 2, 4, 4. Is there a way to make it go 1, 2, 3, 4 or is this asking too much? Please tell me when I have reached the insanely difficult region.

Thank-you so much for all of your help.

[NeoPa]

Seth Shrock:
or is this asking too much?

Yes.

Unless you want to record the time then it's not possible to determine which of them comes first. The logic doesn't include randomisation, and that appears to be what you're looking for. Personally, I can't even imagine a scenario where your request (the latest part) makes sense, but then it's not my question so I can't say for sure.

[Seth Schrock]

Unfortunately it can happen where there are two records with the same EffectiveDate. I'll explain what I'm doing. I work at a bank that offers ACH services to certain businesses that bank with us. ACH files have a date when the money actually posts the account. This is called the Effective Date. This isn't the date that the file was created. I keep track of both, but we bill by the Effective Date. Would it be possible to use the combination of the EffectiveDate and the Creation Date and Time or is this again add lots of complication? I can't quite figure out the logic of what this query is doing, otherwise I would try to figure myself. I feel bad because I feel like I'm having you do all the work.
Attached is a slightly changed version of the database I already posted. I changed the DateEntered field to have default value of =Now() instead of =Date() and the format to General Date so that it would grab the time the file was created.

This is the last time I'll bug you about this problem.

[NeoPa]

Seth Shrock:
I changed the DateEntered field to have default value of =Now() instead of =Date() and the format to General Date so that it would grab the time the file was created.

As this is the first mention of [DateEntered] that I'm aware of I'm not sure how to incorporate that into the rest of your explanation. You should also understand that the Format of the field is pretty immaterial. Only the Type of the field is important for this.

[nico5038]

Just try this query:

 
SELECT tblACHFiles.ACHID, DCount("*","[tblACHFiles]","[ACHID] <=" & [ACHID] & " AND Format([EffectiveDate],'yyyymm') =" & Format([EffectiveDate],'yyyymm') & " AND [ACHCompanyID] = " & [ACHCompanyID]) AS MonthlyCount, Format([EffectiveDate],'yyyymm')  as YearMonth, tblACHFiles.EffectiveDate
FROM tblACHFiles
ORDER BY tblACHFiles.ACHID;
 

As you have a unique index, this can be used for an accurate count instead of the effectivedate.

Nic;o)

[Seth Schrock]

Nico5038, it worked perfectly!

NeoPa, sorry for the confusion. I didn't mention it before because I didn't think that it mattered. I brought it up because it was the one field that I could add the time value to it.

Again, thanks to everyone for your help.