Join 300,419 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,498 people online right now. Registration is fast and FREE... Join Now!
Hi Guys and Gals, I am wondering if it is possible to update a field in access, base on if another field in the same row matches a record in an excel sheet? I'm using access 2000, and at the moment i can only do it if the data to check is in the same database.
Hi Guys and Gals, I am wondering if it is possible to update a field in access, base on if another field in the same row matches a record in an excel sheet? I'm using access 2000, and at the moment i can only do it if the data to check is in the same database.
Chunk
Yes, it is possible.
You need to link in the Excel spreadsheet, or import it into the database that you are working with. Then have to join the fields, and run your query.
Without the code you are using, I can't help beyond that. The wizards, which is cheating, can help more when it comes to writing the update query.
You need to link in the Excel spreadsheet, or import it into the database that you are working with. Then have to join the fields, and run your query.
Without the code you are using, I can't help beyond that. The wizards, which is cheating, can help more when it comes to writing the update query.
That's great, at least i now know i'm not wasting my time going down the wrong path. I haven't done anything yet, but i am sure i will be back if i need more direction
Thanks for your help
Regards Paul
This post has been edited by sir.chunk: 25 Jun, 2009 - 01:36 AM
Right i have now linked a excel sheet to the access database, and have successfully run a query that changes a feild if another feild on the same row matches a field in the excel sheet.
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= [ExcelOOS.Stk Code]));
The problem i have now is that there are some fields that i want change if it contains the text i am trying to check e.g. 1!A567A to change if it contains A567A. I am trying to use the like command with not much success. I've tried in all different ways i can think of but it's not happening
I've tried this (syntax error)
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= Like "*" & [ExcelOOS.Stk Code]));
and this (makes my machine hang)
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= "Like "*" & [ExcelOOS.Stk Code]"));
Plus others....
I'm sure this is school boy stuff, and I feel as i'm going bonkers, i'm sure the solution is staring me in the face!
This post has been edited by sir.chunk: 25 Jun, 2009 - 04:28 AM
Right i have now linked a excel sheet to the access database, and have successfully run a query that changes a feild if another feild on the same row matches a field in the excel sheet.
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= [ExcelOOS.Stk Code]));
The problem i have now is that there are some fields that i want change if it contains the text i am trying to check e.g. 1!A567A to change if it contains A567A. I am trying to use the like command with not much success. I've tried in all different ways i can think of but it's not happening
I've tried this (syntax error)
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= Like "*" & [ExcelOOS.Stk Code]));
and this (makes my machine hang)
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= "Like "*" & [ExcelOOS.Stk Code]"));
Plus others....
I'm sure this is school boy stuff, and I feel as i'm going bonkers, i'm sure the solution is staring me in the face!
Try it this way:
SQL
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference] Like "*" AND [Product.Product Reference]=[ExcelOOS.Stk Code]));
This post has been edited by P4L: 25 Jun, 2009 - 08:00 AM
Right this code using exact match returns 301 records to change
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]=[ExcelOOS.Stk Code]));
And this one using the like command returns 3 records to change
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference] Like "*" & [ExcelOOS.Stk Code] & "*"));
Why is this, surely the like command code should return 301 plus more?! My brain hurts, and i know it's gonna be simple
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]=[ExcelOOS.Stk Code]));
This code is performing the same task as an INNER JOIN in Access, while the code below is saying to look at multiple criteria, and retrieve the rows that satisfy ALL the criteria.
CODE
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference] Like "*" & [ExcelOOS.Stk Code] & "*"));
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference] Like "*" AND [Product.Product Reference]=[ExcelOOS.Stk Code]));
Thanks, this is further than i have got. It gives the same results as using equals (301), but i know for a fact it should return at least 302+ records
This is what i want it to do: First record in the excel sheet is A978D -- the code finds A978D in the product.product reference so it changes the 'product.Can be ordered online' field to '0'. The code also finds 1!A978D and changes the 'product.Can be ordered online' field. It also finds 2!A978D and does the same. Once it has gone through the 'product.product reference' column, it then goes onto the second record in the excel sheet.... and so on.
But it's not finding the 1!A978D, 2!A978D.... product codes.
This post has been edited by sir.chunk: 25 Jun, 2009 - 08:27 AM
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference] Like "*" AND [Product.Product Reference]=[ExcelOOS.Stk Code]));
Thanks, this is further than i have got. It gives the same results as using equals (301), but i know for a fact it should return at least 302+ records
This is what i want it to do: First record in the excel sheet is A978D -- the code finds A978D in the product.product reference so it changes the 'product.Can be ordered online' field to '0'. The code also finds 1!A978D and changes the 'product.Can be ordered online' field. It also finds 2!A978D and does the same. Once it has gone through the 'product.product reference' column, it then goes onto the second record in the excel sheet.... and so on.
But it's not finding the 1!A978D, 2!A978D.... product codes.
Ok, run a simple query.
CODE
Select * From Product, ExcelOOS Where [Product.Product Reference]=[ExcelOOS.Stk Code]
This will tell you how many row it is finding that are in common. This type of a query will bring back ALL the columns from BOTH tables where the product reference is the same as the ExcelOOS stk Code. If it brings back 302+ rows of data then there is a flaw in the UPDATE query.
Forum Index:
Programming Help |
C and C++ |
VB6 |
Java |
VB.NET |
C# |
ASP.NET |
PHP |
ColdFusion |
Perl and Python |
Ruby |
Databases |
Other Languages |
Game Programming |
Mobile Development |
Software Development |
Computer Science |
Industry News |
Programming Tutorials |
C++ Tutorials |
Visual Basic Tutorials |
Java Tutorials |
VB.NET Tutorials |
C# Tutorials |
Linux Tutorials |
PHP Tutorials |
ColdFusion Tutorials |
Windows Tutorials |
HTML/JavaScript Tutorials |
CSS Tutorials |
Flash Tutorials |
Web Promotion Tutorials |
Graphic Design & Photoshop Tutorials |
Software Development Tutorials |
Database Tutorials |
Other Language Tutorials |
ASP.NET Tutorials |
Game Programming Tutorials |
WPF & Silverlight Tutorials |
| 
