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
Solved

Access Update query check

Posted on 2014-04-23
4
258 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
4 Comments
 
LVL 35

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

860 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