Look Up ??

  • Thread starter Thread starter Steve Andler
  • Start date Start date
S

Steve Andler

Hi ;

I have a table as below ;

Product No Code 1 Code2 Code 3
12345 Z T C
67891 K X L

Code fields are the information depending on the product
and kept for internal purposes.

Its been decided to change the coding system as to be all
new and three digit numbers.

For ex : Z is now AD2
C is now CXT

I have a reference table showing that relationship between
the old codes and new codes .

I’d like to create a query that converts all my old codes
with the new ones field by field in my tables .

I’d appreciate any help .

Steve
 
Hi,


If you have a cascade update reference integrity, just update the
reference table, the one with the list of the codes.

Otherwise, it is something like:

UPDATE myTable SET Code1='AD2' WHERE Code1='Z'

and so on, one query per code, per field. Require much more stamina, or, if
you prefer complexity and are sure your computer won't get off for a couple
of hours (just kidding, but indeed, it may take "some" time):


UPDATE myTable
SET Code1=SWITCH( Code1='Z', 'AD2', Code1='C', 'CXT', TRUE, Code1),
Code2=SWITCH( Code2='Z', 'AD2', Code2='C', 'CXT', TRUE,
Code2),
Code3=SWITCH( Code3='Z', 'AD2', Code3='C', 'CXT', TRUE,
Code3)



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel ;

Thank you very much for your reply .
However –unfortunately – I think the problem is much more
complex than I have given in the example.

One of the reason for that is the reference table of old
vs new codes may change frequently . (I have linked that
table to a database)
For example ;
Code Z might have changed to “AD2” but may change to “AD3”
next month (or change to something else the month after ) .

And the other reason is I have hundreds of these codes
which should be changed with the new ones so that I think
it will take too much time to write one by one which code
should be what .

What I would like to do is ,as in excel with Vlookup
function , to update the code fields automatically
referring to a reference table where the old code and the
new code is stated .

I believe I could do that in excel with vlookup , but my
tables may change (I could have new products or less
products which are not in use) so I have linked the tables
in access .

Thank you very much
-----Original Message-----
Hi,


If you have a cascade update reference integrity, just update the
reference table, the one with the list of the codes.

Otherwise, it is something like:

UPDATE myTable SET Code1='AD2' WHERE Code1='Z'

and so on, one query per code, per field. Require much more stamina, or, if
you prefer complexity and are sure your computer won't get off for a couple
of hours (just kidding, but indeed, it may take "some" time):


UPDATE myTable
SET Code1=SWITCH( Code1='Z', 'AD2',
Click to expand...
Code1='C', 'CXT', TRUE, Code1),
 
Hi,


Then, use cascade update referential data integrity, that will care
about the tables referencing the code. For the code, make a conversion
table:

Conversions ' table
FromThis, ToThis ' fields
'x'', 'xyz'
'y', 'y22' 'data



then,


UDPATE myCodes INNER JOIN Conversions ON myCodes.code =
conversions.FromThis
Set myCodes.code = conversions.ToThis



would update the table of code, and, by cascade, the dependant tables too.
Just one simple query, as many code you want... really, can't be simpler,
no?



Hoping it may help,
Vanderghast, Access MVP


Hi Michel ;

Thank you very much for your reply .
However –unfortunately – I think the problem is much more
complex than I have given in the example.

One of the reason for that is the reference table of old
vs new codes may change frequently . (I have linked that
table to a database)
For example ;
Code Z might have changed to “AD2” but may change to
“AD3”
next month (or change to something else the month after ) .

And the other reason is I have hundreds of these codes
which should be changed with the new ones so that I think
it will take too much time to write one by one which code
should be what .

What I would like to do is ,as in excel with Vlookup
function , to update the code fields automatically
referring to a reference table where the old code and the
new code is stated .

I believe I could do that in excel with vlookup , but my
tables may change (I could have new products or less
products which are not in use) so I have linked the tables
in access .

Thank you very much
-----Original Message-----
Hi,


If you have a cascade update reference integrity, just update the
reference table, the one with the list of the codes.

