Access Update query check

Posted on 2014-04-23
Medium Priority
Last Modified: 2014-04-23
I am going about this the other way.  Please tell me EXACTLY what the SQL below will do.  This is a check before I push the button and jump into disaster (hopefully not).  That's Mr. Trebek to you.

INSERT INTO dbo_Mfg_Part_Master ( UDFKEY_49 )
SELECT qryExistingMatchingMPNs2.WW_PN
FROM dbo_Mfg_Part_Master INNER JOIN qryExistingMatchingMPNs2 ON (dbo_Mfg_Part_Master.MPNNUM_49 = qryExistingMatchingMPNs2.MPNNUM_49) AND (dbo_Mfg_Part_Master.PRTNUM_49 = qryExistingMatchingMPNs2.PRTNUM_49);


Question by:gibneyt
LVL 41

Accepted Solution

PatHartman earned 400 total points
ID: 40018290
For starters, ALWAYS back up the database before running an untested action query.

The query selects WW_PN from a query where MPNNUM_49 from the part master = MPNNUM_49 from the query and PRTNUM_49 from the part master = PRTNUM_49 from the query, and adds new rows to dbo_Mfg_Part_Master with WW_PN as UDFKEY_49

Are you sure you want to add new rows?  Perhaps this is supposed to be an update query?  Update queries modify the values of columns in EXISTING rows and Insert/appent queries, add NEW rows.

Assisted Solution

Dulton earned 300 total points
ID: 40018291
Rather than hit the "Execute" (red exclamation) button, if you hit the datasetview button, it'll show you exactly what'll be updated without performing the update..... then go back to the designer/sql view and make necessary changes.
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 300 total points
ID: 40018292
Executing the above query will cause the world to end.  Don't do it.

Just kidding.  Executing the above query will insert into table dbo_Mfg_Part_Master, column UDFKEY_49, the results of the SELECT statement (i.e. everything from SELECT on down).

Author Closing Comment

ID: 40018492
WOW!  All within the first 7 minutes!

Yes, backed up of course and done after hours while no one is accessing the database except me.  And possibly even using a test DB first.

I think this is the literal translation of the SQL above:

Update the field dbo_Mfg_Part_master.UDFKEY_49 with the contents of qryExistingMatchingMPNs2.WW_PN where qryExistingMatchingMPNs2.MPNMUN_49 equals dbo_Mfg_Part_Master.MPNNUM_49 AND qryExistingMatchingMPNs2.PRTMUN_49 equals dbo_Mfg_Part_Master.PRTNUM_49


Yes, Datasheet View shows me the contents of WW_PN in a single column.

Thanks Gents,


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question