Re: Actual vs Scheduled report

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.

--
HTH,
George


"Shanin" <Shanin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8D2C39EF-EF5F-4919-9289-99ECC751D149@xxxxxxxxxxxxxxxx
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" wrote:

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.

--
HTH,
George


"Shanin" <Shanin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5C573F84-857B-4460-A757-673B38EEDEAC@xxxxxxxxxxxxxxxx
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?

"Larry Linson" wrote:

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" <Shanin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B8C5F3C4-636E-4257-A994-609FA5809A71@xxxxxxxxxxxxxxxx
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









.

Relevant Pages

  • Re: Actual vs Scheduled report
    ... Main Report: Based on a query with the unique Actual/Scheduled WorkDates ... Make sure you GroupBy WorkDate on this report. ... I'll give an example with the schedule being on the left and the actual ... shifts so you can easily see who worked extra, who didn't come in, etc. ...
    (microsoft.public.access.reports)
  • Re: Actual vs Scheduled report
    ... Main Report: Based on a query with the unique Actual/Scheduled WorkDates ... Make sure you GroupBy WorkDate on this report. ... What I would like the report to do is to have two columns, the schedule on ... shifts so you can easily see who worked extra, who didn't come in, etc. ...
    (microsoft.public.access.reports)
  • Re: Actual vs Scheduled report
    ... What I would like the report to do is to have two columns, the schedule on ... 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. ...
    (microsoft.public.access.reports)
  • Re: Actual vs Scheduled report
    ... Main Report: Based on a query with the unique Actual/Scheduled WorkDates ... Make sure you GroupBy WorkDate on this report. ... What I would like the report to do is to have two columns, the schedule on ... shifts so you can easily see who worked extra, who didn't come in, etc. ...
    (microsoft.public.access.reports)
  • RE: Meter bar for each record in access form/report
    ... I found an example of a shipping schedule ... Here is the code that comes from the report detail's On Format ... Dim lngStart As Long 'start time of job ... The control always shows the value for the first/top record ...
    (microsoft.public.access.formscoding)
Loading