Replace values in one table based on 2nd table

[ScrmingWhisprs]

Hi,

I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:

ID LABEL CODE
1 Ashaway ASH
2 Barrington BAR
3 Bristol BRI
4 Jesse Smith BUR
....

Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.

What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.

I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty. Any guidance or a kick in the right direction would be appreciated!

 

[llkhoutx]

Appropriately join the 2 table in a query updating the fields in table1 from the correct field in table2.

 

[ScrmingWhisprs]

The thing is, the tables are not related. tblConversion is merely a reference. Basically I would like a loop to start at the beginning of tblConversions, the first record under field Label is "Ashaway" and under field Code is "ASH", I would like to have a second loop find all records in tblSysItemLoc in the Location field that begins with "Ashaway" and replace the entire contents of the field with "ASH". Then move to the next record in tblConversion which is "Barrington" and replace all Location fields in tblSysItemLoc beginning with "Barrington" with the code "BAR", so on and so forth.

This database is kind of a unique situation, as it more of a tool for me calculating Loan Statistics for our Library Consortium. This step is to prep the records for me to do group calculations later in the process.

Thanks,
Mike

 

[jdraw]

The tables must be related if you are going to check the starting values in a field with values in the second table.

Post some sample data in a database.

 

[ScrmingWhisprs]

Here is the database.

 

[Galaxiom]

No loop required.
Are the new locations always the first three letters of the original location?

 

[ScrmingWhisprs]

No they're not. For example Cross' Mills is CHA and Newport is NPT.

 

[jdraw]

I have adjusted the records based on my understanding. Look at the procedure DoIT in module1.

I have used the tblConversion and tblSysItemLoc to create records in the other Table by replacing the Location value with the code value from tblConversion.
I added Prepped value True for all records.
I did not include the Total line.

Same database name.

Hope it's useful.

Also tblConversion and tblSysItemLoc have not been modified.

 

[ScrmingWhisprs]

Hi jdraw,

Thank you for that! The only problem is I need to keep the data in tblSysItemLoc and not transferred to the other table. Is there anyway to accomplish that?

The table tblItemLocation is for a similar, but separate file that I will import after, which will need the same find and replace function, but once I have it working for the first table I can adapt it for the second.

Thank you,
Mike

 

[jdraw]

The procedure works to put the data into a table. I selected the table you had that had no data. If that was incorrect, then

1) delete the data in the table where I added the data.
2) adjust the table and field names in the procedure to the names you need to be populated
3) then run the procedure again so that the data gets put in the correct table.

Also, I would NOT just change the data in the table --just in case there's a glitch-- you won't have lost your original data. I would make a copy of the table and work with that. When you're done, if all is well, you can decide to keep or delete that origiinal table.

 

[ScrmingWhisprs]

Hi jdraw,

Thank you so much for your insight! I got the primary function of what I'm trying to accomplish with this database, but I did run into one little problem. While I like the idea keeping the original table intact and changing the Location Label to the 3-Letter code while copying to the second table, I did find a minor issue with it. In some cases, there are duplicate records getting copied from table 1 to table 2.

The tblConversion, which is basically a lookup for a find (field LABEL) and replace (field CODE), I have strategically placed some records in a certain order for Libraries with Branches. For example, here is a small segment of tblConversion:

ID LABEL CODE
8 Coventry Greene GRE
9 Coventry COV

Here is a small segment example of tblSysItemLoc where the SI_LOCATION field needs to be converted to the 3-Letter code above. When the

18 Coventry Childrens
34 Coventry Movies
86 Coventry Adult Non-Fiction
132 Coventry New Books
200 Coventry Audio Book
222 Coventry Young Adult
278 Coventry Children's New Books
317 Coventry CD
384 Coventry Large Print
477 Coventry Greene New Books <-----
571 Coventry Greene Adult Fiction <-----
606 Coventry Literacy/ESL
660 Coventry Ask at circ desk
740 Coventry Greene Young Adult <----
901 Coventry Children's New Books


The lines above with the arrow (<----) are getting duplicated because on one pass through the conversion table, it sees those labels beginning with "Coventry Greene", copies and converts the location field to "GRE", then on another pass, it sees all of the above (including Coventry Greene) as beginning with "Coventry", copies and converts the location to "COV". Therefore, I am getting duplicates. I would like to keep the data in the same table. Is this possible?

Thanks,
Mike