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    
post May 15 2018, 01:28 PM

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

Go to the top of the page
post May 15 2018, 01:33 PM

Access Wiki and Forums Moderator
Posts: 74,172
Joined: 19-June 07
From: SunnySandyEggo

Hi J,

Welcome to UtterAccess!

Here's the basic syntax to compare Table1 with Table2 and update Table1 with good data from 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
post May 15 2018, 05:40 PM

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?

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    17th January 2019 - 02:12 PM