-
count for each month
I have 2 tables in Access 2000 : Members and Messages
I get all the members.Id for a category with a Procedure
List_Members_3 >>>
SELECT Members.Members_Id
FROM Members
WHERE Members.Cat = 3
then I want to get all the Messages of the Members for january 2004
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Month([DateMessages])
HAVING (((Year([DateMessages]))=2004) AND ((Month([DateMessages]))=1));
I get one row = Count
how can I get 12 rows for each month ?
Month([DateMessages])=(1 to 12)
-------------------------
and how can I avoid >>>
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
with sommething like >>>
SELECT Count(Messages.Id) AS CountOfId
FROM Messages Where Messages.Members_Id IN (SELECT Members.Id
FROM Members
WHERE Members.Cat = 3)
thank you
-
You are getting one row, because you have restricted result with month=1 in having clause. Remove that and you should get 12 rows.
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Month([DateMessages])
HAVING (((Year([DateMessages]))=2004));
-
ooops!
yes you are righ !!! :-(((
and have you any idea to return a 0 if the count is empty
if a few month are missing then I have not 12 rows
thanks again !!
-
Not very straight forward. Try this.
SELECT Year([DateMessages]), Months.Month,
Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
RIGHT JOIN (SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12) as months
ON Month([DateMessages]) = months.month
GROUP BY Year([DateMessages]), Months.Month
HAVING (((Year([DateMessages]))=2004));
-
This look terrible ! I couldn't imagine such a possibilty !
I get an error >>>
Operator absent in :
Messages.Id = List_Members_3.Id
RIGHT JOIN (SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
sele
-
Access needs parentheses for multiple table joins. Try this.
SELECT Year([DateMessages]), Months.Month,
Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN (List_Members_3
RIGHT JOIN (SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12) as months
ON Month([DateMessages]) = months.month)
ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Months.Month
HAVING (((Year([DateMessages]))=2004));
-
ho ! I get again an error >>>(expression de jointure non supportee) that's mean something like >>> Join expression non valid
it was great !! :-(( but access doesn't want
-
Looks like access does not like derived table. To workaround this you can create a query for
SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12
and use the query in RIGHT JOIN.
-
SELECT 1 as month from which table ?
sorry I am not used to so advanced SQL Queries and I don't know what are exactly thoses 1,2,3 as months .. from which table
to simplify , the tables >>>
Members :
Members_Id
Cat
-----
Messages :
Messages_id
Members_Id
DateMessages
----------
a procedure >>>
List_Members_3 :
SELECT Members.Members_Id
FROM Members
WHERE Members.Cat = 3
-------
then I need for YEAR(Messages.DateMessages)= 2004
each Month MONTH(Messages.DateMessages)= 1 to 12
:-)
-
I did not test my query against access, it works in SQL Server.
It seems access does not support SELECT without table name. In that case you have to build a table with 12 rows, one for each month. And join it with rest of the tables.
-
ok ! thanks a lot for your time !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|