Date and Sum Query Problem

[rbeatty]

I have query that I cannot just seem to get right. I have a Date, Investigator, and Number field in a table named First. I need a query that will show me the sum of my number field over one months time. And the part I am having problems with is I need it to tell me this for a specific investagator. Any help would be great if possible.

Thanks
Roger

 

[mwolf00]

SELECT investigator, sum(number) FROM first WHERE date >= #11/1/2002# AND date <= #11/30/2002# GROUP BY investigator

 

[rbeatty]

I tried the query that you posted and it came up with an error saying that you have a subquery that can return more than one field without using the EXISTS reserved word in the main querys FROM clause. It says revise the SELECT statement of the subquery to request only one field.

 

[mwolf00]

Try these:

SELECT investigator, sum(number) FROM first WHERE date BETWEEN #11/1/2002# AND #11/30/2002# GROUP BY investigator


OR

SELECT investigator, sum(number) FROM first WHERE EXISTS date >= #11/1/2002# AND date <= #11/30/2002# GROUP BY investigator

OR

SELECT investigator, sum(number) FROM first HAVING date >= #11/1/2002# AND date <= #11/30/2002# GROUP BY investigator


Sorry for the choices, but I don't work with Access too often....

 

[cmmrfrds]

There is nothing wrong with this query except the use of some reserved words like - date.
SELECT investigator, sum(number) FROM first WHERE date >= #11/1/2002# AND date <= #11/30/2002# GROUP BY investigator

If you are combing this query with another query then show the full query.

 

[mazmouzi]

hi there,i've done everything now but i'm facing another problem like i wanna use the curent date my function is:
dateadd(&quot;m&quot;,-1,02-dec-02)
what i want now like i wanna put an variable date
when i'll go to use it ,it should get the current date