Query by time while suming another column

[abodi]

Hi Everyone,



I've got what i hope to be a simple problem but my google-fu seems weak against it.

i've got a table that contains

Name | Date | Hours off

What i want to be able to do is do a seach within a date range and then total the hours off for each person.

i can do that date range easy and i can do the total when i'm not including date range.

TESTING_All Staff Total
Name SumOfHours off today
A.L 15.2
A.R 7.6
A.S 7.6
A.H 7.6

But i can't do them together, it does try to sum up the hours but will sum it for each person for a particular date, whereas i want to query by date, but i dont' want the data to be separated by day.

TESTING_All Staff Total
Name SumOfHours off TodayDate
A.L 7.6 20/09/2011
A.L 7.6 27/09/2011
A.R 7.6 26/09/2011
A.S 7.6 27/09/2011
A.H 7.6 26/09/2011



so essentially i want the data to show as per the first table shown, but i want to be able to enter the date range to show. that way i could total for day, week, month, or whatever date range you put in.

Thanks in advance, you guys are always so helpful.

[pbaldy]

It would have helped to see the SQL of the query, but it sounds like you have Group By on the date field, and it should be Where.

[abodi]

pbaldy you were absolutely right.

i have come across another issue though.


in the DATE field,

If its left with no conditions but set to "where" : It works

if i set the condition to "Between [s1] and [s2]" : It works (when run it asks for a date and it works)

if i set the condition to "Between [Forms]![View_Data]![StartDate4] And [Forms]![View_Data]![EndDate4]" : it DOESN'T work, just returns no data.

now the confusing part to me, is if i get rid of the summing, and just search via the date range as per my form, it works.


To clarify, the date range fields works and are well tested.
when i enter the date via a popup (because it's not linked to a form) it works.
it does not however work when both are combined. the query runs but returns no data.

any ideas?


heres a pic, i coudln't seems resize once embedded.

https://picasaweb.google.com/lh/phot...eat=directlink