kevin1983
asked on
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.
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.
ASKER
Ok, In a new module in access do you mean?
ASKER
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"
Update Table [DATA-PROM_PRICES];
Set [CardCode] = "NEWK22"
where [COM_PRICE_REFERENCE] = "OLDW11"
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"
this is the correct format of the QUERY
Update [DATA-PROM_PRICES]
Set [CardCode] = "NEWK22"
where [COM_PRICE_REFERENCE] = "OLDW11"
ASKER
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?
I guess there not a way of looking up the table I have or would this be to complex?
ASKER
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";
UPDATE [DATA-PROM_PRICES] SET CardCode = "AAH004"
WHERE [COM_PRICE_REFERENCE] = "AAHP"
UPDATE [DATA-PROM_PRICES] SET CardCode = "ABA999"
WHERE [COM_PRICE_REFERENCE] = "ABAR";
you can use VBA codes if the condition is quite complex..
the sql statement limit is ~64K
the sql statement limit is ~64K
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]
post them all and the corresponding value for the field [CardCode]
ASKER
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?
please can you help with the VBA? - or at least get me started
I post them shortly...would excel file be ok?
upload alsso a .mdb version of the database
ASKER
Ok please see attached access database:
Just has two local tables - Ill explain the tables/fields shortly..
PriceCodes.zip
Just has two local tables - Ill explain the tables/fields shortly..
PriceCodes.zip
ASKER
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
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Ill let you know once complete - looks like ill need to leave it running the query for a while.
ASKER
....been running 2nd query for approx 4 minutes now, i expect it will complete fine at some point
must be a big table, how many records ?
ASKER
...bar shows about half way through, not sure why its running slow slow, no errors
ASKER
18769 records - same number as the local version i sent you
ASKER
guess id prob best leave it for a while now its started - ill aim to check it later
it took less than 5 seconds for me to update the table
ASKER
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
it will help if you index those fields from both tables
ASKER
ok - index the primary keys I guess?
update table
set [CardCode]="NEWK22"
where [COM_PRICE_REFERENCE]="OLD
to search for the in other fields,just extend the where clause
update table
set [CardCode]="NEWK22"
where [COM_PRICE_REFERENCE]="OLD
or [Field1]="OLDW11"