MS Access Query Help With Updating Information To Specific IDs Criteria

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.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> ""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
Dustin StanleyEntrepreneurAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
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
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

you can Open them to look --  just don't Save!
0
PatHartmanCommented:
True but nobody remembers to NEVER SAVE so it is best to use the correct technique when working with unknown files.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.