summing records where field is between

[sheederj]

I am trying to create a sum of a field called ec2mo where mopp is between 0 and 2 and then another sum when mopp is between 2 and 4, etc... I've tried a number of different things, but it doesn't seem to work - instead i get a single value for each field (i'm assuming because there are different values for mopp and many other fields)... I've attached the db with just that table.
Thanks very much for your help!
P.S. I'm not really at all good with using SQL so anything that can be done in a regular query would be most helpful!

 

[Brianwarnock]

Is this what you want?

Brian

 

[sheederj]

That's close

Thanks so much for your help... It's close to what I need, but i need it to do that for each patient (identified by both patient id and grv) for the 2 month blocks...
Also, is there a way to have all the other fields in the query or does that mess it up because, for the most part, all the fields are unique....

 

[Brianwarnock]

As we have an aggregate query all fields must fall into the pattern , they can all be selected and the Total field in the grid could have GroupBy, does that make sense? It would for PatientID and grv, and the date of Birth is constant so that' ok but if we look at patient 1000995 grv 1 then the bccurrent field has 2 values so it would group on those, and etc for the rest of the fields.

Have a play and see if it is what you want.

Brian