Solved

MS Access Query Help With Updating Information To Specific IDs Criteria

Posted on 2016-11-28
6
43 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
[X]
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
  • 3
  • 2
6 Comments
 
LVL 37

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 21

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

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 21
ID: 41904841
you're welcome ~ happy to help

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

705 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