Solved

MS Access Query Help With Updating Information To Specific IDs Criteria

Posted on 2016-11-28
6
16 Views
Last Modified: 2016-11-28
I need help with an update query as I want to get it right so I don't mess up the wrong IDs.
BackStory:
I had an old Database Software that was horrible. I exported everything out of it as a CSV file into Excel.
When I did this in the Column SkuUPC (UPC Codes) Excel messed them all up and turned them into things like this "4.02E+12".

I used that saved CSV file to create my new database and I have 1000's of these messed up UPCs.
My original database had IDs (DDMS3ID). I exported those IDs and made a new field in my new database in the SKUs Table called "DDMS3ID".

I know I can do an update query to say hey update SkuUPC to this new UPC where DDMS3ID = DDMS3ID. But I am not 100% sure how to do this and don't want to mess everything up.


Tables invloved:
SKUs

Fields Involved:
SkuUPC

CSV File from Old Database with original Unmessed up UPCs.

Thanks.
0
Comment
Question by:Dustin Stanley
  • 3
  • 2
6 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41904803
If you have the original exported file (not EVER opened by Excel) or you can export again, you're fine.

The CORRECT way to open a .csv file by Excel is to use the Data tab and import it.  That will allow you to use the dialog and specify that those long "numbers" are not numeric at all but text strings.  Excel thinks it is smarter than it is and assumes it knows best when you simply double click on a .csv.  I would prefer to remove this file association from my clients computers because it is extremely dangerous when the .csv file contains long numbers which are actually strings and not numeric or when it contains codes such as ZIP and SSN which can contain leading zeros.

You can import the .csv file directly into Access without giving Excel the opportunity to mess it up.  Make sure you go through the dialog and define the data types correctly or Access will make the same stupid, wrong, assumptions that Excel did when it corrupted your data.

ALWAYS back up your database before attempting any new update operation.  If you have a unique identifier other than the one that Excel messed up,

To make an update query, open the QBE and choose the two tables from the dialog.  Close the dialog and draw a join line from tbl1 to tbl2 on the join field.  If you need to join on multiple columns, you need to draw join liness for each.  Then choose the column you want to update by double clicking on it.  Change the query type from Select to Update.  In the Update To box, type the tablename.column name of the column in tbl2 that has the correct value.  Save the query and run it.
0
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41904821
> ""4.02E+12""

I feel your pain! But as Pat said, if you have the original, you can get it all back "without giving Excel the opportunity to mess it up".  

I like to:
import data then modify everything to be text (please don't let all text be 255 in size -- think about this and adjust), then delete all records, then import again to append to the structure I set up.  This ensures no data will be lost and you can convert data types as you shuffle them to where they need to go -- often this means adding key fields to the import table as you do it so related records can be created and link properly to main records.
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41904833
Worked Perfect! Made a new TempTable and Appended the CSV File to it. Altered what I needed and then made the update Query with a link on IDs. Thanks a bunch this saved me tons of time and helps me get on the road to success.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41904840
Yes, but are you going to remember to NOT open .csv files by double clicking?  Always open unfamiliar .csv files by using the Data/import method to be safe.  Once you know what data a file contains and are certain that it doesn't contain any long numbers or "numeric" codes with leading zeros such as Zip and SSN, then you can use the shortcut but until then, better to be safe than sorry.
0
 
LVL 19
ID: 41904841
you're welcome ~ happy to help

you can Open them to look --  just don't Save!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904844
True but nobody remembers to NEVER SAVE so it is best to use the correct technique when working with unknown files.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

23 Experts available now in Live!

Get 1:1 Help Now