account for 0

[pyromaniac]

I have seen a bunch of others with this question but I can't get the solutions to work. I have tried Nz and IIf but maybe not correctly. Would some one please help me understand how to make my query produce zeros instead of nothing at all.

SELECT Baseline.MDS, Count(Baseline.[Lesson Type]) AS CBI
FROM Baseline
GROUP BY Baseline.MDS, Baseline.[Lesson Type]
HAVING (((Baseline.[Lesson Type])=4))
ORDER BY Baseline.MDS;


Thank you

 

[plog]

You can't create data in a query where there is none. If there are MDS values in Baseline without their [Lesson Type]=4, then that MDS value will not show up.

What you can do is bring in another datasource that has all the MDS values you want in your results, use that in your FROM clause and LEFT JOIN Baseline to it and then run this query.

Additionally, its kind of weird that you are grouping by [Lesson Type] but its not in the SELECT clause. Why is that?

 

[pyromaniac]

I honestly have no Idea. I touched mySQL when I was in school and this is my first time in access. Most of what I have been done is with the GUI and tutorials. I'm looking at it like it's a huge mess but haven't figured out how to do it right.

So basically I am trying to make a product while learning a tool.

I'm going to give your suggestion a shot.

 

[jdraw]

There are sql tutorials on youtube and a good one at W3Schools

Steve bishop has a full set of free tutorials on Learning Access on youtube

 

[BlueIshDan]

I've a hunch this is a more simple than it appears to be.

SELECT [COLOR="RoyalBlue"]DISTINCT[/COLOR] Baseline.MDS, Count([COLOR="SeaGreen"]Nz([/COLOR]Baseline.[Lesson Type][COLOR="SeaGreen"])[/COLOR]) AS CBI
FROM Baseline
GROUP BY Baseline.MDS[COLOR="Red"], Baseline.[Lesson Type]
HAVING Baseline.[Lesson Type] = 4[/COLOR]
ORDER BY Baseline.MDS;

Also if you're using Nz([field]) then placeing it in the condition statement for [field] = 4. What's the point of that? lol
My instinct tells me that the text is red is not needed. Been awhile since I've been deep into sql lol.

You may benefit from using DISTINCT as well.

 

[BlueIshDan]

Also based on this query, it looks like you need to normalize your tables.

 

[jdraw]

pyromaniac,

It appears that you are having issues with
SQL, Access and database generally.

You can get functions syntax and usage examples at techonthenet.

What exactly is your goal? Are you trying to learn database, Access, SQL????

 

[pyromaniac]

I was told by a coworker that I explained my issue poorly. I have uploaded a picture of what I am trying to achieve. I apologize for the bad writing, I was still waking up when I made it.

I'm still going through all your suggestions at the moment.

***EDIT***

Yes I am new to this software, Database aren't really my thing but I am being told to make something so I am doing what I can. Basically what I am trying to do is make a list with a count of that type, whether it's 0 or 1000.

 

[jdraw]

For reference:
When explaining/describing an issue it is best to start with a 30,000 foot overview to help give the reader some context for the issue/opportunity. Keep it simple, plain English and use the old what, where, when, how and how often approach. It is often best to show an example of the initial data and a sample of your desired output data/format if applicable.

Readers do not know you or your environment. So keep it simple until you are sure there is comprehension. Too many posts go on and on just trying to describe/explain an issue in a manner that readers can understand.

Good luck.

 

[CJ_London]

plog's solution is the correct solution - you said you would give it a shot - how did you get on?

 

[pyromaniac]

I'm still trying it. All the tutorials I have found have been for 2 tables. Mine are currently in one table and things were counted by queries. Maybe this was wrong way to set up?

 

[CJ_London]

you need a table with all your lessontypes which you left join to your baseline table. This will ensure all lessontypes will appear, even if there are none for that particular MDS in the baseline table.

 

[pyromaniac]

I started playing with the noodles and connected the table to a query and it gave me what I was looking for. Kind of shocked myself.

SELECT Baseline.MDS, countCBI.CBI
FROM Baseline LEFT JOIN countCBI ON Baseline.MDS = countCBI.MDS
GROUP BY Baseline.MDS, countCBI.CBI;


Thanks everyone for pointing me in right direction. I had never even heard of a join or left join before today.

 

[jdraw]

Thanks for getting back to us.

You may be interested in working through this tutorial from RogersAccessLibrary.
It has a lot of database and table design concepts, but you have to do the exercise (which has a solution).