UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select Query To Update Query, Access 2016    
 
   
Mouse11
post May 15 2018, 01:28 PM
Post#1



Posts: 21
Joined: 15-June 11



Hi Everyone,

I am struggling a bit with converting a select query to an update query .
all my searching hasn't worked well with any decent results.

I started by creating a query (without matching products)

I am looking to compare two tables [1932-47Inventory] and [Products] they have the same two fields [pID] and [pSection] .
I Then need to update the [Products] Table and the same two fields with the info from the [1932-47Inventory] table

The query/SQL is:
SELECT [1932-47Inventory].pID, [1932-47Inventory].pSection, products.pID, products.pSection
FROM [1932-47Inventory] LEFT JOIN products ON [1932-47Inventory].[pID] = products.[pID];

I wanted to take a crack at this prior to asking for help so I could learn but still struggling a bit with this last part.

Thanks for your help in advance

J
Go to the top of the page
 
theDBguy
post May 15 2018, 01:33 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,438
Joined: 19-June 07
From: SunnySandyEggo


Hi J,

Welcome to UtterAccess!
welcome2UA.gif

Here's the basic syntax to compare Table1 with Table2 and update Table1 with good data from Table2.

CODE
UPDATE Table1
INNER JOIN Table2
ON Table1.CommonFieldName=Table2.CommonFieldName
SET Table1.FirstFieldToUpdate=Table2.FirstFieldToUpdateFrom, Table1.SecondFieldToUpdate=Table2.SecondFieldToUpdateFrom

Hope it helps...

PS. Make sure you have a good backup copy of your database before trying the above.
Go to the top of the page
 
Mouse11
post May 15 2018, 05:40 PM
Post#3



Posts: 21
Joined: 15-June 11



Hey DBguy!

Wow, thanks so much, Im pretty sure it will work. I gave it a try and im getting a type conversion failure.

basically on the table and field I wish to update its field type is set to Number. I have a (lookup table query) table query Drop Down selection for that field [pSection]that converts the number to text so users can select the section by name.

Is there a way to look at the actual number behind the query for that field on that table? and use it (number type) for the update query? Field as Number? My query looks like this...

UPDATE Products INNER JOIN [1932-47Inventory] ON [Products].[pID]=[1932-47Inventory].[pID] SET Products.pSection = [1932-47Inventory].[pSection];

My table Products and the field named pSection is the field that is the Number Type. This field is the one that has the Combo Box Table/Query that converts the number into a readable text selection for the pSections Field. I guess I need some way to look at the text and convert it to the number it refers to.

Any suggestions?

Thanks
J
This post has been edited by Mouse11: May 15 2018, 06:24 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th October 2018 - 07:52 AM