Replacing a value in a field which exists in more than one table

[Dusty_in_Oz]

Hi,

I have 45 databases that have corresponding fields which all contain the
same prefix. That is, 2 fields in 30 or so databases and one field in the
rest, and the values in those fields all start with a four digit number. I
want to change the four digit number on a regualr basis with out having to
open the tables individually and doing a find/replace on the fields
individually.

Is there a way of doing this in Access? I have attempted to use forms, joins
and also combining a number of update queries using the Expression Builder
and using the Replace ([fieldname],"XXXX","YYYY") option but this is very
time consuming and tedious, and I can only do a table at a time or the
duplicate fields names give an error. I would have to add the table names
every time and this would be very time consuming.

Any guru's have any tips of how to do this?

Thanks in advance!!

Dusty

 

[Jeff Boyce]

Dusty

I'm having some trouble visualizing what you are describing...

"45 databases that have corresponding fields..." sounds like you might be
describing 45 Access tables with identical fields.

"the values in those fields all start with a four digit number" might mean
that the fields contain four character text strings, or might mean there are
numbers that have been formatted to display 4 characters.

I don't understand what business need you are attempting to solve by trying
to "change the four digit number on a regular basis".

Could you post back a simple example of a couple tables' definition, and
perhaps an example of the data in those tables?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Dusty_in_Oz]

HI Jeff and thanks for your help...

Unfortunately I am unable to send any screenshots or table definitions due
to sensitivity of the data.

What I can say, its with respect to engineernig and the 4 digit prefix of
the text value denotes the equipment.

Quite often the equipment has the same maintenance and therefore, its very
easy to change the four digit prefix in the text field and then use the new
code as a unique identifier for loading into an Ingres based DBMS.

This happens quite regularly, hence creating a script that will use the
tablename and modify the four digit prefix on the fly...

i.e. I have a text field (32 characters) which can be anywhere from 6 to 32
characters. e.g. 1234-X or 1234-XY01A010201456 etc and each of these text
values is a unique ID for that piece of equipment.

Problem is, 1234 = MACHINE 1, but 4321 = MACHINE2. Exact same machine, but
the Unique ID has a different prefix to isolate the machines.

I have a number of these machines all with different 4 digit unique ID's.

I want to be able to change the Unique ID code in each of the text fields in
all 45 databases, which I will then load into an Ingres DBMS Server.

It sounds complex, but very simple and easier structure to work with. In the
past they have worked in excel one load at a time, and I want to be able to
run an sql script to modify the table on the fly or using Expression Builder
and link the indivdual tables from 45 databases into one query and then the
script will run across all occurences of the four digit prefix.

Does that make sense?

Cheers....

Dusty

Dusty

I'm having some trouble visualizing what you are describing...

"45 databases that have corresponding fields..." sounds like you might be
describing 45 Access tables with identical fields.

"the values in those fields all start with a four digit number" might mean
that the fields contain four character text strings, or might mean there are
numbers that have been formatted to display 4 characters.

I don't understand what business need you are attempting to solve by trying
to "change the four digit number on a regular basis".

Could you post back a simple example of a couple tables' definition, and
perhaps an example of the data in those tables?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi,

I have 45 databases that have corresponding fields which all contain the
same prefix. That is, 2 fields in 30 or so databases and one field in the
rest, and the values in those fields all start with a four digit number. I
want to change the four digit number on a regualr basis with out having to
open the tables individually and doing a find/replace on the fields
individually.

Is there a way of doing this in Access? I have attempted to use forms, joins
and also combining a number of update queries using the Expression Builder
and using the Replace ([fieldname],"XXXX","YYYY") option but this is very
time consuming and tedious, and I can only do a table at a time or the
duplicate fields names give an error. I would have to add the table names
every time and this would be very time consuming.

Any guru's have any tips of how to do this?

Thanks in advance!!

Dusty

 

[Jeff Boyce]

see comment in-line below...

HI Jeff and thanks for your help...

