report with 3 sections need a query???

[Guest]

I have an employee database set up to track new hires. its main purpose is to
get the new hires into the payroll and security systems. I fill out a form
with name address, dept, job,social Insurance Number and wage etc... I click
the print button which produces a report with this information. This report
is in 3 sections, the new hire section takes up the top third of the page. I
want to use the middle section as a "employee status change" section which
will let me change the employees dept, job or wage. The bottom third will be
for terminating staff, which will show why the employee was terminated, if
they have returned uniform, parking pass etc...

currently the middle and bottom section of the report is non functional as
they are only labels filling in the space. do I need more tables for this
section functional or just update queries. When the middle or bottom section
is populated I also want the top section to populate with the employees
current information. Also when the terminate section is used I will want to
make the employee inactive (remove from the employee table) and archive the
employee on a backup table.

I could use a separate report for each section, but would prefer to use one
report which shows everything.

Basically the table structure is 3 tables
tbl_employees
employeeId
departmentID
Job ID
name
adress
etc..

tbl_department
departmentID
department
dept budget code

tbl_job
jodID
job
job budget code

to summarize, I want to update empoyees current status, and
terminate/archive old employees.

thanks for any help

 

[Allen Browne]

Dan, I'm not sure I'm 100% clear on this, but here's some ideas to consider.

Firstly, you can create a query that uses all 3 tables. The query can then
supply all the fields you need from the 3 tables, and so the report has all
the fields you need.

Once you have the fields in the report, you may need 3 pages for each
record? You can stretch the Detail section to around 2 pages, but not 3. If
that's a problem, the solution is to open the Sorting And Grouping box (View
menu), and choose EmployeeID. In the lower pane of the dialog, set Group
Header to Yes, and Group Footer to Yes. Access adds to more sections to the
report: EmployeeID Group Header above the Details Section, and EmployeeID
Group Footer below. You can now stretch the height of these sections so each
one prints a page, and you have your 3 pages.

Regarding archiving employees, you would probably be better of retaining
them in the Employee table, and adding a Yes/No field to indicate Inactive
employees. It's dead-easy to filter out the inactive one (very simple
query), but it prevents all the issues re related records.

If you really want to move them to a different table, here's how to do it
safely:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[Guest]

I do have a yes/no field for active/inactive employees, which is fine. my
issue is, that when I make an emplyee inactive I need to print out a report
to send to our payroll department to let them know the employee no longer
works for the company. this report/form i send them has most of the fields
from the empl;yee tbl, but I require other fields like last day worked,
reason for leaving. should I add these fields to the main emplyee tbl or
create a new table or query for termination reasons? I currently use a 1
page form which we manually fill out and submit. this 1 page form contains 3
sections, new hire, change in status, and a termination section. Ideally I
would like to keep the same format with my database report which will auto
populate the required fields, to eliminate the manual process

 

[Allen Browne]

Yes: a couple of extra fields to indicate the LastDay sounds appropriate
(unless you have a pay table from which you could determine this), and
TerminationReason.