Update concept craters

[scottshiv]

I have several update queries that all update the same field and I couldn't figure out how to combine them, so I came up with the idea of creating a separate cross reference table. The idea was to join the tables on the provider field and then replace the current provider in the provider table with the provider value in the Xref table. One of the advantages of this was I only had to pass the master database one time. Well, the new query runs until the end of time and does nothing.

Xref Table Fields
Clinic
Master

Provider Table field
Provider

Join both tables on Clinic = Provider

Update [Provider]![Provider] to [Xref]![Master]

I was doing all of this from the design view screen. At first the screen would not let me drag and drop the Xref provider into the Update to field on the design view screen, so I typed [Provider Xref]![Master] in there and let her rip. Jesus will come back before my job finishes. What am I missing here?

 

[lespaul]

can you switch to the SQL view and post the acutal query?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[scottshiv]

Here is the SQL code. I can't see anything wrong with it but it never goes to end of job. Could the problem be that the field being updated is also the join field?

UPDATE [Title XX test] INNER JOIN [Provider Xref] ON [Title XX test].Provider = [Provider Xref].Clinic SET [Title XX test].Provider = [Provider Xref].[Master];

 

[IT4EVR]

In order to update the Provider table, unless you want to update every record in Provider, you need to establish some sort of relationship of the Provider table to at least one of the following tables (Title XX Test] or [ProviderXref]