Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

lookup code in MS access table + insert new code?

HI Experts,

Is it possible to search an MS Access 2003 table and lookup a particular code within a particular field, and if that code is found in the field then to insert a code into another field.
For example I would like to search a field named: “COM_PRICE_REFERENCE” for the code: “OLDW11” and anywhere in the table where that code is used then the new code “NEWK22” is inserted into a field named: “CardCode”

I know I could just use the search tool but there’s quite a number of records I need to do this for with different codes so would take a long time to do this manually.

I currently have a separate access table with the Old and new codes I need to use so thought maybe theres a way of doing a lookup using a macros perhaps to insert the relevant data

Any help would be great.
0
kevin1983
Asked:
kevin1983
  • 15
  • 9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try

update table
set [CardCode]="NEWK22"
where [COM_PRICE_REFERENCE]="OLDW11"

to search for the in other fields,just extend the where clause


update table
set [CardCode]="NEWK22"
where [COM_PRICE_REFERENCE]="OLDW11"
or [Field1]="OLDW11"

0
 
kevin1983Author Commented:
Ok, In a new module  in access do you mean?
0
 
kevin1983Author Commented:
does the table name need to be in brackets? - just trying to get the correct syntax

Update Table [DATA-PROM_PRICES];
Set [CardCode] = "NEWK22"
where [COM_PRICE_REFERENCE] = "OLDW11"
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
what i posted is an Update QUERY, not VBA codes

this is the correct format of the QUERY


Update [DATA-PROM_PRICES]
Set [CardCode] = "NEWK22"
where [COM_PRICE_REFERENCE] = "OLDW11"
0
 
kevin1983Author Commented:
oh ok sorry - done in a query and seems to be working fine now thanks, my only concern is i thought a query has a character limit in the SQL? - as theres quite a number of codes ill need to add to extend the where clause using a number of where clauses, or should it be ok?

I guess there not a way of looking up the table I have or would this be to complex?
0
 
kevin1983Author Commented:
if i add just one more where clause it doesnt seem to like it, i have the below, says syntax error - missing operator

UPDATE [DATA-PROM_PRICES] SET CardCode = "AAH004"
WHERE [COM_PRICE_REFERENCE] = "AAHP"
UPDATE [DATA-PROM_PRICES] SET CardCode = "ABA999"
WHERE [COM_PRICE_REFERENCE] = "ABAR";
0
 
Rey Obrero (Capricorn1)Commented:
you can use VBA codes if the condition is quite complex..

the sql statement limit is ~64K
0
 
Rey Obrero (Capricorn1)Commented:
ok, how many values of [COM_PRICE_REFERENCE] do you need in this update ?
post them all and the corresponding value for the field [CardCode]
0
 
kevin1983Author Commented:
Ok, sounds like VBA might be better - i estimate I need to change 100+ COM_PRICE_REFERENCE.
please can you help with the VBA? - or at least get me started

I post them shortly...would excel file be ok?
0
 
Rey Obrero (Capricorn1)Commented:
upload alsso a .mdb version of the  database
0
 
kevin1983Author Commented:
Ok please see attached access database:

Just has two local tables - Ill explain the tables/fields shortly..
PriceCodes.zip
0
 
kevin1983Author Commented:
The table named: "DATA-CURRENT_PRICES"  is the lookup table i thought could be used but currently has a lot of duplicates - the CardCode is the new code that I would like if possible to be inserted into the table named "DATA-PROM_PRICES" into the CardCode field where a matching COM_PRICE_REFERENCE code is found (named Listname in other table) .

and the field named "ListName" in the table DATA-CURRENT_PRICES" is the old Code, which is the same as the field named COM_PRICE_REFERENCE in the table named: DATA-PROM_PRICES

Hope this makes sense?, thanks
0
 
kevin1983Author Commented:
Theres 6105 records currently in the lookup table DATA-CURRENT_PRICES that I thought could be used but im guessing only around 100 to 1300 of those records are unique
0
 
Rey Obrero (Capricorn1)Commented:
see this revised db

1. created query1 to create a table  "newDATA-CURRENT_PRICES"
    this table removes the duplicates from the table "DATA-CURRENT_PRICES"

2. run query2 to update the [CardCode] field in table "DATA-PROM_PRICES"



PriceCodesRev.zip
0
 
kevin1983Author Commented:
Thanks - that looks great, im just trying to run querys now on the live version of the access database - first query run fine, just seems to be taking a while running second query but guess quite a number of records to update.

Ill let you know once complete - looks like ill need to leave it running the query for a while.
0
 
kevin1983Author Commented:
....been running 2nd query for approx 4 minutes now, i expect it will complete fine at some point
0
 
Rey Obrero (Capricorn1)Commented:
must be a big table, how many records ?
0
 
kevin1983Author Commented:
...bar shows about half way through, not sure why its running slow slow, no errors
0
 
kevin1983Author Commented:
18769 records  - same number as the local version i sent you
0
 
kevin1983Author Commented:
guess id prob best leave it for a while now its started - ill aim to check it later
0
 
Rey Obrero (Capricorn1)Commented:
it took less than 5 seconds for me to update the table
0
 
kevin1983Author Commented:
Thanks a lot for help, that worked great- took time running the second query...hours for some reason but sounds like it may just have been related to the computer I was running it on. I'll have to try on another pc soon
0
 
Rey Obrero (Capricorn1)Commented:
it will help if you index those fields from both tables
0
 
kevin1983Author Commented:
ok - index the primary keys I guess?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 15
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now