Otherwise, it is something like:

UPDATE myTable SET Code1='AD2' WHERE Code1='Z'

and so on, one query per code, per field. Require much more stamina, or, if
you prefer complexity and are sure your computer won't get off for a couple
of hours (just kidding, but indeed, it may take "some" time):


UPDATE myTable
SET Code1=SWITCH( Code1='Z', 'AD2',
Click to expand...
Code1='C', 'CXT', TRUE, Code1),
 
Hi Michel ;

I am sorry , not sure I follow you . ( I am a beginner).

I have created the conversion table named "Codes" , fields
are “Code” “Description” and “Old Code” .
My other table named "Part List Translated " ,that I would
like to convert the Code field with the new codes has
following fields ; “Part Number” “Part Description”
and “Code1” .

Could once again explain what I should write in my query
to get the result .

Could you also let me know what is "cascade update
referential data integrity"?
Thanks

Steve
 
Hi,


Since you use illegal names, you have to enclose them in [ ].


UPDATE [Part List Translated] INNER JOIN Codes ON [Part List
Translated].Code=Codes.[Old Code] SET [Part List
Translated].Code=Codes.Code



where I assume Codes.Code hold the new codes.


The referential data integrity can be edited in the Relations window. Right
click a relation between tables, and a kind of properties sheet would appear
(or bring the two tables, drag and drop related field of one table over the
other) and you have to Enforce the relation and to check the Cascade Update.
From that point, any modification of a value in the table of reference will
be propagate to (each of) the referred table. You have to edit such a
relation for each field that have that relation, for each table referring to
the related table. Note that enforcing a relation implies that a value in
the "foreign" table field MUST be in the "referred" table too, or the update
(or the append of a new record) in the foreign table will be rejected. This
way, you are sure that any value in the foreign table field has a match in
the referred table field.


Hoping it may help,
Vanderghast, Access MVP


Hi Michel ;

I am sorry , not sure I follow you . ( I am a beginner).

I have created the conversion table named "Codes" , fields
are “Code” “Description” and “Old Code”
..
My other table named "Part List Translated " ,that I would
like to convert the Code field with the new codes has
following fields ; “Part Number” “Part Description”
and “Code1” .

Could once again explain what I should write in my query
to get the result .

Could you also let me know what is "cascade update
referential data integrity"?
Thanks

Steve
 
I think you would need to use an update query using your conversion table.
Since this is a one time thing, I would probably use trhee update queries.

UPDATE ExistingTable INNER JOIN ConversionTable
ON ExistingTable.Code1 = ConversionTable.OldCode
SET ExistingTable.Code1 = ConversionTable.NewCode

UPDATE ExistingTable INNER JOIN ConversionTable
ON ExistingTable.Code2 = ConversionTable.OldCode
SET ExistingTable.Code2 = ConversionTable.NewCode

UPDATE ExistingTable INNER JOIN ConversionTable
 
Thanks a lot Michel ;

You’ve been very helpful . It worked perfectly
Unfortunately I have faced with one more difficulty.
The translated codes sometimes are not unique .
For ex : C(old code) could be CXT or
CXV
CXW
depending on the master table called " Part List
Translated " .

If that part list is for a different product than
translation for that product may be different for some
codes .However there are not many codes like that , maybe
10 .

What I would like to do is add some strings to my previous
query only for these codes.

So when I work on a different parts list (for example
on "Part List Translated2") I can modify the string
stating that now the translation of code should be changed
to a different value ;

For ex : If the table name is - Part List Translated2- C
should be translated to CXV .

Thank you very much

Steve
-----Original Message-----
Hi,


Since you use illegal names, you have to enclose them in [ ].


UPDATE [Part List Translated] INNER JOIN Codes ON [Part List
Translated].Code=Codes.[Old Code] SET [Part List
Translated].Code=Codes.Code



where I assume Codes.Code hold the new codes.


