Remove zero totals in query

[AccessKay]

I’m trying to find out how I can fix my query so that it doesn’t have records
with zero values. Karl Dewey helped me with a clever way to create a query
that pulls noncontiguous months for Labor_Cost totals. The criteria for the
query is selected from an unbound form with two text boxes. This query works
fine but then I needed to add some fields to it. I added fields, Category
and DGroup and it was okay but then I added Product and that’s when the lines
with zero totals showed up. It’s not for the same product. Here’s a visual
example:

Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $4598

There is no information for Prod25, so why does it appear in the query? I
tried to group it different ways but wasn’t able to get it to work. It's not
even in my table as a zero amount.

As always…I appreciate the help!

Note: I was a little confused about putting the IsNotNull And <>0 criteria
in the Where statement or the Having statement but got the same results.

My SQL is as follows:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between CVDate([Forms]![frmDialogBox]![txtMo1]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo1]))-1,[tblTrans_Mstr].[Labor_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between
CVDate([Forms]![frmDialogBox]![txtMo2]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo2]))-1,[tblTrans_Mstr].[Labor_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Labor_Cost) Is Not Null And
(tblTrans_Mstr.Labor_Cost)<>0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((tblTrans_Mstr.DGroup)="Labor"));

 

[Jessica]

If you do not want $0 to show up, you can put the <>0 in the criteria section
of each field you want it to apply to in the query design view. Just
remember that you will have to put it in eacy field (i.e. Product, Mo1, Mo2)
that you want to limit. I do not know the SQL statement right off the top,
but if done right, this should work.

 

[AccessKay]

Thanks Jessica for your reply. I did as you mentioned and put the <>0
criteria in some fields...then all fields, but still the same result. I have
no clue as to why that didn't work.

If you do not want $0 to show up, you can put the <>0 in the criteria section
of each field you want it to apply to in the query design view. Just
remember that you will have to put it in eacy field (i.e. Product, Mo1, Mo2)
that you want to limit. I do not know the SQL statement right off the top,
but if done right, this should work.
--
Jessica

I’m trying to find out how I can fix my query so that it doesn’t have records
with zero values. Karl Dewey helped me with a clever way to create a query
that pulls noncontiguous months for Labor_Cost totals. The criteria for the
query is selected from an unbound form with two text boxes. This query works
fine but then I needed to add some fields to it. I added fields, Category
and DGroup and it was okay but then I added Product and that’s when the lines
with zero totals showed up. It’s not for the same product. Here’s a visual
example:

Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $4598

There is no information for Prod25, so why does it appear in the query? I
tried to group it different ways but wasn’t able to get it to work. It's not
even in my table as a zero amount.

As always…I appreciate the help!

Note: I was a little confused about putting the IsNotNull And <>0 criteria
in the Where statement or the Having statement but got the same results.

My SQL is as follows:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between CVDate([Forms]![frmDialogBox]![txtMo1]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo1]))-1,[tblTrans_Mstr].[Labor_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between
CVDate([Forms]![frmDialogBox]![txtMo2]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo2]))-1,[tblTrans_Mstr].[Labor_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Labor_Cost) Is Not Null And
(tblTrans_Mstr.Labor_Cost)<>0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((tblTrans_Mstr.DGroup)="Labor"));

 

[Jessica]

I created a table, and then a query with the exact data you had below using
<>0 in the criteria, and I was able to get it to work. Below is the SQL form
the query:

SELECT Table1.[Datagory D], Table1.Group, Table1.Product, Table1.[Mo 1],
Table1.[Mo 2]
FROM Table1
WHERE (((Table1.[Mo 1])<>0) AND ((Table1.[Mo 2])<>0));


Of course, the table names will not match yours, but the WHERE clause is
what you really need most here, as it breaks down the criteria. Hope this
helps.
--
Jessica

Thanks Jessica for your reply. I did as you mentioned and put the <>0
criteria in some fields...then all fields, but still the same result. I have
no clue as to why that didn't work.

