Updating an Access 200 Table with the Results of a Query 1

[boone35223]

Using Access 2000, I am trying to update a field of an existing table with the results of a query. The results of the query include a primary key field, the current values in the table, and the desired values for the table. The update query does not seem to fit since the desired values are not unique. The append query may be appropriate but I am not sure. Can someone point me in the right direction?

Following is the SQL view of the query (the query is actually pulling some information from another query instead of the table I wish to change):

SELECT qMB_Wage_Summary_Step2.pr_check_to, qMB_Wage_Summary_Step2.pr_emp_name, qMB_Wage_Summary_Step2.pr_emp_firstname, qMB_Wage_Summary_Step2.pr_emp_dept, qMB_Wage_Summary_Step2.[Jefferson County Wages], qMB_Wage_Summary_Step2.[LT2-], [Jefferson COunty Wages]*1 AS JeffCo, +[JeffCo]*0.0045 AS CorrLT2, +[CorrLt2]+[LT2-] AS LT2OneTime, +[CorrLT2]/[JeffCo] AS Rate
FROM qMB_Wage_Summary_Step2
GROUP BY qMB_Wage_Summary_Step2.pr_check_to, qMB_Wage_Summary_Step2.pr_emp_name, qMB_Wage_Summary_Step2.pr_emp_firstname, qMB_Wage_Summary_Step2.pr_emp_dept, qMB_Wage_Summary_Step2.[Jefferson County Wages], qMB_Wage_Summary_Step2.[LT2-];

The relevant table structure is as follows:

employee_incomes:Table
pr_ssn (primary key, text)
pr_type_id (primary key, text)- various income and deduction codes which includes LT2, the code I wish to update/change.
pr_type_kind (primary key, text)
pr_inc_rate (currency)
pr_inc_this_onetime (currency)

I wish to update the pr_inc_this_onetime field for pr_inc_categ "LT2" only from its current value of zero to the value CorrLT2 from the query.

Thanks.

 

[kjv1611]

Are you sure that the table structure is what it should be? I'm asking, b/c why do you need more than one primary key, when one of them is a SSN?

Also, specifically, a Type_ID doesn't sound like something that should be a primary key for sure, and that seems to me like it would cause all sorts of problems.

Same thing for type_kind... doesn't sound like what you'd want for a primary key in a table that also has SSNs in it...

I'd wonder if you removed the primary key designation on the 2 type.. fields, whether that'd correct the issue altogether.

 

[boone35223]

I have listed the table structure correctly. There are actually five primary keys and I cannot answer your question as to why that is. I cannot (will not) change the table structure as this is our payroll system. My vendor could do this task for me but then I would learn nothing.

Any other thoughts or suggestions?

 

[dhookom]

With Access/JET, you can't include a totals/group by query in another query and expect the result to allow updates. It will be read-only.

You may be able to update a field to a value from a domain aggregate function like DLookup() or DSum().

UPDATE employee_incomes 
SET pr_inc_this_onetime = DLookup("CorrLT2","YourQuery")
WHERE pr_inc_categ="LT2"

I expect there should be some type of criterian in the DLookup() to grab the value from the appriate record in YourQuery.

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

If I understand your reply, you are saying I need an update query with the code you specified in your thread to perform the change?

 

[dhookom]

I think the update query is what you want. If not, try re-phrase your question.

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

I am still trying to connect to a test database. In the meantime, I have written a select query to test the code you provided earlier. Following is the SQL view of the select query:

SELECT employee_incomes.pr_inc_this_onetime, DLookUp("LT2OneTime","qMB_JeffCo_OccTax_Overpayment") AS Expr1, employee_incomes.pr_ssn
FROM employee_incomes
GROUP BY employee_incomes.pr_inc_this_onetime, employee_incomes.pr_type_id, employee_incomes.pr_ssn
HAVING (((employee_incomes.pr_type_id)="LT2"));

The results are that LT2OneTime (the amount I wish to update the LT2 field in the employee_incomes table) are all the same and from the first row from the
qMB_JeffCccTax_Overpayment query. Any suggestions on what I have entered incorrectly?

 

[dhookom]

As I stated earlier: "I expect there should be some type of criterian in the DLookup() to grab the value from the appriate record in YourQuery."

Can you tell us which value from qMB_JeffCccTax_Overpayment corresponds with which value in employee_incomes? If there isn't a unique record in qMB_JeffCccTax_Overpayment for each record in employee_incomes, then I don't think this is possible.

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

In the qMB_JeffCccTax_Overpayment query, the pr_check_to (social security number) is the unique record. I cannot find an example of how to assign this as a criterian in the DLookup() though. Thanks.

 

[dhookom]

Something like:

DLookUp("LT2OneTime","qMB_JeffCo_OccTax_Overpayment","pr_check_to =""" & pr_ssn & """")

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

The update query worked in my test database once the criterian was added to the DLookup function. I appreciate your assistance. You saved me countless hours.