Need to count what's not there.

[myblueshadow]

I'm trying to do a query to count corresponding records in another table. It works except for returning zeros. I've tried using NZ and switching the type of join, but to no avail. I need help!!

Here's what I have:

Query A has 3 columns (FU kids)
AlphaID
DtcCtr (a Location Code)
DlsDtc (a Date of change)

Table A has many columns, but I'm only using a few. (dbo_MNCPSTNote)
AlphaID
DtcCtr (the same Location Code)
ServDate (the date I'm trying to count)

Here's what I have:

SELECT [FU kids].AlphaID, [FU kids].DtcCtr, Count(dbo_MNCPSTNote.ServDate) AS CountOfServDate
FROM dbo_MNCPSTNote RIGHT JOIN [FU kids] ON dbo_MNCPSTNote.AlphaID = [FU kids].AlphaID
WHERE (((dbo_MNCPSTNote.Center)=[FU kids]![DtcCtr])) OR (((dbo_MNCPSTNote.ServDate)>[FU kids]![DlsDtc]))
GROUP BY [FU kids].AlphaID, [FU kids].DtcCtr;


I want to know the count, including zero, of the number of records based on ServDate for each AlphaID in Query A. I hope that makes sense.

Just FYI: I didn't create the tables and have no control over how they are designed/organized, which can be quite frustrating!!

 

[plog]

I don't think its possible with your join type and your WHERE clause. You have a RIGHT JOIN, however you're WHERE clause requires data in both sides (dbo_MNCPSTNote and [FU kids]) to be evaluated. If no records are found on one side of the JOIN then it fails. You will never be able to find a zero value because a zero value will never make it through your WHERE clause.

I can't get my head entirely around your query, but I do know that a sub-query will probably do the trick. That sub-query would be based on the data source that has all the values you want to report on (probably [FU kids] since its the one being grouped) Then you would create a new query based on that sub-query and LEFT JOIN the other data source (probably dbo_MNCPSTNote since its what you are counting).

If you can provide sample data from both tables and then what you expect the query to produce based on that sample data, I can provide more specific help.

 

[myblueshadow]

First, thank you so much for helping me!!

Here is the sample data:

FU kids:

AlphaID DtcCtr DlsDtc
ABRAJ1 02 01-May-09
ABUKA1 04 01-Nov-09
ACEVM1 05 25-Jun-03
ACOFA1 11 27-Jun-05
ACOFJ1 07 30-Aug-04

MNCPSTNote:

AlphaID DtcCtr ServDate
ABRAJ1 02 3/12/2009
ABRAJ1 02 4/5/2011
ABRAJ1 02 9/16/2012
ABUKA1 04 12/4/2009
ABUKA1 04 5/21/2010
ACEVM1 05 5/10/2004

What I want:

AlphaID DtcCtr Count of ServDate
ABRAJ1 02 2
ABUKA1 04 2
ACEVM1 05 1
ACOFA1 11 0
ACOFJ1 07 0


The FU kids is a list of kids in a certain category. They enter that category on the DlsDtc date. The MNCPSTNote is where the notes for a service received are entered. They can have multiple notes or no notes. I want to know how many notes they have (even when it's zero) after they enter that category.

Oh I was able to filter out the center from my first example so that I don't have to include it in this query if that makes it simpler.

 

[plog]

Good explanation and data. Below is the SQL that will provide you with what you want:

SELECT [FU kids].AlphaID, [FU kids].DtcCtr, Count(MNCPSTNote.ServDate) AS CountOfServDate
FROM [FU kids] LEFT JOIN MNCPSTNote ON [FU kids].AlphaID = MNCPSTNote.AlphaID
WHERE IsNull([ServDate]) Or [ServDate]>[DlsDtc]
GROUP BY [FU kids].AlphaID, [FU kids].DtcCtr;

The above query will provide one record for every record in [FU kids]. I was able to avoid the sub-query because [FU kids] contained unique AlphaID data. The real trick in producing the 0 values was the IsNull(([ServDate]) in the WHERE clause. Without that it would only return the records that existed, this way it will produce 0 values.

Let me know if you have any questions or this doesn't exactly meet your needs.

 

[Privateer]

While I don't doubt plog's solution, the count feature will put a zero in there as long as the tables are linked with a left join. You are saying, give me all the kids and count any that match. So where there is no match, count evaluates to a zero. Try this, it's basically the same thing, but without the criteria.

Select [FU Kids].AlphaID, Count(MncpstNote.ServeDate) AS CountOfServeDate, FROM [FU Kids] LEFT JOIN [MncpstNote] ON [FU Kids].AlphaID = [MncpstNote].AlphaID GROUP BY [FU Kids].AlphaID;

 

[plog]

the count feature will put a zero in there as long as the tables are linked with a left join.

That was the whole point of his post--he wants those 0's to appear and couldn't achieve that. Additionally, the criteria is very important because yours will miscount ABRAJ1 as having 3 ServDate values where his criteria specifically states it should evaluate to 2.

 

[myblueshadow]

Hi again. Thanks so much for your help. I just got back to work this morning and plugged this in, but it's still not giving me the zeros.