If you do not want $0 to show up, you can put the <>0 in the criteria section
of each field you want it to apply to in the query design view. Just
remember that you will have to put it in eacy field (i.e. Product, Mo1, Mo2)
that you want to limit. I do not know the SQL statement right off the top,
but if done right, this should work.
--
Jessica

I’m trying to find out how I can fix my query so that it doesn’t have records
with zero values. Karl Dewey helped me with a clever way to create a query
that pulls noncontiguous months for Labor_Cost totals. The criteria for the
query is selected from an unbound form with two text boxes. This query works
fine but then I needed to add some fields to it. I added fields, Category
and DGroup and it was okay but then I added Product and that’s when the lines
with zero totals showed up. It’s not for the same product. Here’s a visual
example:

Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $4598

There is no information for Prod25, so why does it appear in the query? I
tried to group it different ways but wasn’t able to get it to work. It's not
even in my table as a zero amount.

As always…I appreciate the help!

Note: I was a little confused about putting the IsNotNull And <>0 criteria
in the Where statement or the Having statement but got the same results.

My SQL is as follows:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between CVDate([Forms]![frmDialogBox]![txtMo1]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo1]))-1,[tblTrans_Mstr].[Labor_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between
CVDate([Forms]![frmDialogBox]![txtMo2]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo2]))-1,[tblTrans_Mstr].[Labor_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Labor_Cost) Is Not Null And
(tblTrans_Mstr.Labor_Cost)<>0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((tblTrans_Mstr.DGroup)="Labor"));

 

[AccessKay]

Thanks Jessica...it worked when I typed it into the SQL screen per your
statement. I'm not sure why it wasn't working in design view...probably me.
But it works now and I'm very please. I appreciate your help.

Kay

I created a table, and then a query with the exact data you had below using
<>0 in the criteria, and I was able to get it to work. Below is the SQL form
the query:

SELECT Table1.[Datagory D], Table1.Group, Table1.Product, Table1.[Mo 1],
Table1.[Mo 2]
FROM Table1
WHERE (((Table1.[Mo 1])<>0) AND ((Table1.[Mo 2])<>0));


Of course, the table names will not match yours, but the WHERE clause is
what you really need most here, as it breaks down the criteria. Hope this
helps.
--
Jessica

Thanks Jessica for your reply. I did as you mentioned and put the <>0
criteria in some fields...then all fields, but still the same result. I have
no clue as to why that didn't work.

If you do not want $0 to show up, you can put the <>0 in the criteria section
of each field you want it to apply to in the query design view. Just
remember that you will have to put it in eacy field (i.e. Product, Mo1, Mo2)
that you want to limit. I do not know the SQL statement right off the top,
but if done right, this should work.
--
Jessica


:

I’m trying to find out how I can fix my query so that it doesn’t have records
with zero values. Karl Dewey helped me with a clever way to create a query
that pulls noncontiguous months for Labor_Cost totals. The criteria for the
query is selected from an unbound form with two text boxes. This query works
fine but then I needed to add some fields to it. I added fields, Category
and DGroup and it was okay but then I added Product and that’s when the lines
with zero totals showed up. It’s not for the same product. Here’s a visual
example:

Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $4598

There is no information for Prod25, so why does it appear in the query? I
tried to group it different ways but wasn’t able to get it to work. It's not
even in my table as a zero amount.

As always…I appreciate the help!

Note: I was a little confused about putting the IsNotNull And <>0 criteria
in the Where statement or the Having statement but got the same results.

My SQL is as follows:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between CVDate([Forms]![frmDialogBox]![txtMo1]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo1]))-1,[tblTrans_Mstr].[Labor_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between
CVDate([Forms]![frmDialogBox]![txtMo2]) And
DateAdd("m",1,CVDate([Forms]![frmDialogBox]![txtMo2]))-1,[tblTrans_Mstr].[Labor_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Labor_Cost) Is Not Null And
(tblTrans_Mstr.Labor_Cost)<>0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((tblTrans_Mstr.DGroup)="Labor"));