Text summary form or report in Access 2000

[micksopko]

I'm building an employee database in Access 2000 and I'd like to have as part of it an employee review form or report that includes fields for ID, Name, Dept., etc., as well as a brief review and the date of the review. There will be many reviews of each employee over time. I'm trying to conceptualize way in Access to have a summary form or report that will document this information by date as it accumulates, so I can see a history of the reviews and search them when necessary. Can anyone advise me on a straightforward way to do this? TIA. MICK

 

[DougP]

OK, I would have 2 tables.<br>
Table 1 has Employee in formation i.e. Name, Hire date, and other things that don't change but refer to that employee.<br>
Table 2 is review Information i.e. Date of Review, Review memo field, Salary increases etc.<br>
Table 1 has one entry for each employee<br>
Table 2 has multiple entrys for each employee each time you add a review item, like annual raise etc.<br>
The relationship between the 2 tables in on EmployeeID.<br>
<br>
Table1 field layout<br>
EmployeeID<br>
FName<br>
LName<br>
SS#<br>
etc.<br>
<br>
Table2 field layout<br>
EmployeeID<br>
Review Date<br>
Review Memo<br>
Salary<br>
etc.<br>
<br>
Now to make a report, you can have several.<br>
Have a single page one that has the Employee Review's. The one you bring them into the office for and they sign it.<br>
Other reports can have multiple employees (like a tabular affair)<br>
Create a query that has a link between the 2 tables<br>
and base your report on that query. Of course you can have several different queries and several reports.<br>
The EmployeeID field can prompt for a specific EmployeeID number then it will show that employee and all of his review info. Here is the syntax for that [Enter Employee Number]<br>
Thats a start<br>
Good Luck<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

The tricky thing about this is only that so much employee info can change. As far as I know, everything except SS#. If this is a large company, even hire date can change since a person can leave and be rehired. In that case do you want to delete a person with all thier records or just note the termination date and have a rehire date if they come back? Another note you may want to capture the current status of several items on the Employee table (curr salary, curr title, curr supervisor etc.) and also put these same items in the Review table too (before & after salary, % and or $ increase, supervisor/reviewer, title etc.)

 

[micksopko]

Doug - Thanks for your quick reply. I created a table of reviews that can have multiple entries for each employee and whose primary key is the review itself in a memo field. I'm setting up a query based on the relationship between the employee and review tables as you suggested and basing a report on that. I want to put a command button on the employee form that can jump to the review report for the employee whose record I'm viewing in the form. I have to work out the details but I think I'm heading in the right direction. Thanks for your help.<br>
Elizabeth - thanks for your observations. The field in the employee table that won't change will be the auto ID number. And even when employees leave they can go into a department called "Gone" that'll keep them on ice and available.

 

[elizabeth]

I don't think you want to set up a memo field as a primary key. It's meant to be the primary way to index and reference a field. Why not throw in an Autonumber. You may want to relate another table to the review record at some time.

 

[micksopko]

Elizabeth – I keyed an auto ID number as primary for each review as you suggested. I can see how that’d be simpler and less variable, but also unique.<br>
<br>
Doug or Elizabeth – I’m not clear on how to get the Review summary for each employee to work right. I set up a query relating the Employee and Review tables, related by Employee ID. I then based a report on that query, “Review Summary,” with fields EmpID, Name, PrevDept, PrevSalary, Review…” The thing I’d like to work out would be having the review Summary accessible from the Employee form so that each Employee record could link to its unique summary of reviews. Whenever I try that now I get the reviews of all the employees not just the one I’m interested in. I tried putting in “[EnterEmployeeID]” in the data control source property, but that gave me all the records too. I’m missing something here. Do you have enough info to troubleshoot my problem? Thanks in advance.<br>
MICK <br>

 

[DougP]

Well to do that<br>
Create a main form /sub form<br>
The Employee could be at the top and the &quot;Review&quot; info at the bottom.<br>
both forms are linked on &quot;EmployeeID&quot;<br>
First create an Employee form (here's how)<br>
goto the Tables TAB<br>
Single click the Employee table<br>
Click the &quot;Insert&quot; Menu word at the top<br>
Then Click &quot;Autoform&quot; this will instantly make a form.<br>
go in design view of this new form.<br>
Drag the form so its either wider or longer (Lots of gray space)<br>
In the Toolbox click Subform/Subreport button<br>
Drag a large area on your form<br>
A wizard will pop up<br>
Click Next button<br>
In the Table/Queries find your Review table<br>
Add all of the fields<br>
Click Next button<br>
Your Link should be on EmployeeID, which should be the top one in the list.<br>
Click Next button<br>
then Finish button<br>
View your form and move to each employee, the review info will change as the employee changes.<br>
<br>
OK<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

On DougP's 4th line, I believe he meant to key:<br>
the *tables* are linked on &quot;EmployeeID&quot;<br>
This is critical. If the tables are linked correctly your wizards do everything for you.

 

[micksopko]

Doug and Elizabeth - thanks for your suggestions. This whole thing is coming together. I set up the forms based on the 2 tables, linked by Employee ID, and made the Reviews a subform of the Employees, with each set of reviews unique to the individual Employee. So far so good. The last refinement I'd like to make, if possible, would be to have, instead of a subform of numbered records, a Summary report of the individual reviews, still appearing uniquely with each employee. All reviews for an employee could be displayed at a glance. So far the best I can do is a control button on Employee form opening a Summary report of ALL employees reviews, not of just the one record I have open. Is there a way to do this? Many thanks for your continued help. MICK

 

[elizabeth]

Use the filter parameter on your DoCmd.OpenReport. Set the filter to the value of the EmployeeID control on the form (Forms!FormName!ControlName).

 

[micksopko]

That last bit of info is a dense kernel for me. Could you expand it a little? I don’t know what DoCmd.OpenReport refers to. What database object would I be working in to do this? I have tables for Employees, Reviews and Departments. Queries for Employees and Reviews. Forms for all of those and a summary report for Reviews. Thanks. MICK

 

[elizabeth]

Sorry this one slipped through the cracks In case you haven't already taken care of this, look at the code behind the OnClick event of the control button on your form that opens the report. You'll have to modify the code behind that button's OnClick event just a little bit. If you look up the OpenReport method of the DoCmd command, it will show you how to specify a filter when you open your report. Your filter will have to use the value that identifies your employee, on the Employee form, as a criteria, so that bit above was showing you how to refer to the field on your form that held that value.