Calculated Field in a table using multiplication

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Calculated Field in a table using multiplication

Post by wire_jp »

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?

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Calculated Field in a table using multiplication

Post by wire_jp »

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)?

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Calculated Field in a table using multiplication

Post by HansV »

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

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Calculated Field in a table using multiplication

Post by wire_jp »

Hi Hans, thank you for your prompt response. I appreciate your help. Thanks