Solved

lookup code in MS access table + insert new code?

Posted on 2011-09-02
24
359 Views
Last Modified: 2012-05-12
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
Comment
Question by:kevin1983
[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
  • 15
  • 9
24 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36473780
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
 

Author Comment

by:kevin1983
ID: 36473827
Ok, In a new module  in access do you mean?
0
 

Author Comment

by:kevin1983
ID: 36473840
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36473858
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
 

Author Comment

by:kevin1983
ID: 36473960
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
 

Author Comment

by:kevin1983
ID: 36473987
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36473997
you can use VBA codes if the condition is quite complex..

the sql statement limit is ~64K
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36474014
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
 

Author Comment

by:kevin1983
ID: 36474024
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36474052
upload alsso a .mdb version of the  database
0
 

Author Comment

by:kevin1983
ID: 36474116
Ok please see attached access database:

Just has two local tables - Ill explain the tables/fields shortly..
PriceCodes.zip
0
 

Author Comment

by:kevin1983
ID: 36474150
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
 

Author Comment

by:kevin1983
ID: 36474173
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36474342
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
 

Author Comment

by:kevin1983
ID: 36474644
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
 

Author Comment

by:kevin1983
ID: 36474655
....been running 2nd query for approx 4 minutes now, i expect it will complete fine at some point
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36474684
must be a big table, how many records ?
0
 

Author Comment

by:kevin1983
ID: 36474687
...bar shows about half way through, not sure why its running slow slow, no errors
0
 

Author Comment

by:kevin1983
ID: 36474695
18769 records  - same number as the local version i sent you
0
 

Author Comment

by:kevin1983
ID: 36474704
guess id prob best leave it for a while now its started - ill aim to check it later
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36474758
it took less than 5 seconds for me to update the table
0
 

Author Closing Comment

by:kevin1983
ID: 36476212
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36476300
it will help if you index those fields from both tables
0
 

Author Comment

by:kevin1983
ID: 36477508
ok - index the primary keys I guess?
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

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