Date and Time grouping for Production Reports

[CoopEng]

Hey Team,

Here's one for you all to ponder. In Access 2003, I'm updating a Operations Database for a manufacturing plant. The data is of production runs for the plant. So there are start date's and start times for each run. For example on monday march 10 there could have been entries like 5:00 - 13:00 (..data from shift), 13:00 - 21:00 (data from shift).. etc.

The report that i'm working on displays all production runs between two dates. So all runs for the month of March for example. The report groups the entries by start date. So all runs for the 10th of march are in one group, and at the end of this group there is some vital summaries for the day. Then the next group is of the 11th and at the end of that, vital summaries for that day's runs.

So far pretty straight forward?

Because the plant is run in shifts, a single day (in their minds) runs from 5:00 am on the day of, to 5:00 am on the following day.

So here is my question. Is it possible to set the grouping of the report to group the runs in the manner that I want? Where under March 10th there would only be runs that started after 5:00 am on the 10th and before 5:00 am on the 11th? Right now the report would include a run that started March 10th at 1:00 am and went to 5:00 am, it would include this in the March 10th group. When really this run should be in the March 9 group.

I am aware that you can use expressions to group but would these parameters be too advanced and complex for access to handle? If not then where would I start? I tried to use some "IF" statements but to no avail.

Thanks for all your help in advance. Hopefully this little brain teaser won't prove too exorbitant.

[MindBender77]

I work where our midnight shift goes from 10pm on day 1 to 7am on day 2. You add criteria to the date and time range of your query like:

This example is based on my shift schedule but, it may help in your case.

 DateField                                     TimeField
Criteria: Date()-1        Between 22:00:00 and 23:59:59
Criteria: Date()                         Between 00:00:00 and 07:00:00
 

Bender

[CoopEng]

I work where our midnight shift goes from 10pm on day 1 to 7am on day 2. You add criteria to the date and time range of your query like:

This example is based on my shift schedule but, it may help in your case.

 DateField                                     TimeField
Criteria: Date()-1        Between 22:00:00 and 23:59:59
Criteria: Date()                         Between 00:00:00 and 07:00:00
 

Bender

The query that calls the data is calling it correctly. It draws from 3 different queries. One that has the 5:00 - 24:00 on the front end of the search date, one that gets all the middle dates, then another the gets the info from 00:01 - 4:59 on the last day. I have another report that does the same function as this report, however it only does one day. That report works fine (because there is no need to group).

In the report I'm working on now, I need to create a group that runs much like a query really. So each group will be created based on each runs StartTime and StartDate. It's making this expression and where to put it that is the baffler.


The grouping is done in the "Sorting and Grouping" Menu.
View > Sorting and Grouping.
Here there is a Field/Expression column that specifies the criteria for each group. What expression would I have to put in there to create the group that I want?

Thanks for the help though Bender