The referential data integrity can be edited in the Relations window. Right
click a relation between tables, and a kind of properties sheet would appear
(or bring the two tables, drag and drop related field of one table over the
other) and you have to Enforce the relation and to check the Cascade Update.
From that point, any modification of a value in the table of reference will
be propagate to (each of) the referred table. You have to edit such a
relation for each field that have that relation, for each table referring to
the related table. Note that enforcing a relation implies that a value in
the "foreign" table field MUST be in the "referred" table too, or the update
(or the append of a new record) in the foreign table will be rejected. This
way, you are sure that any value in the foreign table field has a match in
the referred table field.


Hoping it may help,
Vanderghast, Access MVP


Hi Michel ;

I am sorry , not sure I follow you . ( I am a beginner).

I have created the conversion table named "Codes" , fields
are “Code” “Description” and “Old Code”
..
My other table named "Part List Translated " ,that I would
like to convert the Code field with the new codes has
following fields ; “Part Number” “Part Description”
and “Code1” .

Could once again explain what I should write in my query
to get the result .

Could you also let me know what is "cascade update
referential data integrity"?
Thanks

Steve
-----Original Message-----
Hi,


Then, use cascade update referential data integrity, that will care
about the tables referencing the code. For the code, make a conversion
table:

Conversions ' table
FromThis, ToThis ' fields
'x'', 'xyz'
'y', 'y22' 'data



then,


UDPATE myCodes INNER JOIN Conversions ON myCodes.code =
conversions.FromThis
Set myCodes.code = conversions.ToThis



would update the table of code, and, by cascade, the dependant tables too.
Just one simple query, as many code you want... really, can't be simpler,
no?



Hoping it may help,
Vanderghast, Access MVP


Hi Michel ;

Thank you very much for your reply .
However –unfortunately – I think the problem is much more
complex than I have given in the example.

One of the reason for that is the reference table of old
vs new codes may change frequently . (I have linked that
table to a database)
For example ;
Code Z might have changed to “AD2” but may change to
“AD3”
next month (or change to something else the month after ) .

And the other reason is I have hundreds of these codes
which should be changed with the new ones so that I think
it will take too much time to write one by one which code
should be what .

What I would like to do is ,as in excel with Vlookup
function , to update the code fields automatically
referring to a reference table where the old code and the
new code is stated .

I believe I could do that in excel with vlookup , but my
tables may change (I could have new products or less
products which are not in use) so I have linked the tables
in access .

Thank you very much

Code1='C', 'CXT', TRUE, Code1),


.
Click to expand...


.
Click to expand...
 
Thanks John ;

Thats very helpful;
I was wondering if this three queries could be written
as one query . Because actually i have 15 code colums to
be translated and there are 4 list of tables to be updated.
That means i should have 60 update queries which is too
much .

Thanks for any help

Steve
 
You can do this with DLookup function, but the speed WILL be slow.

UPDATE ExistingTable
SET Code1 = DLookup("NewCode","ConversionTable","OldCode=""" & Nz(Code1) & """"),
SET Code2 = DLookup("NewCode","ConversionTable","OldCode=""" & Nz(Code2) & """"),
SET Code3 = DLookup("NewCode","ConversionTable","OldCode=""" & NZ(Code3) & """"),
SET Code4 = DLookup("NewCode","ConversionTable","OldCode=""" & NZ(Code4) & """"),
....

This could blow up on fields with null values.
 
Hi John ;
Thanks .

But when I run this query it requries a -parameter value
entered- and then gives error message saying -Opertaion
must use an updatable query- .How can this be solved ?
I could not manage to get the result by that query ?

Steve

-----Original Message-----
You can do this with DLookup function, but the speed WILL be slow.

UPDATE ExistingTable
SET Code1 = DLookup
Click to expand...
("NewCode","ConversionTable","OldCode=""" & Nz(Code1)
& """"),
SET Code2 = DLookup
Click to expand...
("NewCode","ConversionTable","OldCode=""" & Nz(Code2)
& """"),
SET Code3 = DLookup
Click to expand...
("NewCode","ConversionTable","OldCode=""" & NZ(Code3)
& """"),
SET Code4 = DLookup
Click to expand...
("NewCode","ConversionTable","OldCode=""" & NZ(Code4)
& """"),
 
Back
Top