I have two tables in a Microsoft Access database with following fields: -
PayRate Table: -
PayRateID
PayRate
EmployeePay Table: -
EmployeePayID
PayRateID (foreign key)
Hours
EmployeePay (Calculated Field: [PayRateID]*[Hours])
The calculation for the EmployeePay is not correct, as the database is calculating the PayRateID (from the PayRate Table) x Hours (EmployeePay Table). How do you calculate the correct amount for the EmployeePay?
Calculated Field in a table using multiplication
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Calculated Field in a table using multiplication
Update: I created a query between the two tables, and I was able to achieve the result which I was looking for using the SQL code below:-
SELECT tblEmployeePay.EmployeePayID, tblEmployeePay.PayRateID, tblEmployeePay.RegularHours, [PayRate]*[Hours] AS [Employee Pay]
FROM tblPayRates INNER JOIN tblEmployeePay ON tblPayRate.PayRateID = tblEmployeePay.PayRateID;
My question is: - can I get the correct result using a calculated field in the EmployeePay Table (which I initially described above)?
SELECT tblEmployeePay.EmployeePayID, tblEmployeePay.PayRateID, tblEmployeePay.RegularHours, [PayRate]*[Hours] AS [Employee Pay]
FROM tblPayRates INNER JOIN tblEmployeePay ON tblPayRate.PayRateID = tblEmployeePay.PayRateID;
My question is: - can I get the correct result using a calculated field in the EmployeePay Table (which I initially described above)?
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculated Field in a table using multiplication
A query is the way to do this; as far as I know you cannot refer to fields in another table in the definition of a calculated field.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 67
- Joined: 19 Jan 2018, 00:00
Re: Calculated Field in a table using multiplication
Hi Hans, thank you for your prompt response. I appreciate your help. Thanks