Solved

MS Access Query Help With Updating Information To Specific IDs Criteria

Posted on 2016-11-28
6
26 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

863 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

27 Experts available now in Live!

Get 1:1 Help Now