Counting number of variables in a field?

[cvili]

Hello,
Im asking for help; I have two tables (Mkz and Prihodi) with a query on
Mkz.id. I take a date and mkz from MKZ table and I want to count
Prihodi table in an interval from MKZ.date to MKZ.date-14 days:

*************
SELECT G.id, G.mkz, G.[date], count(P.mkz)
FROM Mkz AS G, Prihodi AS P
WHERE G.mkz=P.mkz And P.[date]>=G.[date]-14 And P.[date]<=G.[date]
GROUP BY G.id, G.mkz, G.[date];

***********

Now a problem: how can I get returns, where count(P.mkz) is NULL???

THANKS A LOT!!!

 

[Guest]

First of all, although it isn't essential to do so, take advantage of JOINs
to clean up some of the syntax:

SELECT G.id, G.mkz, G.[date], count(P.mkz)
FROM Mkz AS G INNER JOIN Prihodi AS P
ON G.mkz = P.mkz
WHERE P.[date]>=G.[date]-14 And P.[date]<=G.[date]
GROUP BY G.id, G.mkz, G.[date];

Secondly, don't use date as the name of a field! Date is the name of a
function in VB, and sooner or later, you're going to get caught by this.
There is also the basic issue that 'date' is not very descriptive; what date?
What is this the date of?

Finally, I don't believe count() can return NULL. It will return 0 if there
are no records to count. So at very least, your return will be a count of 0.

Good Luck!

 

[Van T. Dinh]

I don't think Count() will return Null. In my experience, Count will return
0 in this case.

 

[cvili]

Dear,

thank you very much! Im sorry for incomprehensibility: "Finally, I
don't believe count() can return NULL. It will return 0 if there
are no records to count. So at very least, your return will be a count
of 0. "

THATS THE CASE, I WANNA HAVE!

I took the both cases: with inner join and primary with the old one and
I got the same results: rows without 0, where arent any hits....

I don't think Count() will return Null. In my experience, Count will return
0 in this case.

--
HTH
Van T. Dinh
MVP (Access)

Hello,
Im asking for help; I have two tables (Mkz and Prihodi) with a query on
Mkz.id. I take a date and mkz from MKZ table and I want to count
Prihodi table in an interval from MKZ.date to MKZ.date-14 days:

*************
SELECT G.id, G.mkz, G.[date], count(P.mkz)
FROM Mkz AS G, Prihodi AS P
WHERE G.mkz=P.mkz And P.[date]>=G.[date]-14 And P.[date]<=G.[date]
GROUP BY G.id, G.mkz, G.[date];

***********

Now a problem: how can I get returns, where count(P.mkz) is NULL???

THANKS A LOT!!!

 

[cvili]

Oh, date - I wrote date, because I use otherwise a syntax from my
language (datum-ura - date-hour, like 15.3.2005 8:26:58) - case is more
understandable...