Autofill

[prchrj]

I have two tables. One is tblMain, with employee as a combo box, TotalHours, and Cost field. The second table is employee and it has two fields, Name and Rate. I am trying to do a calculation in the tblMain Cost field that multiplies the TotalHours with the second field "Rate" in that is shown in Employee field of the same table? Thanks

 

[plog]

You don't generally store calculated values, instead you calaculate them when you need them. That means instead of having a Cost field in a table, you should have this in a query and do the calculation there. To build that query do this:

Bring both fields into a query, link them via their common field(s), then create a caluclated field using this:

Cost: TotalHours*Rate

If you have any issues, post back here.

 

[prchrj]

That make sense. Here's my dilemma. I have a field "Employee" that has two fields in it that comes from another table. I can see Name and Rate in the field, but I can't seem to get the calc to see the rate part of the field. Any suggestions? If it was Excel I would reference, for example Sheet1!b2, but this doesn't see to work in Access. Perhaps it is the wrong syntax? Thanks

 

[plog]

I have a field "Employee" that has two fields

That doesn't make sense. Can you explain your issue with data? Also post whatever SQL you have tried to generate a solution.

 

[prchrj]

Ok, I have a table "tblMain" that has three fields. First Field is Employee which is a combo box, TotalHours and Type. The Employee field is a combo box that is linked to another tblEmployee, which has two fields, "Name" and "Rate". When I open the tblMain and select an employee, it shows both Name and Rate. I want to calc TotalHours with Rate, but Rate is the second set of data stored in the Employee field. Hopefully, this clears it up. In the query I entered this; Cost: [employee!rate] * [TotalHours]. But it errors.

I am trying to link the Name and Rate so that the calculation is automatic.

Sorry, its been a while.

 

[plog]

Did you build a query as I outlined? Can you post the SQL from that?

 

[prchrj]

Yes I did and it came back with a popup wanting me to enter "Rate". So it is not seeing the rate in the query. I appreciate your help, but I have to go for the day. Be back on Monday, have a great weekend.

 

[CJ_London]

First Field is Employee which is a combo box, TotalHours and Type. The Employee field is a combo box that is linked to another tblEmployee, which has two fields, "Name" and "Rate".

This does not make sense - a field cannot have two comboboxes.

Are you trying to do this as a calculation in a table or query?

Sounds like you have a lookup field in your table and you think the table or query can see the rate (which it won't)

 

[prchrj]

Problem Solved!
Thank you for the line of questioning, it sparked some rethought as to the design. Have a great weekend.