I created an Employee Profile in FORMS of all of the employees in the company. I used a spreadsheet that was given to me and created a table (Master Employee List) and thus took that table and created a FORM. Included in the employee profile is current salary information. I was given another spreadsheet with salary and education history of the employees as my boss wants me to include the salary history in the FORM already created. What is the best way to do this? Should I make the spreadsheet given to me with the salary history into a table? If so, could I match the the employees id number from both tables or would it be better to take the spreadsheet with the salary history make it into a table then craete a query?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Employee profile in FORMS 3
- Thread starter vince1209
- Start date
-
1
- #2
Make a Salary History table, relate it to Master Employee by Employee ID (this will be a one-to-many relationship, the Salary History being on the Many side).
You can than make a Salary History subform which uses table of the same name as it's datasource. Put the subform inside the Master Employee form, link the main form to the subform via the Employee ID field.
You can than make a Salary History subform which uses table of the same name as it's datasource. Put the subform inside the Master Employee form, link the main form to the subform via the Employee ID field.
-
1
- #3
The current salary and salary history should be in one table. Maybe have start and end date fields to show time of each salary.
Also, there should be a separate table for education.
Main employee table should have only the basic info on each employee: Firstname, lastname, Address, City, State, zip, Phone, etc.
May want to read:
Fundamentals of Relational Database Design
Also, there should be a separate table for education.
Main employee table should have only the basic info on each employee: Firstname, lastname, Address, City, State, zip, Phone, etc.
May want to read:
Fundamentals of Relational Database Design
- Thread starter
- #4
fneily thanks for the suggestion. I was able to build separate tables for the education and salary history. I have another question. On the form I have a section where I have the current salary of an employee and underneath it I want to have the salary history of that employee. i created a subform based off a query I built using the master Employee table and the newly built salary table. I joined both by the empl ID#. I have the desired results in the query but when I used the query in a converted subform I am not seeing the different salary of the employee. Any sugggestions?
For Example: I have this employee salary history. How to show this same thing in the subform?
John Doe $41.71 $86,758.24
John Doe $41.71 $86,758.24
John Doe $41.40 $86,112.40
John Doe $41.40 $86,112.40
John Doe $39.90 $82,999.90
John Doe $39.90 $82,999.90
John Doe $36.01 $74,899.23
John Doe $36.01 $74,899.23
John Doe $34.29 $71,332.60
John Doe $34.29 $71,332.60
John Doe $34.29 $71,332.60
John Doe $32.53 $67,653.40
John Doe $31.16 $64,822.60
John Doe $31.16 $64,822.60
John Doe $29.40 $61,153.40
John Doe $28.13 $58,520.00
John Doe $26.92 $56,000.00
John Doe $26.92 $56,000.00
John Doe $23.08 $48,000.00
John Doe $23.08 $48,000.00
John Doe $21.79 $45,319.87
For Example: I have this employee salary history. How to show this same thing in the subform?
John Doe $41.71 $86,758.24
John Doe $41.71 $86,758.24
John Doe $41.40 $86,112.40
John Doe $41.40 $86,112.40
John Doe $39.90 $82,999.90
John Doe $39.90 $82,999.90
John Doe $36.01 $74,899.23
John Doe $36.01 $74,899.23
John Doe $34.29 $71,332.60
John Doe $34.29 $71,332.60
John Doe $34.29 $71,332.60
John Doe $32.53 $67,653.40
John Doe $31.16 $64,822.60
John Doe $31.16 $64,822.60
John Doe $29.40 $61,153.40
John Doe $28.13 $58,520.00
John Doe $26.92 $56,000.00
John Doe $26.92 $56,000.00
John Doe $23.08 $48,000.00
John Doe $23.08 $48,000.00
John Doe $21.79 $45,319.87
What are you seeing? A subform is just a form, so if the query is showing the desired results, then create a form in continuous view or datasheet view. It should reflect your query. Or you could have a pop-up form showing the salary history. Or a listbox. Or... any creative idea.
- Thread starter
- #6
I am not sure I understand what you mean. I am using my main form of the employee profile and I already have a section to where I have the employee information, but I want to have a subform that will show the salary history of that employee. I dont think a list box nor a combo will work for me. I really think a subform will work. Do you think I can create a form in a existing form or is it better to create a subform from the query I created?
- Thread starter
- #7
You state - "I have the desired results in the query"
That to me means you created a query that shows the salary history of an employee. So the recordset of the query shows John Doe's salaries. Use this to create a form, in other words the query will be the recordsouce. I'm not sure what's confusing. A subform is a form in a form. It is a form.
Maybe you can post the SQL of your query.
Your main form is based on the Employee table. Your subform is based on the Salary table. They're connected through the EmployeeID.
"Do you think I can create a form in a existing form or is it better to create a subform from the query I created"
A form within a form is a subform. They may or may not be linked. It is just terminology.
That to me means you created a query that shows the salary history of an employee. So the recordset of the query shows John Doe's salaries. Use this to create a form, in other words the query will be the recordsouce. I'm not sure what's confusing. A subform is a form in a form. It is a form.
Maybe you can post the SQL of your query.
Your main form is based on the Employee table. Your subform is based on the Salary table. They're connected through the EmployeeID.
"Do you think I can create a form in a existing form or is it better to create a subform from the query I created"
A form within a form is a subform. They may or may not be linked. It is just terminology.
-
1
- #9
TheAceMan1
Programmer
How are ya vince1209 . . .
In form design view, use the [blue]subform wizard[/blue]
. Just be sure to select your query as the datasource.
Also, since the subform will be linked, you don't need to include the Name in the subform . . .
See Ya! . . . . . .
Be sure to see thread181-473997
Also faq181-2886
In form design view, use the [blue]subform wizard[/blue]
Also, since the subform will be linked, you don't need to include the Name in the subform . . .
Be sure to see thread181-473997
Also faq181-2886
- Thread starter
- #10
Hey AceMan1, JoeAtWork and fneily: Thanks for your suggestions. What I ended up doing was making three separate tables and created a subform for each table then I set the field to ID and Emplid. In the property under design view and changed the record source to the table and got exactly the results I needed. I had to hide a few of the columns I did not need but got what I needed. For each employee with mulitple entries all of the correct data is there. Thank again. I have one more lingering problem. When I click on Print Preview to look at the entire report, I am able to see the fist page fine. When I click onthe second page I lose my Form header. How can I view each page in it's entirety? I want to be able to see everything in the form header and all of the contents in the detail.
- Status
Similar threads
