Autolookup Query

I have an autolookup query that automatically fills in employee name and rate on a form. What I would like is to be able to update the rate, for each record, without updating all of the data in the query to the new value entered, ie if I change the rate on the form, the new rate value is used in all existing records for the calculation. Has me stumped and would appreciate any suggestions.

Ernie

Is the rate field bound to an underlying table field? What is the
code/expression you are using to do the calculation you mention? What is
the code you are using to update the "employee name" and "rate" controls on
your form? You and I may not have the same definition of "autolookup
query" -- what's your definition?

--
More info, please ...

Jeff Boyce
Access MVP

I have two linked tables, Employee with fields for Employee ID, Base rate, and name(the "one" side), and Timecard Distribution (the "many" side)with fields for Employee ID, Rate ,Name, Hours and Account. The query design includes the following fields from Employee ID table: Base rate, and name, and from the Timecard distribtuion table the fields: Employee ID , Rate, Name , Account, Hours, and a calculated field named Total which equals Rate from Timecard Distribution * Hours. On the form based on the query, when Employee ID is entered, the Base rate and Name is automatically entered with values from the Employee table. While I don't want to change the value of Base rate in Employee table, I would like the option of putting in a different rate in a record and have the calculation for Total to reflect that rate for that record.

----- Jeff Boyce wrote: -----

Ernie

Is the rate field bound to an underlying table field? What is the
code/expression you are using to do the calculation you mention? What is
the code you are using to update the "employee name" and "rate" controls on
your form? You and I may not have the same definition of "autolookup
query" -- what's your definition?

--
More info, please ...

Jeff Boyce
Access MVP
I have two linked tables, Employee with fields for Employee ID, Base rate, and name(the "one" side), and Timecard Distribution (the "many" side)with fields for Employee ID, Rate ,Name, Hours and Account. The query design includes the following fields from Employee ID table: Base rate, and name, and from the Timecard distribtuion table the fields: Employee ID , Rate, Name , Account, Hours, and a calculated field named Total which equals Rate from Timecard Distribution * Hours. On the form based on the query, when Employee ID is entered, the Base rate and Name is automatically entered with values from the Employee table. While I don't want to change the value of Base rate in Employee table, I would like the option of putting in a different rate in a record and have the calculation for Total to reflect that rate for that record.

Ernie

I'm still somewhat baffled. You say

While I don't want to change the value of Base rate in Employee table, I
would like the option of putting in a different rate in a record and have
the calculation for Total to reflect that rate for that record.

If you don't want to record a changed BaseRate, what "record" are you
talking about putting a different rate into? It would still be helpful if
you'd post the SQL statement your query uses.

--
More info, please ...

Jeff Boyce
Access MVP

Jeff, this is the SQL statement the query currently uses:
SELECT [Timecard Distribution].Date, [Timecard Distribution].EmployeeNumber, Employees.LastName, Employees.FirstName, Employees.Rate, [Timecard Distribution].Rate, [Timecard Distribution].[Account Number], [Timecard Distribution].Hours, ([Timecard Distribution]![Rate])*([Timecard Distribution]![Hours]) AS TOTAL
FROM Employees INNER JOIN [Timecard Distribution] ON Employees.EmployeeNumber = [Timecard Distribution].EmployeeNumber
WHERE ((([Timecard Distribution].Date)0));

Ernie

Thanks for posting the SQL statement. I still don't think I understand
enough about where you are trying to change the Rate, and where you are
trying to store the rate. Could you also provide this information, and a
brief description of the table structure that would apply?

Thanks

Jeff Boyce
Access MVP