Problem with counting repeating numbers

[evilone]

Hello,

First of all, I have two tables in ACCESS database.

Results
int ResultID auto_inc
int ResultWeek
int ResultYear
DateTime ResultDate

ResultNumbers
int NumberID auto_inc
int ResultID
int ResultNumber
bool IsBasic

Now I want to select last 40 results from ResultNumbers and count the repeating numbers.

I made a query lik this:

SELECT TOP 40 Results.ResultDate, ResultNumbers.ResultNumber, Count(ResultNumbers.ResultNumber) AS CountOfResultNumber
FROM ResultNumbers INNER JOIN Results ON ResultNumbers.ResultID = Results.ResultID
GROUP BY Results.ResultDate, ResultNumbers.ResultNumber
ORDER BY Results.ResultDate DESC;

But all I get is

ResultDate	ResultNumber	CountOfResultNumber
-------------------------------------------------------------
2.04.2008	       8	                       1
2.04.2008	      27	                       1
2.04.2008	      29	                       1
2.04.2008	      30	                       1
2.04.2008	      38	                       1
2.04.2008	      41	                       1
2.04.2008	      43	                       1
2.04.2008	      44	                       1
26.03.2008	       6	                       1
26.03.2008	      13	                       1
26.03.2008	      19	                       1
26.03.2008	      33	                       1
26.03.2008	      38	                       1
26.03.2008	      40	                       1
26.03.2008	      41	                       1
26.03.2008	      44	                       1
19.03.2008	      2	                       1
19.03.2008	      15	                       1
19.03.2008	      16	                       1
19.03.2008	      25	                       1
19.03.2008	      26	                       1
19.03.2008	      37	                       1
19.03.2008	      40	                       1
19.03.2008	      48	                       1
12.03.2008	      7	                       1
12.03.2008	      9	                       1
12.03.2008	      15	                       1
12.03.2008	      22	                       1
12.03.2008	      27	                       1
12.03.2008	      33	                       1
12.03.2008	      34	                       1
12.03.2008	      40	                       1
5.03.2008	      4	                       1
5.03.2008	      5	                       1
5.03.2008	      6	                       1
5.03.2008	      14	                       1
5.03.2008	      18	                       1
5.03.2008	      29	                       1
5.03.2008	      42	                       1
5.03.2008	      46	                       1

Number 6 is here 2 times, but why this query returns all counts in different rows?

I want that result will be like:

ResultNumber	CountOfResultNumber
-----------------------------------------------------------
                8                             1
               27                            2
               29                             2
               30                             1
               38                             2
               41                             2
               43                             1
               44                             2
	   6	                     2
               13                             1
               19                             1
               33                             2

I know that there's a problem with date, but I'm not quite home with databases. Please can someone give me hand?

[r937]

Now I want to select last 40 results from ResultNumbers and count the repeating numbers.

last 40 results:

SELECT TOP 40 
       Results.ResultDate
     , ResultNumbers.ResultNumber
  FROM Results  
INNER 
  JOIN ResultNumbers
    ON ResultNumbers.ResultID = Results.ResultID
ORDER 
    BY Results.ResultDate DESC

count repeating numbers in last 40 results:

SELECT ResultNumber
     , COUNT(ResultNumber) AS CountOfResultNumber
  FROM ( SELECT TOP 40 
                Results.ResultDate
              , ResultNumbers.ResultNumber
           FROM Results  
         INNER 
           JOIN ResultNumbers
             ON ResultNumbers.ResultID = Results.ResultID
         ORDER 
             BY Results.ResultDate DESC ) AS last40
GROUP 
    BY ResultNumber

[evilone]

this works great, I learn so much from you r937, thank you...