Solved

Access Update query check

Posted on 2014-04-23
4
256 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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now