Inclusion of date parameter messes up query

[betheball]

I have a query that tells me the number of reviews conducted by each team in my organization. It gives me the total for each team including a value of zero for teams that did none. This is what I want. Here is the query:

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

Now, my only problem is that it gives me a count of all the reviews in the DB. I want to get results on a weekly basis. So, I added to the end of the SQL this: WHERE AgeReview.ReviewDate Between Date() AND Date()-7

This query worked but did a couple of things to the results that I don't want it to do. First, it no longer returned zero values. Second, if a team did 2 reviews on one day of the week and 2 on another, it gave me two results for that team, instead of one. Meaning, it would give me one row that said Team 405 Count 2 and then a second row that said the same when what I want is simply one row that says Team 405 Count 4.

How can I add a date parameter without changing the results that are returned?

 

[RV]

As for your first question,

"it no longer returned zero values", I can't help you.

As for your second question (totalling per team per day instead of per team), check your GROUP BY.
I'm sure you'll see you included AgeReview.ReviewDate
Remove this column from your GROUP BY.

RV

 

[betheball]

RV, your suggestion resolved my second question. I probably should have known that.

Pat, I changed the order of my date criteria as you suggested, but my results remained the same. I really need my query to return a result for every team even if the result is zero. It puzzles me why the query works perfectly until I add the date criteria.

 

[betheball]

Let me see if I can make this a little clearer with a better example. I took the Orders query from the Northwind DB and added a date parameter. It now returns only the orders made in July of 1997. However, what I would like to see is a list of customer who DID NOT place an order in July of 1997. How would I write that query?

 

[simongallop]

have a list of the teams and use that to force a result.

Easy way. Create a second query linking the list of teams to the results of the current query. Select the link to show all reocrds of teams and those that match in the previous query.