Group report by Business Day

[Jean]

I have an Access 2000 report that I am trying to group by
Business Day rather than calendar day.

The Business Day is user defined in a table. The work
Shifts for each Business Day are also defined in the same
table.

Business Day Start End Begin DOW End DOW
"Sun" 1:00 AM 10:00 AM "Sun" "Sun"
"Sun" 10:00 AM 11:00 AM "Sun" "Sun"
"Sun" 11:00 AM 2:00 PM "Sun" "Sun"
"Sun" 2:00 PM 10:30 PM "Sun" "Sun"
"Sun" 10:30 PM 1:00 AM "Sun" "Mon"

My data contains a date and time field for each record. I
join each record with the appropriate Business/Shift
record. I want the report to sort by the record's
Date/time field but group on the work Shifts and the
Business Day.

I currently have the Sorting and Grouping set as follows:
1. Recordtime - group on Day (no group header or footer)
2. Business Day - group on Each value (with group footer)
3. Work Shift - group on Each value (with group footer)

This works fine until a record has a timestamp after
midnight. Then the report displays the records between
midnight and 1:00 AM as a separate group.

report example:
1/11/2004 11:45 PM BDay = "Sun"
Groups on Work Shift 10:30 PM - 1:00 AM and 1/11/04

1/12/2004 12:01 AM BDay = "Sun"
Groups on Work Shift 10:30 PM - 1:00 AM and 1/12/04

Should be:
1/11/2004 11:45 PM BDay = "Sun"
1/12/2004 12:01 AM BDay = "Sun"
Group on Work Shift 10:30 PM - 1:00 AM and 1/11/04 (as the
business day)

How do I make it group on the Business Day but keep the
records sorted by the record timestamp?

Any help is greatly appreciated.

Jean

 

[Baisong Wei[MSFT]]

Hi Jean,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

From the information you provided, it seems that when a working time span a
day, it cannot be report in a group, right?

I think to add a column in you table would bring convenience to your
design, the 'ShiftID', so that your table would be:

ShiftID Business Day Start End Begin DOW End DOW
1 "Sun" 1:00 AM 10:00 AM "Sun"
"Sun"
2 "Sun" 10:00 AM 11:00 AM "Sun"
"Sun"
3 "Sun" 11:00 AM 2:00 PM "Sun"
"Sun"
4 "Sun" 2:00 PM 10:30 PM "Sun"
"Sun"
5 "Sun" 10:30 PM 1:00 AM "Sun"
"Mon"

Accordingly, every record could have a 'ShiftID'. The following record's
ShiftID is 5:
1/11/2004 11:45 PM 5 BDay = "Sun"

You could group the record by ShiftID instead of any specific time period,
, it would be convinient for you to change the working shift time later.

Hope this helpful to your question. If you still have questions, please
feel free to post new message here and I am ready to help

Best regards

Baisong Wei
Microsoft Online Support

 

[Guest]

Your suggestion makes sense but I don't think it will work for my current situation. I already have similar fields setup in the table that I didn't previously mention.

Each record in the table has a ShiftID field and a unique ID field (autogenerated). However, the ShiftID field is not unique. Each Business Day has ShiftIDs that begin with 1 and go to however many shifts they define for that Business Day

Since my initial posting, I have successfully accomplished my goal but I'm not sure my way is the most efficient means of getting there. I'm still open to suggestions

My goal is to have the report display each record sorted by the record date/time. Then group on each Shift within the Business Day (in Shift order) and group again on the Business Day. In the Business Day group footer, the report needs to display the actual date that the Business Day converts to

I have accomplished this by designing various nested queries that match each record to the appropriate Business Day Shift record. Then a calculated query field generates a "Business Date" field for the record by comparing the record's Day-of-Week (DOW) to the Shift record's beginning DOW and ending DOW.

For example
1/11/2004 11:45 PM DOW = "Sun" BDay = "Sun" BDate = 1/11/200
1/12/2004 12:01 AM DOW = "Mon" BDay = "Sun" BDate = 1/11/200

This extra field allows me to group the report on the "Business Date" field

 

[Steve]

Could you send your DLL of the record
I suppose there must be a date/time column,suppose its
name is 'ShiftTime', with content of '1/11/2004 11:45 PM'
and '1/12/2004 12:01 AM'
You could have the bussiness day 'Sun' from the shift
table you have, Then you could group by 'Sun', then In
each business day, you could group by the shiftID. For
each shift record, you could just sort by the you could
add a sort of the 'ShiftTime'. Then you will have a report
group by business day, then group by shiftID, in each
shift id, each shiftID will be sorted by the date-time.
you could do it just by right-click the report design and
choose 'sorting and grouping'.

Well ,you could just post some sample data of how you
create your report from you shift table and the shift
records

Steve

-----Original Message-----
Your suggestion makes sense but I don't think it will

work for my current situation. I already have similar
fields setup in the table that I didn't previously
mention.

Each record in the table has a ShiftID field and a unique

ID field (autogenerated). However, the ShiftID field is
not unique. Each Business Day has ShiftIDs that begin with
1 and go to however many shifts they define for that
Business Day.

Since my initial posting, I have successfully

accomplished my goal but I'm not sure my way is the most
efficient means of getting there. I'm still open to
suggestions.

My goal is to have the report display each record sorted

by the record date/time. Then group on each Shift within
the Business Day (in Shift order) and group again on the
Business Day. In the Business Day group footer, the report
needs to display the actual date that the Business Day
converts to.

