Access Query
Access Query
#1
Posted 24 June 2009 - 02:53 AM
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
#2
Re: Access Query
Posted 24 June 2009 - 07:52 AM
sir.chunk, on 24 Jun, 2009 - 02:53 AM, said:
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.
#3
Re: Access Query
Posted 25 June 2009 - 01:35 AM
P4L, on 24 Jun, 2009 - 07:52 AM, said:
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 June 2009 - 01:36 AM
#4
Re: Access Query
Posted 25 June 2009 - 04:12 AM
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)
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= Like "*" & [ExcelOOS.Stk Code]));
and this (makes my machine hang)
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 June 2009 - 04:28 AM
#5
Re: Access Query
Posted 25 June 2009 - 07:57 AM
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
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
This post has been edited by sir.chunk: 25 June 2009 - 07:58 AM
#6
Re: Access Query
Posted 25 June 2009 - 07:58 AM
sir.chunk, on 25 Jun, 2009 - 04:12 AM, said:
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)
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference]= Like "*" & [ExcelOOS.Stk Code]));
and this (makes my machine hang)
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:
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 June 2009 - 08:00 AM
#7
Re: Access Query
Posted 25 June 2009 - 08:05 AM
sir.chunk, on 25 Jun, 2009 - 07:57 AM, said:
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
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
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.
UPDATE Product, ExcelOOS SET Product.[Can be ordered online] = "0" WHERE (([Product.Product Reference] Like "*" & [ExcelOOS.Stk Code] & "*"));
#8
Re: Access Query
Posted 25 June 2009 - 08:27 AM
P4L, on 25 Jun, 2009 - 07:58 AM, said:
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 June 2009 - 08:27 AM
#9
Re: Access Query
Posted 25 June 2009 - 11:54 AM
sir.chunk, on 25 Jun, 2009 - 08:27 AM, said:
P4L, on 25 Jun, 2009 - 07:58 AM, said:
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.
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.

Start a new topic
Add Reply





MultiQuote
| 


