Unable to group by a calculated time

[kroden]

I'm new to the forum and have limited Access experience.

I want to calculate the workload efficiency of staff in a blood center. Occassionally donors are registered before official office hours. I wrote an IIf expression to change the registration time (adding an hour) if the office is not open, otherwise display the registration time.

CorrectHr: IIf (IsNull([AvgStaffWorkHrs]),IIf([RegHour]>#11:00:00 AM#, DateAdd("h", -1,[RegHour]),DateAdd("h", +1, [RegHour])),[RegHour])

The expression returns the expected values. I then ran a second query to group by the "corrected" registration time so I could add the total number of registrations. Although the times appear the same, they are not being grouped together. What am I missing?

thanks

 

[speakers_86]

Why do the office hours matter? If the person registered at x time, why would you display they registered at y time?

If you remove the iif statement, does it work as expected? If so, then is it out of the question to actually change the time instead of just displaying this calculated time?

 

[student]

I, like speakers, may be missing your point, but how about you group by the original time & simply not display it in your results? Granted, that won't group those with a genuine time matching a 'fake' time, but would you want that? Not entirely sure.

 

[kroden]

Why do the office hours matter? If the person registered at x time, why would you display they registered at y time?

If you remove the iif statement, does it work as expected? If so, then is it out of the question to actually change the time instead of just displaying this calculated time?

Staff are taking donors a few minutes before office hours, but they are really processed the following hour - say they registered at 7:55 (they really are processed during the 8 o'clock hour.) The data is pulled from the registration system by the hour. The Director wants to look at how many are processed each hour and how many are registered per worked hour. I originally left the times "as is", but the Director wants to see the data based on the hours the doors are officially open. Hope this explains the why. I just don't understand why the calculated value appears correct, but will not group (& add) as expected.

thanks

 

[gemma-the-husky]

can you not use two queries

build the first WITH the calculated field. THen use this as a recordsource for a second query. That should work.

 

[kroden]

That is what I did. It did not work.

 

[student]

Back up a minute, can I just check it's not keeping records separate because of another field not being counted or summed?

 

[kroden]

Back up a minute, can I just check it's not keeping records separate because of another field not being counted or summed?

I ran the first query with the calculation. I used that query in a second query to group & sum. The group & sum query groups by Location, RegistrationType, Year, Month, DayOfWeek and Hour; it also sums the # of registrations. I worked backward and forward grouping the different fields. The Hour field does not recognize the corrected time (7:00 + 1) as being the same as the 8:00 even though they look the same. It will not group them together. My question is why are they different and is there something I can change so I can group them?

thanks