Solved

Access Update query check

Posted on 2014-04-23
4
259 Views
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);

TIA,

Tim
0
Comment
Question by:gibneyt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 100 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.
0
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 75 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.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 75 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).
0
 

Author Closing Comment

by:gibneyt
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

Agreed?

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

Thanks Gents,

Tim
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

749 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