Why does COUNT give the wrong result and how do I display periods without data?

[petter]

Hi!
I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month.

I have an Access database where I want to create a query to count all the internal units in my list (the “input”-table), between two dates. I want my list to be grouped by year and month. Here is the SQL-code, as called from VBA:

strSQL_INTU1 = "PARAMETERS [From Date] DateTime, [To Date] DateTime;" _
    & "SELECT Format([the Date],""yyyy mm"") AS [Month], " _
    & "[Unit] AS [INT]" _
    & "INTO[INTU1]" _
    & "FROM [input]" _
    & "WHERE ((([the Date]) Between [From Date] And [To Date]))" _
    & "GROUP BY Format([the Date],""yyyy mm""), [Unit]" _
    & "ORDER BY Format([the Date],""yyyy mm"") ASC;"
 
strSQL_INTU = "SELECT [Month] AS [WhatMonth], COUNT([INT])/21 AS [INTU] " _
    & "INTO[INTU]" _
    & "FROM [INTU1]" _
    & "GROUP BY [Month]" _
    & "ORDER BY [Month] ASC;"

I use the first SQL-string to create a query that creates a table. This table is used in the second query. I know this is probably not the best way to do it, but it works for me. The first query uses parameters to select the date range, and then format those dates to only display month and year. It takes the records where the “Unit”-field is populated, groups the results per month and creates a new table. This table is used in the second query, where the number of records (unique unit-entries) per month is counted and saved to a new table.

First the COUNT-question: when I use the COUNT-function to count how many records that satisfies my request, I get an answer that is 21 times as high as the real answer (that’s why I have divided by 21 in the code). Why is this? I get the right answer, but I don’t like to do it this way. Is there a way to get the right result without having to divide by 21?

The other question: for the months where there are no data in the table, I would like to display a zero value (instead of nothing) in the final table (the “INTU”-table). My “input”-list haven’t got entries for all months, and I haven’t figured out how to solve that.

I would really appreciate if anyone could help me out with this!

[puppydogbuddy]

Hi!
I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month.

I have an Access database where I want to create a query to count all the internal units in my list (the “input”-table), between two dates. I want my list to be grouped by year and month. Here is the SQL-code, as called from VBA:

First the COUNT-question: when I use the COUNT-function to count how many records that satisfies my request, I get an answer that is 21 times as high as the real answer (that’s why I have divided by 21 in the code). Why is this? I get the right answer, but I don’t like to do it this way. Is there a way to get the right result without having to divide by 21?

The other question: for the months where there are no data in the table, I would like to display a zero value (instead of nothing) in the final table (the “INTU”-table). My “input”-list haven’t got entries for all months, and I haven’t figured out how to solve that.

I would really appreciate if anyone could help me out with this!

Your First Question:
----------------------------
The parameter declaration should not be part of your sql statement....it is a separate declaration that passes parameters to the sql statement (proper syntax is shown below). If parameters are included in the sql, they will cause an overlap in the code execution.

PARAMETERS [From Date] DateTime, [To Date] DateTime; 
strSQL_INTU1 =  "SELECT Format([the Date],""yyyy mm"") AS [Month], " _
'______________________
 
 

Your Second Question
----------------------------------
Use Access's "null to zero" (nz) numeric function to replace a null with 0.as shown:

& "nz([Unit],0) AS [INT]" _

[petter]

Thanks for your reply! Unfortunately, I haven’t got it right anyway.

The first question:
I removed the parameter declaration from the SQL query, but I still get the wrong result (same as before). This is how I execute the queries:

    Set qryDef_INTU1 = dbs.CreateQueryDef(strQueryName_INTU1, strSQL_INTU1)
    qryDef_INTU1.Parameters(0) = fromDate
    qryDef_INTU1.Parameters(1) = toDate
    qryDef_INTU1.Execute
    Set qryDef_INTU = dbs.CreateQueryDef(strQueryName_INTU, strSQL_INTU)
    qryDef_INTU.Execute
 

Are there any errors here?

