Need help with access 2003 query

[StageLeft]

This is annoying me. I have this query and it's good in SQL:

UPDATE [T 99] SET [Part Number] =
(SELECT x.[new part] FROM xref x
WHERE x.[old part] = [T 99].[Part Number])

As you can see, it updates a field in T 22 based on a cross referenced value in xref table. xref has only "old part" and "new part"

In access, however, I get this error:

"Operation must use an updateable query."

Google is no help. This is a common problem, but no solutions on google fix me. I am an admin on this machine and the database is on my local box. I have tried this:

UPDATE [T 99] SET [Part Number] =
DLookup("[new part]", "xref", "[old part] = [T 99].[Part Number]")

and when I hit run nothing happens. There is no error, no indication as to how many records to update, nothing at all. It's like Access cannot update an entire table based on matching records in another table. My last resort here is to import the entire thing into SQL, run the proper update query, then export back out to Access, but that is a really silly option.

I am eternally indebted to whomever helps!

 

[KLin]

UPDATE [T 99] INNER JOIN xref ON [T 99].[Part Number] = xref.[Old Part] SET [T 99].[Part Number] = [xref].[New Part];

 

[KLin]

WHERE'S MY 3 BUCKS DAMNIT!

😉

 

[imported_Dhaval00]

Wait a minute. I thought I emailed you the solution, KLin?

 

[StageLeft]

I won't know if it works for a couple hours, but it looks like it probably will. I'm so used to the other way I was doing it that I didn't think of a join 🙁

 

[StageLeft]

OK PM me your email!

 

[Markbnj]

As facilitator for this transaction between parties, the Anandtech Moderator for Programming takes $.30 and wishes you all a good day.

 

[Evadman]

Originally posted by: Markbnj
As facilitator for this transaction between parties, the Anandtech Moderator for Programming takes $.30 and wishes you all a good day.

:laugh:

 

[KLin]

Originally posted by: Skoorb
OK PM me your email!

Don't worry about it. 🙂

 

[KLin]

Originally posted by: Markbnj
As facilitator for this transaction between parties, the Anandtech Moderator for Programming takes $.30 and wishes you all a good day.

I am foregoing the stated fee, therefore you get...NOTHING. HAH 😛

 

[StageLeft]

Originally posted by: KLin

Originally posted by: Markbnj
As facilitator for this transaction between parties, the Anandtech Moderator for Programming takes $.30 and wishes you all a good day.

I am foregoing the stated fee, therefore you get...NOTHING. HAH 😛

hehe

Don't worry about it.

Thanks, but seriously it could get you almost half of a coffee from starbucks!!

 

[KLin]

Originally posted by: Skoorb

Originally posted by: KLin

Originally posted by: Markbnj
As facilitator for this transaction between parties, the Anandtech Moderator for Programming takes $.30 and wishes you all a good day.

I am foregoing the stated fee, therefore you get...NOTHING. HAH 😛

hehe

Don't worry about it.

Thanks, but seriously it could get you almost half of a coffee from starbucks!!

Or 3 McDouble's from MickeyD's!!! I may have to rethink your offer.

 

[KLin]

Originally posted by: Skoorb
I won't know if it works for a couple hours, but it looks like it probably will. I'm so used to the other way I was doing it that I didn't think of a join 🙁

Access is very fickle when it comes to action queries.

 

[StageLeft]

Originally posted by: KLin

Originally posted by: Skoorb
I won't know if it works for a couple hours, but it looks like it probably will. I'm so used to the other way I was doing it that I didn't think of a join 🙁

Access is very fickle when it comes to action queries.

Yes it sure is!