Actual vs Scheduled report

[Shanin]

I'm trying to get a report that will match up scheduled shifts to actual
shifts. Basically grouping by department and totaling by day for each day in
the date range. I created a report with two unbound sub-reports, one pulling
from the schedule and one pulling from the actual, but due to the difference
in shifts, the actual may have 8 shifts worked compared to the scheduled only
having 4, they don't line up for easy comparison. I can page break by day,
but then that wastes a lot of paper. Is there any good way to do this that
is easier or more effective? Both tables the queries pull off of would have
the same dates and departments. I would like them to split the days in the
same location so you can compare the shifts easily to see what is extra or
less.

Thanks

 

[Larry Linson]

What data do you have, and how is it laid out in Tables, and exactly what is
it that you want to match up? When you talk of "shifts", if you mean "a
person working a shift," it would be useful information for someone trying
to assist. Please clarify and perhaps someone can make useful suggestions.

Larry Linson
Microsoft Office Access MVP

 

[Shanin]

The data is actually pulled from a FoxPro database that we purchased for
staff to clock in and out to so I can't arrange those tables in any way or
have a choice on field names. The tables from it are the following and have
the following fields:

Table 1: tktimcrd (this is the table with actual punch in and outs)
Fields: Employee, work_date, Job, time_in, time_out, hours

Table 2: empsched (this is the table with schedules)
Fields: Employee, date, Job, time_in, time_out, hours

Basically the table empshed is the perfect world scenerio where everyone
scheduled comes in on time and clocks out on time, that never happens though.
I'll give an example with the schedule being on the left and the actual
being on the right of say Job 30 for Monday 2/11/08.

Smith, 12:00am-6:00am Smith 12:00am-6:00am
Jones, 6:00am-2:00pm Smith 6:00am-6:20am
White, 2:00pm-10:00pm Williams 6:20am-2:00pm
Smith, 10:00pm - midnight White 2:00pm- 10:00pm
White 10:00pm
- 10:10pm
Smith 10:04pm
- midnight

What I would like the report to do is to have two columns, the schedule on
the left, and the actual on the right, like above, and break them by date,
with a day total and then show the next day. What I get since I have 2
unbound sub-reports, is that, but since the schedule has less shifts
(entries), it's day break is before the actual's day break so the farther you
go down the report, the days no longer line up. I may have 3 scheduled days
in the left column on page 1, but only 2 days of actual punches. I just want
each day to be side by side so you can compare the day totals and see the
shifts so you can easily see who worked extra, who didn't come in, etc. I
can break the report to show only one day per page, but I would like to be
able to show more per page as to not waste paper. Did that help any?

 

[George Nicholson]

One approach:

Main Report: Based on a query with the unique Actual/Scheduled WorkDates
involved. Make sure you GroupBy WorkDate on this report.

2 Subreports, one for Scheduled, one for Actual. Subreports located within a
WorkDate section (Header, Detail or Footer). Master/Child Link on WorkDate.
Each subreport instance will only show the hours for one WorkDate (based on
the Master/Child link), but you will have a set of subreports for each day
within the Main Report recordset and they will share a common Workdate
Header/Footer on the Main Report, so they will remain side-by-side.

 

[Shanin]

I think I understand the report construction now. My question would now be
how would I get a query to pull that work date? Since table tktimcrd has the
date field set as "work_date" and the table tkempschd has the date field set
as "date" and it's possible there could be a scheduled date that's not worked
or vise versa and regular select queries want some common link. The date
field is common, but there may not always be a match. Thanks

 

[George Nicholson]

My question would now be

how would I get a query to pull that work date?

The following should give you a list of all unique dates appearing in either
table.

SELECT Work_Date From tktimcrd
UNION SELECT Date From empsched
ORDER BY Work_Date

- Open query designer. View>SQLView. Copy, paste & save. (You can't use
Design view to build UNION queries.)
- UNION queries exclude duplicate records by default.)

Use this date list as the basis of your parent report, grouping on
Work_Date, and then link your subreports to it on their date fields.

 

[Shanin]

I got it to work...thanks a lot.

The following should give you a list of all unique dates appearing in either
table.

SELECT Work_Date From tktimcrd
UNION SELECT Date From empsched
ORDER BY Work_Date

- Open query designer. View>SQLView. Copy, paste & save. (You can't use
Design view to build UNION queries.)
- UNION queries exclude duplicate records by default.)

Use this date list as the basis of your parent report, grouping on
Work_Date, and then link your subreports to it on their date fields.