The second question:
When I import my data (as an excel file), there are some months that don’t have any records. If that is the case, I don’t even have a date. Is it possible to use NZ in that case? I don’t understand how. My guess it that I need to create another table, with all months in the time range, and in some way join the tables. But I haven’t got that to work either…

[puppydogbuddy]

What happens when you execute your SQL this way? And yes, you can try NZ for the date. if NZ does not work for the date, then you will have to join another table, using an outer join.

PARAMETERS [From Date] DateTime, [To Date] DateTime; 
strSQL_INTU1 =  "SELECT Format(nz([the Date],0),""yyyy mm"") AS [Month], " _ 
    & "nz([Unit],0) AS [INT]" _
    & "INTO[INTU1]" _
    & "FROM [input]" _
    & "WHERE (((nz([the Date],0)) Between [From Date] And [To Date]))" _
    & "GROUP BY Format([the Date],""yyyy mm""), [Unit]" _
    & "ORDER BY Format([the Date],""yyyy mm"") ASC;"
 
strSQL_INTU = "SELECT [Month] AS [WhatMonth], COUNT([INT]) AS [INTU] " _
    & "INTO[INTU]" _
    & "FROM [INTU1]" _
    & "GROUP BY [Month]" _
    & "ORDER BY [Month] ASC;"
 
 
CurrentDb.Execute strSQL_INTU1, DbFailOnError
 
CurrentDb.Execute strSQL_INTU, DbFailOnError 

[NeoPa]

pDog,

It looks like you've been having a (very rare) brainstorm :(
The PARAMETERS SQL does need to come before the main SQL as you say, but assuming the strSQL_INTU1 string is executed, it should work ok as originally posted.

@Petter, As far as the Count() not working goes, I can see no reason, from the SQL posted, why this might be the case :S Are you sure your expectations aren't somehow skewed? Does the total # of records match what you expect to see in the table [INTU1]?

As far as providing entries for months which don't appear in your input goes, this is a little more fiddly. Essentially you need to find a way of producing a data source which provides these months. With this data source on the 'left' of your query, you need to add your [INTU1] data with a LEFT JOIN.

The data source availability depends heavily on what is available to you, but in the worst case you can set up the data in a table using some code.

PS. pDog, please don't take this as criticism. I only mention it because the usual quality of your posts is so consistently high (and maybe I have actually misunderstood something here).

[NeoPa]

I meant to respond yesterday, but ran out of time. This morning there was still no extra response so I posted - only to find two new posts in there once the thread refreshed.

I think it's pretty well still relevant though :)

[puppydogbuddy]

pDog,

It looks like you've been having a (very rare) brainstorm :(
The PARAMETERS SQL does need to come before the main SQL as you say, but assuming the strSQL_INTU1 string is executed, it should work ok as originally posted.

@Petter, As far as the Count() not working goes, I can see no reason, from the SQL posted, why this might be the case :S Are you sure your expectations aren't somehow skewed? Does the total # of records match what you expect to see in the table [INTU1]?

As far as providing entries for months which don't appear in your input goes, this is a little more fiddly. Essentially you need to find a way of producing a data source which provides these months. With this data source on the 'left' of your query, you need to add your [INTU1] data with a LEFT JOIN.

The data source availability depends heavily on what is available to you, but in the worst case you can set up the data in a table using some code.

PS. pDog, please don't take this as criticism. I only mention it because the usual quality of your posts is so consistently high (and maybe I have actually misunderstood something here).

Adrian,
Not to worry. I don't get upset over constructive criticism. It is just that I have never ever seen a parameters declaration within the sql itself before.....

pDog

[puppydogbuddy]

Petter,
see this link:
Count Function

Try using Count(*) instead of Count([YourField]) if the field you are counting returns a null for some rows, and you want those rows included in the Count.

[petter]

Thanks for your help!
pDog: I tried to execute the SQL code in the way you proposed. I guess that I should execute the parameters-statement in some way to? I did it like this:

