How do return a zero value in a query

[acccessnewb]

Hi all,


I have a table with company inventory.
On that table there is a "Status" column and a "Sub Category" Column.

I'm trying to display a count of the items in the subcategory that is "In Stock" and is "Issued".

I was able to write a query that shows items that are Issued and In stock but if there aren't any items in stock it doesn't return a zero value for in stock.

How do I return a zero value when there aren't any item listed as "In Stock" or "Issued" in the table?

Thanks!!

Here is my query so far:
SELECT [Asset Table].Status, [Asset Table].[Sub-Category], Count([Asset Table].[Sub-Category]) AS [CountOfSub-Category]
FROM [Asset Table]
GROUP BY [Asset Table].Status, [Asset Table].[Sub-Category]
HAVING ((([Asset Table].Status) Like "In Stock" Or ([Asset Table].Status)="Issued"))
ORDER BY [Asset Table].[Sub-Category];

Here's a screen shot of my current result:
As you can see it will only show items with a value. "Bear Kit" for example actually has items in stock and issued in the table and as a result shows both.


Thanks in advance!!

[rpeare]

You'd have to link the sub categories table (the list of unique sub category items) and create a left join (arrow points from the category table to this query) on the category ID if you're using a primary key in your table. That would show all your sub categories and fill in the counts where appropriate then you can use the nz() function to zero out anything that has a null value.

[acccessnewb]

You'd have to link the sub categories table (the list of unique sub category items) and create a left join (arrow points from the category table to this query) on the category ID if you're using a primary key in your table. That would show all your sub categories and fill in the counts where appropriate then you can use the nz() function to zero out anything that has a null value.

Thanks for writing me back.

I'm still having a heck of a time with this query! I separated the Subcategories into a different table and used a left join but it still only shows the data that is there, not the data that isn't. I need it to show an In Stock Count and an Issued Count in the query regardless what is in the table.

Is there a way to do a count based on the status (Issued and In Stock) against the item? And if no items are set as Issued in the table for it return a zero count?

Here's what I have for code so far:
SELECT [Asset Table].Status, Count(AssetTableSubCategory.Category) AS CountOfCategory, [Asset Table].Item
FROM AssetTableSubCategory LEFT JOIN [Asset Table] ON AssetTableSubCategory.[ID] = [Asset Table].[DontUseSubcategory]
GROUP BY [Asset Table].Status, [Asset Table].Item;





In the above screenshot I need it to show both the In Stock and Issued count for each category item (4", 435, 6" etc....)

[rpeare]

So let me approach this from a different direction, based on the data shown above could you not have something like:

Item	In Stock	Issued
4"	1	0
435	4	0
6"	1	0
ACT II - HQ	1	0
ACT II - HQ - Controller	1	0
ATV Helmet	1	0
Field Laptop	1	1

If this is an acceptable format and every time in your inventory is either in stock or issued you shouldn't have to do anything crazy

You could just use the GROUP BY clause to group on the item name and in your 'in stock' and 'issued' columns have an equation like:

Tot Issued: iif([status] = 'Issued', 1, 0)

and in the TOTALS line (assuming an aggregate query) you would use the SUM function

[acccessnewb]

That worked like a charm thank you very much!! I have attached a screenshot of the query in the event this helps anyone else. I added a total expression as well.

So let me approach this from a different direction, based on the data shown above could you not have something like:

Item	In Stock	Issued
4"	1
435	4
6"	1
ACT II - HQ	1
ACT II - HQ - Controller	1
ATV Helmet	1
Field Laptop	1	1

If this is an acceptable format and every time in your inventory is either in stock or issued you shouldn't have to do anything crazy

You could just use the GROUP BY clause to group on the item name and in your 'in stock' and 'issued' columns have an equation like:

Tot Issued: iif([status] = 'Issued', 1, 0)

and in the TOTALS line (assuming an aggregate query) you would use the SUM function