Display records by half hour increments 2

[jkupov]

I want to display records by half hour increments. So, I want something like this

Half Hour Count Met Goal Service Level
08:00 10 8 80%
08:30 18 12 67%
09:00 23 16 70%
09:30 19 14 74%
etc.


The record time is stored as a Date/Time field

I know I can identify the hour by using HOUR() and the half hour by using MINUTE()<30 & MINUTE()>30....

but from there how do I get them to display in a row which accounts for both the hour and half hour?

Thanks.

 

[lbass]

Is there data for every interval? If not, do you care if there are missing intervals?

-LB

 

[jkupov]

Good question which I left out. I don't care if there are missing intervals. Whether or not there is data for each interval will depend on the date range that I select. If I'm running the report for just one day or even a few days I probably will have some missing intervals during the wee hours of the morning. However, these typically fill in throughout the month and a monthly report almost always will have no missing intervals.

 

[lbass]

Create a formula like this:

if minute({table.datetime}) < 30 then
datetime(date({table.datetime}),time(hour({table.datetime}),0,0) else
datetime(date({table.datetime}),time(hour({table.datetime}),30,0)

This would allow you to group first on date and then on this formula to get the intervals within a specific day. If you mean to summarize across days within intervals, you could replace the 'date({table.datetime})' in the above formula with currentdate.

-LB

 

[jkupov]

I think this is working, but how would I get Group 1 to show a time rather than a date. I dont' care so much about grouping by date because my date range is in the select expert. I just want to show the 48 half hour increments and how many jobs we are doing during each interval to determine jobs per half hour and then adjust scheduling.

Right now, the Group 1 shows a date and I just want to display it like the first column in this example:

Half Hour Count Met Goal Service Level
08:00 10 8 80%
08:30 18 12 67%
09:00 23 16 70%
09:30 19 14 74%
etc.

Thanks LB!

JKupov

 

[fisheromacse]

sorry to jump into a post at the end, but...
it sounds like you could remove the date group.
then you would have 1 group remaining, 1/2 increments.

 

[jkupov]

Thanks fisherromance,

I did that prior to my last reply and that is when I realized that I was getting dates. When I format the field as H:MM PM, it displays the group headers like: January 3, 2010.

It's pretty clear that I'm missing something. Just not sure what. If I could do this in access I would, but I can't figure it out in Access either.

 

[fisheromacse]

In thinking and looking at lbass' replies, try the following modificaton...it removes the date and should just leave times.



if minute({table.datetime}) < 30 then
time(hour({table.datetime})),0,0) else
time(hour({table.datetime})),30,0)

 

[jkupov]

Ah! Perfect. Thanks to both of you. It makes sense once you said it, but I'm not experienced enough to have spotted it on my own. That's what happens when they cut budgets and put a project manager in charge of reports

Thanks LB and fisherromacse!

 

[fisheromacse]

HA! i can understand your situation.

I am a trained and experienced case manager/activity director for DD adults and have had the unique and at times exasperating experience of becoming our agency's IT staff person. Approx 315 users, many of which have literally never used a mouse before...and i get to do it all, basic staff training, SQL database mgmt, report creation, software & hardware installations, troubleshooting sotware & hardware, programming DVD/VCR clocks, running cabling, etc. If it has remotely to do with technology i usually end up with it.

I will say that i love learning and get the opportunity to learn something frequently.

Isn't it great to be stretched!!

 

[jkupov]

Sounds like we're in a similar boat- only you've learned much quicker! I actually enjoy it. Thanks again for your help.

 

[fisheromacse]

i enjoy it too, even if it feels like i have no brain cells left.

what was once a barely-scratch-the-surface hobby has become a job.

but at heart i am a mac guy working at a windows shop, so at least i still have fun at home.