Query Help

[Neo-fite]

I'm not getting my Query to work right, please help.

I'm using 3 Fields [Book Yr], [Costs] and [Name]...what I'm looking to get is a breakdown/Group by Book Yr of the Count of Name and the Sum of Costs where [Costs] > 0.

I am able to group by the year and and get the correct sum of costs, but the Count of Name includes ALL the records (regardless of Costs).

 

[vbaInet]

You want it to exclude the Nulls in the Name field? How are you counting?

 

[Neo-fite]

Here's my SQL...I removed parens for clarity

SELECT [Book Yr], Count([Name]), Sum([Costs])
FROM [tblName]
GROUP BY [Book Yr]
HAVING [Book Yr]>=2003 AND Sum([Costs])>0
ORDER BY [Book Yr];

 

[vbaInet]

I don't see anything wrong with the SQL statement.

What do you expect to get?

Can you show me some sample records?

 

[Neo-fite]

I even changed the Count([Name]) to Count([ID]) and got the same results...I reviewed the records to confirm that [Costs] did not have any NULL (and even added that to the criteria.

I ran a pivot table from my Excel version of the data as a check point and the Costs match, but the count is 384 in excel and 1139 in Access.

 

[vbaInet]

Alright, you want to count all the records based on the filter?

Remove the Count, create a new query based on your Sum query and perform your count in there.

 

[Neo-fite]

OK...by chance, I figured it out.

I had to add WHERE [Costs]>0 to the existing code.

 

[vbaInet]

Good stuff!

 

[Neo-fite]

Is it possible to have the result display all the years >2003, even when the results are zero?

 

[vbaInet]

The result of what field?

 

[Neo-fite]

The result of what field?

[Book Yr]...currently, if the other results are 0, then that Book Yr does not appear.

For example, I am looking for query results for 2000-2009, my results might only show 2000,2001,2002,2004,2005,2008,2009...but I would like 2003 & 2006,2007 to show results of 0 across the board. (if that makes sense)

 

[vbaInet]

In that case you need to change the way your Sum() works. So using your example of 2000 to 2009:

Sum(Nz([Costs], 0))

 

[Neo-fite]

In that case you need to change the way your Sum() works. So using your example of 2000 to 2009:

Sum(Nz([Costs], 0))

So, I used the following in part of my SQL Code in Excel/ADO

Sum(NZ([MISCCost],0))>0

It causes and error that NZ in an undefined function.

 

[vbaInet]

What do you mean by Excel/ADO? Where did you put the code? In a query?

 

[Neo-fite]

I'm using the SQL in an Excel Macro that is connected to the Access db, therefore, my Query results are loaded directly into Excel.

 

[vbaInet]

What version of Excel is this?

 

[Neo-fite]

What version of Excel is this?

2007...I use Access to create my queries, then utilize the SQL in the Excel VBA

Here in my SQL...I tried using the NZ in Access, but was not able to get the missing year(s) to appear.

SELECT [Book Yr], Count([ID]) AS [Instances], Sum([Cost]) AS [Total Cost]
FROM [tblName]
WHERE [Cost]>0 AND [CustomerName]="Dewey, Cheatum & Howe"
GROUP BY [DateYear]
HAVING [DateYear]>"1999" AND Sum([Cost])>0;

 

[vbaInet]

You have lots of criteria in place.