School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!
You're Browsing As A Guest! Register Now...
Become an Expert!

Join 353,816 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 3,497 people online right now.Registration is fast and FREE... Join Now!



Access Query

Page 1 of 1

Access Query Rate Topic: -----

#1 sir.chunk  Icon User is offline

  • New D.I.C Head
  • Pip

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 08-December 08


Dream Kudos: 0

Posted 24 June 2009 - 02:53 AM

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
Was This Post Helpful? 0
  • +
  • -

#2 P4L  Icon User is offline

  • Geek 4 Life
  • Icon

Reputation: 18
  • View blog
  • Posts: 2,413
  • Joined: 07-February 08


Dream Kudos: 150

Re: Access Query

Posted 24 June 2009 - 07:52 AM

View Postsir.chunk, on 24 Jun, 2009 - 02:53 AM, said:

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.
Was This Post Helpful? 0
  • +
  • -

#3 sir.chunk  Icon User is offline

  • New D.I.C Head
  • Pip

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 08-December 08


Dream Kudos: 0

Re: Access Query

Posted 25 June 2009 - 01:35 AM

View PostP4L, on 24 Jun, 2009 - 07:52 AM, said:

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.


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

Was This Post Helpful? 0
  • +
  • -

#4 sir.chunk  Icon User is offline

  • New D.I.C Head
  • Pip

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 08-December 08


Dream Kudos: 0

Re: Access Query

Posted 25 June 2009 - 04:12 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.

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

Was This Post Helpful? 0
  • +
  • -

#5 sir.chunk  Icon User is offline

  • New D.I.C Head
  • Pip

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 08-December 08


Dream Kudos: 0

Re: Access Query

Posted 25 June 2009 - 07:57 AM

Right this code using exact match returns 301 records to change
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

Was This Post Helpful? 0
  • +
  • -

#6 P4L  Icon User is offline

  • Geek 4 Life
  • Icon

Reputation: 18
  • View blog
  • Posts: 2,413
  • Joined: 07-February 08


Dream Kudos: 150

Re: Access Query

Posted 25 June 2009 - 07:58 AM

View Postsir.chunk, on 25 Jun, 2009 - 04:12 AM, said:

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.

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

Was This Post Helpful? 0
  • +
  • -

#7 P4L  Icon User is offline

  • Geek 4 Life
  • Icon

Reputation: 18
  • View blog
  • Posts: 2,413
  • Joined: 07-February 08


Dream Kudos: 150

Re: Access Query

Posted 25 June 2009 - 08:05 AM

View Postsir.chunk, on 25 Jun, 2009 - 07:57 AM, said:

Right this code using exact match returns 301 records to change
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] & "*"));

Was This Post Helpful? 0
  • +
  • -

#8 sir.chunk  Icon User is offline

  • New D.I.C Head
  • Pip

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 08-December 08


Dream Kudos: 0

Re: Access Query

Posted 25 June 2009 - 08:27 AM

View PostP4L, on 25 Jun, 2009 - 07:58 AM, said:

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]));


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

Was This Post Helpful? 0
  • +
  • -

#9 P4L  Icon User is offline

  • Geek 4 Life
  • Icon

Reputation: 18
  • View blog
  • Posts: 2,413
  • Joined: 07-February 08


Dream Kudos: 150

Re: Access Query

Posted 25 June 2009 - 11:54 AM

View Postsir.chunk, on 25 Jun, 2009 - 08:27 AM, said:

View PostP4L, on 25 Jun, 2009 - 07:58 AM, said:

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]));


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.
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1

Fast Reply

  

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users



Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month

Programming Help

Web Development



What's Here?

  • Members: 353,816
  • Replies: 956,667
  • Topics: 161,531
  • Snippets: 4,968
  • Tutorials: 1,307
  • Total Online: 3,497
  • Members: 99
  • Guests: 3,398