Another need zero values question

[betheball]

I am using the following query to show me the number of reviews done by each team during a certain time period. It works great except for one thing. I would like it to return all teams including those that had zero reviews. As is it only returns a value if the team had at least one review. Any thoughts?

SELECT AgeReview.TeamIDRS, Count(AgeReview.TeamIDRS) AS CountOfTeamIDRS
FROM AgeReview INNER JOIN Team_Info ON AgeReview.TeamIDRS = Team_Info.TeamIDRS
WHERE (((AgeReview.ReviewDate) Between [Type the beginning date:] And [Type the ending date:]))
GROUP BY AgeReview.TeamIDRS;

 

[Dugantrain]

Have you tried an outer join?

 

[betheball]

I can't say I have. I am a relative newbie at this stuff. I am somewhat familiar with inner, left and right joins, but not sure what an outer join is or how to do it.

 

[betheball]

It is the date parameter part that gives me trouble. For example, this query returns total reviews for each team, including zeroes:

SELECT Team_Info.TeamIDRS, Count(AgeReview.TeamIDRS) AS CountOfTeamIDRS
FROM AgeReview RIGHT JOIN Team_Info ON AgeReview.TeamIDRS = Team_Info.TeamIDRS
GROUP BY Team_Info.TeamIDRS;

But, if I add the date parameter as shown in my first post, I no longer get the zero values.

 

[simongallop]

Looking at the the 2 queries the bit that you changed in the second one is correct ie you are selecting TeamIDRS from Team_Info, whereas in the first one you used AgeReview.

Therefore would have thought that this would work.

SELECT Team_Info.TeamIDRS, Count(AgeReview.TeamIDRS) AS CountOfTeamIDRS
FROM AgeReview RIGHT JOIN Team_Info ON AgeReview.TeamIDRS = Team_Info.TeamIDRS
WHERE (((AgeReview.ReviewDate) Between [Type the beginning date:] And [Type the ending date:]))
GROUP BY AgeReview.TeamIDRS;

 

[betheball]

Greetings Harry,

I tried the query in your post and I get the following error:

You tried to execute a query that does not include the specified expression 'TeamIDRS' as part of an aggregate function.

Sadly, I am such a rookie that I don't even know what an aggregate function is. If you can provide further help, please do.

Thanks

Duane

 

[simongallop]

Try this: You will need 2 queries. The first selects the records, the second gives the result

In the following code I am calling the first query Selected_Records

SQL for query Selected_Records:

SELECT AgeReview.TeamIDRS
FROM AgeReview
WHERE AgeReview.ReviewDate Between [Please type beginning date] AND [Please type end date];

SQL for second query:

SELECT Team_Info.TeamIDRS, Count([Selected_Records].TeamIDRS) AS TotalRecords
FROM Team_Info LEFT JOIN [Selected_Records] ON Team_Info.TeamIDRS=Selected_Records.TeamIDRS
GROUP BY Team_Info.TeamIDRS;

HTH

 

[betheball]

That's it! Thank you so much. Hopefully someday I will understand it, but for now I am just elated to have it working.

 

[betheball]

Is it possible to combine these two queries into one???

 

[simongallop]

I think not, though someone might prove me wrong. The first attempt that I did was the combining of the 2 queries but as you said it creates an error