Help with Data Entery

[mobaligh]

Hello,

I have two tables, tblEmployees, tblEmployeePayment. tblEmployees has the following fields.

EmployeeID
Name
Salary
Status
Employeegroup

Data for this table comes from our payroll system. The other table, tblEmployeepayment, which has many to one relationship with tblEmployees has the following

EmployeeID
PayPerioddate
Paybegdate
Payenddate
Salary

data into this table is entered by the users. The reason I have salary on this table is becasue I want to keep the salary history for each payperiod. So if an employee get a raise in May I want to know how much he was paid in April. What I would like to do is papulate the salary field in tblemployeepayment based on tblemployees. I don't want to do lookup because we get new updated tblemployees everymonth. I tried to do an update query but I couldn't figure it out how to update this field.

 

[Sprawl]

I may be misunderstanding a little.

But i dont think you need the "salary" feild in the first table. Just create a join between the two tables based on EmployeeID, and in your query include all fields between the two tables.

the resulting output / screen would give you the following

EmployeeID , Name , Status , Employeegroup, PayPerioddate, Paybegdate, Payenddate, Salary

And when run, each individual pay period would show up as it's own line

 

[mobaligh]

but if employee salary changes the query results will changes. I get the salary information from our payroll system. I want to keep salary history. I want know what was empoyee salary each pay period. Like

EmployeeID Name Salary Payperiod
123 XYZ $100 05/15/2007
123 XYZ $110 05/31/2007
123 XYZ $115 06/15/2007

 

[boblarson]

The more "correct" normalization version would say that you should store a table of salaries with effective dates. I say that is a pain-in-the-butt to try to implement. So in this case it actually makes more sense to store the salary at the time in the table.

 

[rainman89]

if u want to keep the salary history id advise making a new table called salary with
salaryID
payperiod
employee id
salary

 

[rainman89]

grrr bob
why do u beat me everytime?

 

[RuralGuy]

For historical reasons, the OP is correct in that you will want the Salary at the time of the pay period recorded in the tblEmployeepayment table. I would use the Current event of the form to put the tblEmployees Salary in the tblEmployeepayment table when there is a new record. You are still going to want to use a query as Sprawl suggested but include both Salary fields in the query and only display the Salary from the tblEmployeepayment table which you have loaded in the current event. Post back if you need further assistance or my gibberish sounded like...well...gibberish.

 

[boblarson]

grrr bob
why do u beat me everytime?

Ray - Maybe it's because I am using other parts of the website to see when someone has posted (like the new posts link and the who's online feature to see what everyone is doing) and then when I get bored at work, or just don't want to do what I need to do I check more frequently than most people.

Trust me, I'm not trying to do it to you personally

 

[rainman89]

oh no i know. i only use new posts! im not mad.
i just find it humorous that whenever i post u seem to beat me to it!

 

[mobaligh]

Can you give me a little more how to use the current event of the form to update the salary field in the tblemployeepayment.

 

[RuralGuy]

If you do the query as suggested then you will have your TextBox control named (say txtSalary) bound to the tblEmployeePayment.Salary field but tblEmployees.Salary is available in the RecordSet. In the Current event of your form you would have:

If Me.NewRecord Then
   Me.txtSalary = [tblEmployees].[Salary]
End If

That should be all it takes.

 

[mobaligh]

That worked just fine. Thank you very much. I have a form that user would check employee payment history. I would like to allow users to enter a employeeid and click on the go button and then I would display information based on the value they put in the employee field. I am new to access and vb could you please help me with that. thanks

 

[rainman89]

put a combo box on the form

use the wizard and select that u want to find a record on form based on box. set the employeeid feild to the bound column in the combo box

 

[RuralGuy]

Did Ray's suggestion work for you? It should.

 

[mobaligh]

I haven't started that yet the solution you gave me for updating the salary field in the tblemployeepayment is not working I didnot change anything since. I am not sure what to do?

 

[RuralGuy]

Hmmm...I thought you said it worked. Oh, well. Is anything happening? Can you post the code you put in the Current event of the form? How about posting the SQL for the RecordSource for the form?

 

[mobaligh]

Here is the code . In tblEmployees it is annualsalary and in tblEmployeepayment is salary about the SQL from the query I created right?

Private Sub Text33_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.txtSalary = [tblEmployees].[Annualsalary]
End If
End Sub

 

[RuralGuy]

Since the field names are different, it should not be necessary to scope the field with the table name. Do you have both fields in your query? Can you post the SQL for the query?

 

[mobaligh]

SELECT tblEmployees.*, tblEmployeepayment.PayPeriodDt, tblemployeepayment.PayBegDt, tblEmployeepayment.PayEndDt, tblEmployeepayment.PartialPayment, tblEmployeepayment.NumberofHrs, tblEmployeepayment.SDADailyPay, tblEmployeepayment.salary
FROM tblEmployees INNER JOIN tblEmployeepayment ON tblEmployees.EmployeeID = tblEmployeepayment.EmployeeID;

 

[RuralGuy]

AhhHaa!!! Your code needs to be in the Current event of the Form, not the BeforeUpdate event of a TextBox.