Update Query Criteria

[jneirinckx]

Good morning,

Using an Update Query I am trying to update data in 2 fields of a table1 with data from table2 where the FirstName and LastName fields match but when I put table2's FirstName and LastName fields as criteria using the builder when I run it, it treats it as a parameter and prompts for input.

Where am I going wrong?

Thanks
Jerry

 

[Brianwarnock]

Besides suggesting checking the spelling there is not much we can guess at, posting the SQL may help.

Brian

 

[jneirinckx]

Thanks for your reply Brian.

Here is the SQL

UPDATE tblContributors INNER JOIN tblContributions ON tblContributors.ContributorID = tblContributions.ContibutorID SET tblContributions.Category = [tblUpdater]![Category], tblContributions.Description = [tblUpdater]![Description]
WHERE (((tblContributors.FirstName)=[tblUpdater]![FirstName]) AND ((tblContributors.LastName)=[tblUpdater]![LastName]));

Thanks again

 

[Brianwarnock]

You have 3 tables here
tblContributors
tblContributions
tblUpdater

and I cannot see how they in.
I'll have to think about it.

Brian

 

[ACMAIN_CHM]

hi Jerry, I am also wondered where is the [tblUpdater] come from , is it a FROM or another table/query?

UPDATE tblContributors INNER JOIN tblContributions
ON a.ContributorID = b.ContibutorID
SET b.Category = [tblUpdater]![Category],
b.Description = [tblUpdater]![Description]
WHERE (a.FirstName=[tblUpdater]![FirstName]) AND (a.LastName=[tblUpdater]![LastName])

 

[Brianwarnock]

I think that you may just have to include tblUpdater in the Update

UPDATE tblContributors INNER JOIN etc to become

UPDATE tblUpdater, tblContributors INNER JOIN etc

Brian

 

[jneirinckx]

This is a separate table that was created by converting an excel worksheet and their is no primary key to connect to.

I was hoping that I could update the category and description fields by matching the lastname and firstname.

Is there another method to do this.

Thanks again

Jerry

 

[Brianwarnock]

What you are attempting should be ok but you must name all tables in the Update stement as I indicated.

Brian

 

[jneirinckx]

Hooray

That did it Brian.

Your a genius.

Wish I could buy you a beer.

Thanks

Jerry