I have accomplished this by designing various nested

queries that match each record to the appropriate Business
Day Shift record. Then a calculated query field generates
a "Business Date" field for the record by comparing the
record's Day-of-Week (DOW) to the Shift record's beginning
DOW and ending DOW.

 

[Baisong Wei]

Hi Jean,

Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with your issue.

since you have a table 'Shift' in you initial post and a
table 'ShiftTime' (for each shift record) and you could
have a query like

SELECT Shift.BusinessDay,Shift.BeginDow,Shift.EndDow,
shiftTime.shiftTime, Shift.ShiftID
FROM Shift INNER JOIN shifttime ON
Shift.shiftID=shifttime.shiftid;

You could use a Report Wizard to have an report of this
query, and will group by BusinessDay and then group by
ShiftID. You then could use add a sort of the ShiftTime.
Then in the design windows of the report, you add a TEXT
Controler in the detail for the BDate, the Control Source
is '=Format(DateAdd("h",-1,[shiftTime]),"yy-mm-dd")'
For both of the records as follows, you will get the same
BDate:
1/11/2004 11:45 PM DOW = "Sun" BDay = "Sun" BDate =
1/11/2004
1/12/2004 12:01 AM DOW = "Mon" BDay = "Sun" BDate =
1/11/2004

Hope this helps. If you still have questions, please feel
free to post new message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support

 

[Guest]

I'm a little confused. Maybe I didn't explain myself very well in my initial posting

I have a table that contains the following information (we'll call it the Shift table)

RcrdID BDay Start End BeginDOW End DOW ShiftI
1 "Sun" 10:00 AM 11:00 AM 1 1
2 "Sun" 11:00 PM 1:00 AM 1 2
3 "Mon" 2:00 PM 5:00 PM 2 2
4 "Mon" 5:00 PM 8:00 PM 2 2

With additional entries for every business day and every shift within each business day

Then I have another table that contains the following records (we'll call it the Data table)

RcrID Time1 Time2 Time3 Time
1 1/11/04 10:15:30 1/11/04 10:20:45 1/11/04 10:16:15 1/11/04 10:17:0
2 1/11/04 23:01:45 1/11/04 23:07:30 1/11/04 23:02:14 1/11/04 23:03:0
3 1/12/04 00:25:20 1/12/04 00:28:45 1/12/04 00:26:10 1/12/04 00:27:0

The report displays the Data table records. I want the report to sort by Time1 of the Data table but group by Business Day of the Shift table. Then within the Business Day, I also want the report to group by Shift from the Shift table

 

[Baisong Wei[MSFT]]

Hi Jean,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

In my past post, I just want to add an ShiftID int the table 'DateS', and
this will be helpful for you for grouping. For the sample data you provide,
you could add

1) Since the time span in the table will not have the Date information but
only Time information, you should split the time span into two part if it
span 2 days, but with same ShiftID (Show as follows). Then create a
function to look for the ShiftID in the table 'Shift' by the time span.
When using the following functions, This will not affect the final report.
RcrdID BDay Start End BeginDOW
End DOW ShiftID
1 "Sun" 10:00 AM 11:00 AM 1
1 1
2 "Sun" 11:00 PM 1:00 AM 1
2 2
3 "Mon" 2:00 PM 5:00 PM 2
2 1
4 "Mon" 5:00 PM 8:00 PM 2
2 2

Should be splitted as:
RcrdID BDay Start End BeginDOW
End DOW ShiftID
1 "Sun" 10:00 AM 11:00 AM 1
1 1
2 "Sun" 11:00 PM 12:00 AM 1
2 2
3 "Sun" 12:00 AM 1:00 AM 1
2 2
4 "Mon" 2:00 PM 5:00 PM 2
2 1
5 "Mon" 5:00 PM 8:00 PM 2
2 2

Function LookupShiftID(InputTime As Date, BusinessDay As String) As Integer
Dim dtTime As Date
LookupShiftID = -1
dtTime = CDate(Format(InputTime, "Short Time"))
Dim criteria As String
criteria = "[Start]<=#" & dtTime & "# and [End]>#" & dtTime & "#" & " and
[BusinessDay]='" & BusinessDay & "'"
If Not IsNull(DLookup("ShiftID", "[Shift]", criteria)) Then LookupShiftID =
DLookup("ShiftID", "[Shift]", criteria)
End Function

Note: For any record whose time is not in the time span in table 'Shift',
the function will return '-1' as the ShiftID.

2) Create a query 'Query6' which will get the BussinessDay of each record.
Note that I reduced one hour for the time, that means, if the record's
time1 is within the first hour of Monday, the business day will still be
Sunday:

SELECT Date1.Time1,
Left(WeekdayName(Weekday(DateAdd("h",-1,Date1.Time1))),3) AS BusinessDay
FROM Date1;

3) Create a query 'Query7' for the report:
SELECT Query6.BusinessDay, LookupShiftID([Time1],[BusinessDay]) AS Expr,
Query6.Time1 FROM Query6;
You could use the Report Wizard, and the report will be grouped by
Query6.BusinessDay, then ShiftID which will be returned by function
LookupShiftID. Then all the record will be sorted by the record time.

Then you will get the report group by Business day first, then shiftID, and
all the record in the group will be sorted by record time (Time1).

Hope this is is what you want, if you still have questions, please feel
free to post new message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support