strPar = “PARAMETERS [From Date] DateTime, [To Date] DateTime;”
…
CurrentDb.Execute strPar
 

Is that the right way? This will save me some lines of code since I can re-use the same parameters for several queries, thanks!

I get the records for the months were there are no data, but the problem still remains for the months were I don’t have a date at all. I need to do some kind of join. Is there a way to create a list of the months between “From Date” and “To Date” and join the tables so that all those months appear in the final table?

The COUNT problem also remains. I have counted the number of records in my input-table and compared them to the result in the INTU-table. The result that I get is the number in the input-table multiplied by 21. The result that I’m looking for is actually the number of unique Units per month. That’s why I first group the table on month and unit, and then count the number of records for each month.

Is there any more information that I can provide for this?

[puppydogbuddy]

Petter,
Here is an example of the syntax for using parameters with an sql select statement. see this link:
http://msdn.microsoft.com/en-us/library/bb208916.aspx

' Define the parameters clause.
    strParm = "PARAMETERS [Employee Title] CHAR; "
 
    ' Define an SQL statement with the parameters
    ' clause.
    strSql = strParm & "SELECT LastName, FirstName, " _
        & "EmployeeID " _
        & "FROM Employees " _
        & "WHERE Title =[Employee Title];"

Did you try Count(*)....what happened with that?

[petter]

Hi,

Thanks again for your help. I haven't been able to work with this the last days, but today I have tried again.

The Count-problem: I have tried to use COUNT(*) instead of just counting the field, but it don't make any difference. I still get the same, to high, result.

The way I do this is by creating queries in Access and use the result tables. Would it make any difference if I would do it in another way? How should I do that in that case?

I would really like some help with the table joins as well...

[puppydogbuddy]

Hi,

Thanks again for your help. I haven't been able to work with this the last days, but today I have tried again.

The Count-problem: I have tried to use COUNT(*) instead of just counting the field, but it don't make any difference. I still get the same, to high, result.

The way I do this is by creating queries in Access and use the result tables. Would it make any difference if I would do it in another way? How should I do that in that case?

I would really like some help with the table joins as well...

I can't see anything wrong with the way you have used the count function in your query. Could you show us what the data looks like in the result table?

In re: the fiscal period table:

As an example, in tblFiscalPeriod, you have fiscal months [FiscalMonth] as numbers 1-12 with the [StartDate] for 1 = 01/01/2007 and an [EndDate] of 01/31/2007, FiscalMonth = 2 - 02/01/2007 - 02/28/2007 etc.

Further, if you have a one to many relation set between the fiscal table and the operating data table, you can create a right join to select all fiscal periods from the Fiscal period table and matching data from the data table. This will extract fiscal dates with no activity.

Here is an illustration of how you would incorporate a fiscal table in a query.

SELECT b.FiscalMonth, a.[OperationalDataDate], a.Field2, a.Field3
FROM tblYourOperatingDataTable As a, tblFiscalPeriod As b
WHERE a.[OperatingDataDate] >= b.[StartDate]
AND a.[OperatingDataDate] <= b.[EndDate]

[petter]

I have tried a new solution for the table join that works when I write it in query designer (SQL view), but not when I call it from the VBA code. I have created a table (manually) were I store every month, and then join that table with my result tabel. Also, I have another table (the TTable) with data.

I just wonder why it don't work from the VBA code:

strSQL_RW = "SELECT dates.Month, RW1.ReWe AS RW," _
    & "(SELECT Last(TTable.WRW) FROM TTable) AS W," _
    & "(SELECT Last(TTable.RRW) FROM TTable) AS R," _
    & "(SELECT Last(TTable.CRW) FROM TTable) AS C," _
    & "(SELECT Last(TTable.SRW) FROM TTable) AS S INTO RW " _
    & "FROM dates LEFT JOIN RW1 ON dates.Month=RW1.Month " _
    & "GROUP BY dates.Month, RW1.ReWe " _
    & "ORDER BY dates.Month ASC;"
 

Hopefully, I will get more time to look into the Count problem again soon.
BR Petter