Count "0" rather than not including data

[tozey]

Hello all

This is my first ever post, first of all thank you to everyone as this forum has been a godsend at times. Excellent work.

I have been looking for the answer to this for some time and cannot find it anywhere. I am hoping you can help.

I am looking to have the result of a count query show all of my catagories even if there are no records against them. Let me explain in food format.

I have various foods with a count of how many orders there are for them
I have a foods table and a orders table

Chips 45
Pizza 12
Bread 12

I also have Beans but as no beans are in ther orders table it doesnt give me a result. I would like it to show:

Chips 45
Pizza 12
Bread 12
Beans 0

I hope this makes sense, I tired to explain it as simple as possilble.

Thanks in advance for your help.

Alex

 

[namliam]

You are "inner joining" your food and order tables, as a result it will not show any food that doesnt have an order, therefor not even showing the food to be counted as 0.

"outer join" your Order table and you should get the result you desire.
Double click the line between the Food and Order tables, an option pops up with 3 options. Option 1 is the (default) inner join, Options 2 and 3 are outer joins, Left and Right... Depending on your situation you want one or the other, just read the description.

 

[tozey]

Thank you for the prompt response namliam.

I still have the same problem doing it this way, I have tried both combinations and it is still not giving me a 0, it just leaves out the beans.

It says in the descripotion of the join, include all records from table 1 and only those from table 2 where the join fields are equal. Yes the joins are equal but the food type appears in one table not the other.

Thanks for your suggestion, are you or anyone else able help further?

Thanks again

Alex

 

[namliam]

Still, this has to be your solution...

Are you displaying the Beans from your food table, not from your order table??

 

[tozey]

namliam, thank you, i have now got it working. Thank you so much for the advice.

Alex

 

[adyas]

Namliam

This thread has been really helpful as I have had a similar problem. However can I ask one further question please?

I have all records showing but those without content are blank (i.e. no zero). This means that when I try to add the values to make a final total the result is a blank field.

Is there anyway to add these fields together even with blanks?

 

[namliam]

The NZ function will save your day, or is it NVL, either/or... I forget I use to many

Think its NZ, however if you get blank (i.e. NULL) values you are not using count

 

[adyas]

Ha Ha you are absolutely right, I've used the sum function to get my totals in 3 queries and put in a formula to calculate the total in another 2 queries. I am producing an estimate and what I need to do is have all totals on one screen with the grand total appearing also.

You are correct again, it is the NZ function but I'm afraid the Help menu is reading like gobbledigook!!! I am unfamiliar with VB and I'm not sure where to start with this.

I've attached a screen dump of one of the queries I'm using.
If an estimate has been produced but this section (furniture) is left blank it now shows (thanks to you) but as a null. I need it to show a zero so that I can use a formula to give me a total. Do I need to use the NZ function in the query or in a macro. I'm completely lost as I have limited experience of functions.

 

[namliam]

you have to use NZ(Yourfield,0) around any field that can be Null, really any numberfield should always have a default 0 value not a null value...

But yeah, any value that potentially is Null, NZ(Value,0) or for text NZ(YourText,"IT IS BLANK") or what ever you want

 

[namliam]

Oh, I will save you the lecture of a good naming convention and the use of "illegal" characters such as spaces in column/table names

I suggest you read up on "naming convention" a bit more, to prevent yourself getting the lecture in the future from me or one of many others

 

[adyas]

Thank you very much for your help. I'll give it a go now.

I'm self taught so I'll be happy to take your advice on naming conventions. I shall read up immediately ;o)

Thanks again. You're a lil gem.

 

[adyas]

namliam, I'm still missing something.

As you pointed out I do set all my number fields to default value 0.

However the field that is showing up as null is one I have created in the query and I can't find where to set this to default to 0 (if in fact it is possible).

So, following your earlier advice, I put the formula around both fields in my newly created Total field, i.e.

Total: NZ([field1],0)*NZ([field2],0)

but still no 0 appears.

Can you see from this what I'm doing wrong?

 

[namliam]

You shouldnt be doing anything wrong, double check your fields actualy number fields... other than that you got me up against a wall

Can you post (part of) your db, then I can have a direct look.

 

[adyas]

Woohoo, got it working!!! )

I had set the default value to 0 in all of the tables. What I hadn't done was made sure ALL fields in the queries were also set to either currency or general number.

This now done, its working beautifully. I can't thank you enough, I'd never have found this on my own. This forum is fab.

Thanks again for your time and patience, its very much appreciated.

Best wishes
Al

 

[amisieudinh]

Namliam
Hi, this is exactly what I'm having problem with.
Although I've read all the things you said but still cannot have it work.
This is my database. I have some copies of book with status Y(means on hand) or N( means Sold), that Copy table is linked to Book table.
I want to count the inventory on hand after each period, but access didn't show 0 value( only 1-2-3... shown), while I really need that number.
This is my database, and the query name is Inventory List, can you help me through this?
Thank you!!!

 

[namliam]

I cannot open your database as I only have A2000 available to me

You probably have something like
Select YourBook, count(*)
from Yourtable
Where Status = "Y"
Group by YourBook

Try something like so:
Select YourBook
, SUM(IIF(Status="Y",1,0)) OnHandCount
, SUM(IIF(Status="N",1,0)) SoldCount
from yourTable
Group by YourBook

There are other options like doing an "outer join" or doing a "union" but without your DB or Your setup, thats going to be hard to explain.