Old Farmer needs help with a date query

[chuckdarwin]

I am learning as I go here but i just cant figure this one out

I have table listing Insect Broods. there are two fields I am working with and they are [Seed Date] and [POP Est]
I am strying to sum all records within 1 week date ranges to provide available populations

I created the following query

SELECT Sum(Broods.[POP Est]) AS [SumOfPOP Est], Broods.[Seed Date], Broods.[Brood ID], Broods.Species
FROM Broods
GROUP BY Broods.[Seed Date], Broods.[Brood ID], Broods.Species


HAVING (([Seed Date]>=DateAdd("d",-17,Date())) AND ((Broods.Species)='House Cricket'));

That is to give me a sum of all crickets from 17 days after seed
I intend to furthur filter this down to only sum the populations from 10 days to 17 days but baby steps here i cant get past this first one


Problem is instead of totaling the values contained in [POP Est] it is displaying all the records.

Can someone see what i am doing wrong

thanks in advance

[aytee111]

The records will show depending on your grouped fields - one of which is the date. This should be a non-display field. You also do not need to display the species as they will all be the same.

[Minty]

Try changing the Having to a WHERE clause.

Having will apply the criteria after the grouping is done, I think you need it before.

SELECT Sum(Broods.[POP Est]) AS [SumOfPOP Est]
FROM Broods
WHERE (([Seed Date]>=DateAdd("d",-17,Date())) AND ((Broods.Species)='House Cricket'))

[chuckdarwin]

That worked like a charm

Now just gotta figure out how to filter by max age

[chuckdarwin]

So i tried adding in other filter using the same format and now it generates zero results.

SELECT Sum(Broods.[POP Est]) AS [SumOfPOP Est]
FROM Broods
WHERE (((Broods.[Seed Date])>=DateAdd("d",-24,Date())) AND ((Broods.Species)='House Cricket') AND ((Broods.[Seed Date])<=DateAdd("d",-31,Date())));


Am i missing something here

I apologize i am learning as i go here

[aytee111]

"Sum" denotes totaling, you can't just type it in. Click on the totals icon at the top in query design, then select sum from the total line.

[chuckdarwin]

I dont understand I didnt change the Sum all i did was add the additional text saying

AND ((Broods.[Seed Date])<=DateAdd("d",-31,Date())));

What i am trying to do

Seed date is the date eggs are placed in the icubator
10 days later they hatch
I need to run querys showing me the Total crickets that are 1 week 2 week ect all the way to 5 weeks

I thought i was on the correct path am i wrong ?

[aytee111]

Date must be after 24 days ago and before 31 days ago! Ain't gonna happen. You can also use Between Date1 And Date2 for dates.

[chuckdarwin]

ROFL I noticed that after. Been busy fixing everything and it works perfectly thank you all for you help