Unfortunately I am unable to send any screenshots or table definitions due
to sensitivity of the data.

What I can say, its with respect to engineernig and the 4 digit prefix of
the text value denotes the equipment.

This sounds like you have a field that contains more than 4 characters, the
first 4 of which denotes one fact ("the equipment"), and the remainder which
denotes some other fact -- if so, this is counter to good database design,
which calls for "one fact, one field".

Quite often the equipment has the same maintenance and therefore, its very
easy to change the four digit prefix in the text field and then use the new
code as a unique identifier for loading into an Ingres based DBMS.

"easy" and "good idea" don't necessarily work together. Are you saying that
you have multiple pieces of equipment that are all the same model, so that
the maintenance required is the same?

This happens quite regularly, hence creating a script that will use the
tablename and modify the four digit prefix on the fly...

I still don't understand whether you are talking about 45 .mdb files
(databases) or 45 tables (in one .mdb/database). What does "tablename" have
to do with this? Are you using different tables to store data about
different equipment?

i.e. I have a text field (32 characters) which can be anywhere from 6 to 32
characters. e.g. 1234-X or 1234-XY01A010201456 etc and each of these text
values is a unique ID for that piece of equipment.

Problem is, 1234 = MACHINE 1, but 4321 = MACHINE2. Exact same machine, but
the Unique ID has a different prefix to isolate the machines.

So you're saying that "1234" and "4321" prefixes refer to the same type of
equipment, but different physical machines?

Does your database include a table with a field that categorizes the
equipment by "type of machine"?

I have a number of these machines all with different 4 digit unique ID's.

And again, what about "type of machine" category?

I want to be able to change the Unique ID code in each of the text fields in
all 45 databases, which I will then load into an Ingres DBMS Server.

"databases"? or "tables"?

Also, if the text field is truly a "Unique ID", then you probably shouldn't
be changing it. Do you have a different field that you can use to hold an
"ID" that you could, if necessary, change?

It sounds complex, but very simple and easier structure to work with. In the
past they have worked in excel one load at a time, and I want to be able to
run an sql script to modify the table on the fly or using Expression Builder
and link the indivdual tables from 45 databases into one query and then the
script will run across all occurences of the four digit prefix.

Does that make sense?

Sorry, perhaps I'm being dense, but I still don't get why "changing a Unique
ID" would help with your process. What will having a (new, changed) Unique
ID allow you to do that you can't do without it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Cheers....

Dusty

Dusty

I'm having some trouble visualizing what you are describing...

"45 databases that have corresponding fields..." sounds like you might be
describing 45 Access tables with identical fields.

"the values in those fields all start with a four digit number" might mean
that the fields contain four character text strings, or might mean there are
numbers that have been formatted to display 4 characters.

I don't understand what business need you are attempting to solve by trying
to "change the four digit number on a regular basis".

Could you post back a simple example of a couple tables' definition, and
perhaps an example of the data in those tables?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi,

I have 45 databases that have corresponding fields which all contain the
same prefix. That is, 2 fields in 30 or so databases and one field in the
rest, and the values in those fields all start with a four digit number. I
want to change the four digit number on a regualr basis with out having to
open the tables individually and doing a find/replace on the fields
individually.

Is there a way of doing this in Access? I have attempted to use forms, joins
and also combining a number of update queries using the Expression Builder
and using the Replace ([fieldname],"XXXX","YYYY") option but this is very
time consuming and tedious, and I can only do a table at a time or the
duplicate fields names give an error. I would have to add the table names
every time and this would be very time consuming.

Any guru's have any tips of how to do this?

Thanks in advance!!

Dusty

 

[Dusty_in_Oz]

Hi Jeff

Thanks and do understand that whilst data base practice should be adhered to
in most projects, this is a very old design and that data held within the
table structure is sometimes painful.

True, confusion is that I do have 45 mdb files, but I have extracted the
required tables that need the prefix changed out of the mdb files.

