Actual vs. Scheduled

[Shanin]

I'm trying to figure out a good way to show actual timecards versus
scheduled. I have an Access 2000 database that is pulling info from a FoxPro
database to compile this. I currently have a query and report that pull
everyone's time sorts it by department and by day and totals it by day and
report total depending on the date range entered. It looks like this:

SELECT tktimcrd.employee, tktimcrd.job, tktimcrd.department,
tktimcrd.work_date, py_emplo.first_name, py_emplo.name, tktimcrd.time_in,
tktimcrd.time_out, tktimcrd.hours, tktimcrd.pay_type, pypaytyp.descriptn,
tktimcrd.comment
FROM (py_emplo INNER JOIN tktimcrd ON py_emplo.employee = tktimcrd.employee)
INNER JOIN pypaytyp ON tktimcrd.pay_type = pypaytyp.pay_type
WHERE (((tktimcrd.job) Not In ("10","30","35","81","25","82")) AND
((tktimcrd.department) Not In ("COR")) AND ((tktimcrd.work_date) Between
[Start date] And [End Date]) AND ((tktimcrd.pay_type) Not In
("S","P","Y","Z")))
ORDER BY tktimcrd.job, tktimcrd.work_date, tktimcrd.time_in
WITH OWNERACCESS OPTION;


What I really would like to do is on the report be able to just list the
schedule for that department on that day and I can't think of how to do it
since staff may not always work their scheduled shift, someone may fill in
for them. The scheduled info is in another table "tksche" and it has the
following fields: date (I didn't name it that, that is what it was named in
this FoxPro database), time_in, time_out, hours, and job. Is there anyway I
could just get it to list the schedule by day down one side of a report and
get it to pull the information of the actual worked hours down the other? I
know it's too complicated for my head to comprehend today.

Thanks
Shanin

 

[John W. Vinson]

What I really would like to do is on the report be able to just list the
schedule for that department on that day and I can't think of how to do it
since staff may not always work their scheduled shift, someone may fill in
for them. The scheduled info is in another table "tksche" and it has the
following fields: date (I didn't name it that, that is what it was named in
this FoxPro database), time_in, time_out, hours, and job. Is there anyway I
could just get it to list the schedule by day down one side of a report and
get it to pull the information of the actual worked hours down the other? I
know it's too complicated for my head to comprehend today.

Since the two tables are not directly related, I'd just use two side by side
Subreports.

John W. Vinson [MVP]

 

[Shanin]

When you say two subreports do you mean just make a blank report and insert
two subreports one pulling from the actual query and one pulling from the
schedule query? I've never really dealt with sub-reports, I've only put in
sub-forms.

Thanks

 

[John W. Vinson]

When you say two subreports do you mean just make a blank report and insert
two subreports one pulling from the actual query and one pulling from the
schedule query? I've never really dealt with sub-reports, I've only put in
sub-forms.

Yep. They work in very much the same way. In this case you would have an
unbound and basically empty main report (perhaps some labels in the header,
page numbers in the footer, etc. if you wish, but no bound controls); the two
subreports would be based on the two queries as you describe, with nothing in
their master/child link fields.

John W. Vinson [MVP]

 

[Shanin]

Well I got to work to an extent. Is there anyway I can get it so I don't
have to enter the date range twice?? The actual query uses the field
work_date and I have the criteria set to be Between [Start Date] AND [End
Date]. The scheduled query uses the fied date, which I renamed to also be
work_date and I have the same criteria. Of course when I run the report I
get it asking for Start Date and End Date twice. The other fun part is
trying to get it all to fit on one page, I've got to work on that some to see
what I can shrink since even at landscape I'm a little to big.

 

[John W. Vinson]

Well I got to work to an extent. Is there anyway I can get it so I don't
have to enter the date range twice?? The actual query uses the field
work_date and I have the criteria set to be Between [Start Date] AND [End
Date]. The scheduled query uses the fied date, which I renamed to also be
work_date and I have the same criteria.

You can (and should, I'd say) use a Form to solicit the criteria. Rather than
launching the report, launch a form, frmCrit, with textboxes txtFrom and
txtTo. Use

= CDate([Forms]![frmCrit]![txtFrom] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtTo])

as your criterion.

Of course when I run the report I
get it asking for Start Date and End Date twice. The other fun part is
trying to get it all to fit on one page, I've got to work on that some to see
what I can shrink since even at landscape I'm a little to big.

That I'll leave to you... of course you could have them arranged one above the
other instead of side by side, if that is useful for the user.

 

[Shanin]

You know sometimes it pays to sleep since that should have been so obvious.
Thanks. You don't have any tips for getting the days to line up which each
other do you? Currently in the side by side format I may have 4 days on the
scheduled and 3 on the actual. I guess I can try and find a group interval
and have it group on that where it may keep 3 days to a page. There isn't
some trick of linking them in a query based off the date and job is there? I
tried that earlier and I don't remember what it spit out, but it wasn't right.

Thanks again.

Well I got to work to an extent. Is there anyway I can get it so I don't
have to enter the date range twice?? The actual query uses the field
work_date and I have the criteria set to be Between [Start Date] AND [End
Date]. The scheduled query uses the fied date, which I renamed to also be
work_date and I have the same criteria.

You can (and should, I'd say) use a Form to solicit the criteria. Rather than
launching the report, launch a form, frmCrit, with textboxes txtFrom and
txtTo. Use

= CDate([Forms]![frmCrit]![txtFrom] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtTo])

as your criterion.

Of course when I run the report I
get it asking for Start Date and End Date twice. The other fun part is
trying to get it all to fit on one page, I've got to work on that some to see
what I can shrink since even at landscape I'm a little to big.

That I'll leave to you... of course you could have them arranged one above the
other instead of side by side, if that is useful for the user.

 

[John W. Vinson]

You know sometimes it pays to sleep since that should have been so obvious.
Thanks. You don't have any tips for getting the days to line up which each
other do you? Currently in the side by side format I may have 4 days on the
scheduled and 3 on the actual. I guess I can try and find a group interval
and have it group on that where it may keep 3 days to a page. There isn't
some trick of linking them in a query based off the date and job is there? I
tried that earlier and I don't remember what it spit out, but it wasn't right.

I'd repost in the Reports newsgroup - I know there are some clever tricks to
control print formatting but that's not my area of expertise.

Access, of course, knows nothing whatsoever about "jobs" - just data in
tables. I guess I'm not quite clear what you're asking about the query. If you
want four records to match up with three records, then there WILL be a
mismatch and I don't know what you would expect to see!

 

[Shanin]

I'll give that a try. What I'm referring to as "job", is just the
department. Each "job" has a set schedule. Most schedules are about 4
shifts, midnight to 8am, 8am to 2pm, 2pm to 10pm, and 10pm to midnight. When
I'm referring to getting them to line up I mean the scheduled side would have
only those four shift for that day, so it's going to be 4 lines long and then
go to the next day. The actual side may have 8 shifts due to people working
over their schedule and program splitting those out. So on the actual side I
may show 3 days worth of work, but since there are less shifts on the
scheduled side, 4 days may be showing there, therefore the don't line up so
you can easily compare the schedule to the actual. I can get it to break by
day, but that puts one day per sheet and that wastes a lot of paper.

thanks for you help