Newbie Update query help

[mcinnes01]

Hi,

I am trying to make an update query that updates multiple fields from another table.

Basically it holds the relational data for staff using job codes and in my database I also hold the employee number for each of the 6 relationships per job.

Every job has 6 relationships:
Manager
Expenses Authoriser
Timesheets Authoriser
Training Authoriser
Leave Authoriser
Special Leave Authoriser

The HR system has a table called REL that holds the Job Code the relationship is for and then the job code of the person who is in the role of the 6 relationships above.

My job code management database also holds this data but has the employee number as well as the job code for each of the 6 relationships.

When a change is made e.g. if a person in 1 of the 6 relationship positions moves to say a different department and someone else moves in to their role, I need a way of updating the employee numbers for each of the 6 relationships on the table in my database.

Can anyone explain how I can get my table "AUTH" to update each of the relationships employee ID fields:

[Manager ID]
[Expenses Auth ID]
[Timesheet Auth ID]
[Training Auth ID]
[Leave Auth ID]
[SP Leave Auth ID]

By finding the [EMP_ID] from my database view [TMC_POS_MPO]

Where

[AUTH]![Manager Job]=[TMC_POS_MPO]![POS_NUMBER]
[AUTH]![Expense Auth Job]=[TMC_POS_MPO]![POS_NUMBER]
[AUTH]![Timesheet Auth Job]=[TMC_POS_MPO]![POS_NUMBER]
[AUTH]![Training Auth Job]=[TMC_POS_MPO]![POS_NUMBER]
[AUTH]![Leave Auth Job]=[TMC_POS_MPO]![POS_NUMBER]
[AUTH]![SP Leave Auth Job]=[TMC_POS_MPO]![POS_NUMBER]

 

[JamesMcS]

If there's a way, you'd be best off linking into the HR table. That way you don't have two unsynchronised copies of the same data. I would say that, because the join between the two tables would be different depending on which role is changing, you'd have to have 6 update queries. Make sure you've got your join in the right place, then just update the field to [AUTH].[Fieldname].

 

[mcinnes01]

Hi thanks for the reply, so basically if I want to keep things the way I have them I would need 6 seperate update queries?

I am really rubbish at update queries, could you explain how I go about doing this for 1 of the six columns e.g. manager as per my example?

Thanks again

Andy

 

[JamesMcS]

Yep. SQL would look something like:

UPDATE [Destination Table] INNER JOIN [Source Table] ON [Destination Table].JoinField= [Source Table].JoinField SET [Destination Table].Fieldname] = [Source Table].Fieldname

Essentially, in design view, create a join beteween the two tables (I guess employee ID), drag down the field you want to update, and in the update to box put [sourcetable].fieldname.

 

[mcinnes01]

is it possible to make this work in one update query, obivously in its current state it doesn't work but I was wondering if it can be altered?

UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[Manager Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[Manager ID] = [TMC_POS_MPO].[EMP_ID]
UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[Expense Auth Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[Expense Auth ID] = [TMC_POS_MPO].[EMP_ID]
UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[Timesheet Auth Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[Timesheet Auth ID] = [TMC_POS_MPO].[EMP_ID]
UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[Training Auth Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[Training Auth ID] = [TMC_POS_MPO].[EMP_ID]
UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[Leave Auth Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[Leave Auth ID] = [TMC_POS_MPO].[EMP_ID]
UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[SP Leave Auth Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[SP Leave Auth ID] = [TMC_POS_MPO].[EMP_ID]

 

[JamesMcS]

I don't think so, as your joins are different. You could automate it in VBA though:

Sub Run_updates
docmd.setwarnings 0
docmd.runsql "[Insert SQL statement here]"
docmd.runsql.....
docmd.setwarnings -1
End Sub

 

[mcinnes01]

Hi,

I have made 6 queries and created a sub, but I get an error when I try to run any of there queries.

"3073 - Operation must be an updatable query"

This is my code, the error occurs even if I run the queries on their own.

Sub authUP()
DoCmd.SetWarnings 0
DoCmd.OpenQuery "maup"
DoCmd.OpenQuery "exup"
DoCmd.OpenQuery "tiup"
DoCmd.OpenQuery "trup"
DoCmd.OpenQuery "lvup"
DoCmd.OpenQuery "spup"
DoCmd.OpenQuery "authstrIDQuery"
DoCmd.SetWarnings -1
End Sub

 

[JamesMcS]

Are you trying to update a table or a query? You should be updating tables really

 

[mcinnes01]

the table is called AUTH which is in my queries e.g.:

UPDATE [AUTH] INNER JOIN TMC_POS_MPO ON AUTH.[Manager Job] = TMC_POS_MPO.[POS_NUMBER] SET AUTH.[Manager ID] = [TMC_POS_MPO].[EMP_ID];

 

[mcinnes01]

AUTH is my access table
TMC_POS_MPO is a view therefore not updatable just readable