Each mdb file is an update to the Ingres configuration database through a
data loading tool.

Once the mdb files have the tables populated with the changes, I load the
data.

All mdb files contain the same tables and same structure. There is up to 5
different tables and there corresponding deltas.

But alas, I have managed to create a number of sub queries and

see comment in-line below...

HI Jeff and thanks for your help...

Unfortunately I am unable to send any screenshots or table definitions due
to sensitivity of the data.

What I can say, its with respect to engineernig and the 4 digit prefix of
the text value denotes the equipment.

This sounds like you have a field that contains more than 4 characters, the
first 4 of which denotes one fact ("the equipment"), and the remainder which
denotes some other fact -- if so, this is counter to good database design,
which calls for "one fact, one field".

Quite often the equipment has the same maintenance and therefore, its very
easy to change the four digit prefix in the text field and then use the new
code as a unique identifier for loading into an Ingres based DBMS.

"easy" and "good idea" don't necessarily work together. Are you saying that
you have multiple pieces of equipment that are all the same model, so that
the maintenance required is the same?

This happens quite regularly, hence creating a script that will use the
tablename and modify the four digit prefix on the fly...

I still don't understand whether you are talking about 45 .mdb files
(databases) or 45 tables (in one .mdb/database). What does "tablename" have
to do with this? Are you using different tables to store data about
different equipment?

i.e. I have a text field (32 characters) which can be anywhere from 6 to 32
characters. e.g. 1234-X or 1234-XY01A010201456 etc and each of these text
values is a unique ID for that piece of equipment.

Problem is, 1234 = MACHINE 1, but 4321 = MACHINE2. Exact same machine, but
the Unique ID has a different prefix to isolate the machines.

So you're saying that "1234" and "4321" prefixes refer to the same type of
equipment, but different physical machines?

Does your database include a table with a field that categorizes the
equipment by "type of machine"?

I have a number of these machines all with different 4 digit unique ID's.

And again, what about "type of machine" category?

I want to be able to change the Unique ID code in each of the text fields in
all 45 databases, which I will then load into an Ingres DBMS Server.

"databases"? or "tables"?

Also, if the text field is truly a "Unique ID", then you probably shouldn't
be changing it. Do you have a different field that you can use to hold an
"ID" that you could, if necessary, change?

It sounds complex, but very simple and easier structure to work with. In the
past they have worked in excel one load at a time, and I want to be able to
run an sql script to modify the table on the fly or using Expression Builder
and link the indivdual tables from 45 databases into one query and then the
script will run across all occurences of the four digit prefix.

Does that make sense?

Sorry, perhaps I'm being dense, but I still don't get why "changing a Unique
ID" would help with your process. What will having a (new, changed) Unique
ID allow you to do that you can't do without it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Cheers....

Dusty

Dusty

I'm having some trouble visualizing what you are describing...

"45 databases that have corresponding fields..." sounds like you might be
describing 45 Access tables with identical fields.

"the values in those fields all start with a four digit number" might mean
that the fields contain four character text strings, or might mean there are
numbers that have been formatted to display 4 characters.

I don't understand what business need you are attempting to solve by trying
to "change the four digit number on a regular basis".

Could you post back a simple example of a couple tables' definition, and
perhaps an example of the data in those tables?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi,

I have 45 databases that have corresponding fields which all contain the
same prefix. That is, 2 fields in 30 or so databases and one field in the
rest, and the values in those fields all start with a four digit number. I
want to change the four digit number on a regualr basis with out having to
open the tables individually and doing a find/replace on the fields
individually.

Is there a way of doing this in Access? I have attempted to use forms,
joins
and also combining a number of update queries using the Expression Builder
and using the Replace ([fieldname],"XXXX","YYYY") option but this is very
time consuming and tedious, and I can only do a table at a time or the
duplicate fields names give an error. I would have to add the table names
every time and this would be very time consuming.

Any guru's have any tips of how to do this?

Thanks in advance!!

Dusty