Replace statment

[jagrok]

Dear



I have a table GFW.
This tables has 5 columns,

Uniq_id, name_of_company, serial, model, date_of_creation

In the table name_of_company i have company which equal to many names

Company_A_nr1
Company_A_nr3
Company_A_nr4

And many others companies

Company_B.... Company_Z

This Companies has uniqal ID as well.

I was trying to use expression with replace function to give new one names for these Companies

Company_A_nr1 replace with Company_A
Company_A_nr3 replace with Company_A

For one name its easy

Replace([name_of_company];"Company_A_nr1";"Company_A")

But i dont know how to realize for many entries with diferent names.

In that same time i want to replace their uniq_id with one

Do you have any suggestion how to realize that.

[JoeM]

The following formula will drop everything after the last underscore:

Left([name_of_company],InStrRev([name_of_company],"_")-1)

[jagrok]

Ok.

Simple i do not expect that we can drop.
And giving you example, i gave you not at all data.

What about if it`s on that way ?

Company_A nr1
Company A_nr3
Company_A_nr4
Company A.nr43

And how to replace the uniq ID signed to this companies A ?

[JoeM]

If you can come up with a "rule" we can use to always determine what needs to be dropped off, we can probably provide a formula to do it. But you need to determine what that rule is.
We first thought it would be everything after the last underscore. But now you are saying that is not always the case.
Does the part we want to drop always start with "nr"?

It may be that you have a combination of rules, you just need to let us know what they all are.

[jagrok]

Ok

Here is example:

Uniq_id; name_of_company
1234 Company A a.b.c. City - General
1835 Company A a.b.c.-City 1
1137 Company A a.b.c.-City 2
1638 Company A a.b.c.-City 3
1339 Company A a.b.c.

So as i suppose it could be ok with command


Left([name_of_company],InStrRev([name_of_company],".")-1)

But how to replace UNIQ_ID ?

[jagrok]

Ok.

Working but only for this Company A

If i have other entries like

Company B
Company C-W-M
Company C.W.M

And i dont want to replace them ?

[JoeM]

Before you can program anything, you have to explicitly (in detail) define a set of rules that will work for every instance.
When I say define the rules, I mean just in English. Something like:
- When situation A occurs, do this...
- When situation B occurs, do this...
- When situation C occurs, do this...

And each situation needs to be defined so that each record can only fall into one of those situations.
So what we really need you to do is come up with all the rules (not examples, but the actual rules - only you know what all your data looks like).

If you are not able to do that because there are too many variations to define any clear-cut rules, then you are probably not going to be able to program anything to do it for you automatically. In which case, you will probably need to manually update them.

One option may be to dump all the records out to Excel (Unique ID and Company_Name) where you can sort/group the records as you see fit, and change all the values you want, then re-import to Access (you could import to another table and use an Update Query to update your current table).

But how to replace UNIQ_ID ?

I am not sure why you would want to touch that. If it is a Autonumber field, you will not have the ability to do anything to it anyway.

[jagrok]

Before you can program anything, you have to explicitly (in detail) define a set of rules that will work for every instance.
When I say define the rules, I mean just in English. Something like:
- When situation A occurs, do this...
- When situation B occurs, do this...
- When situation C occurs, do this...

And each situation need to be defined so that each record can only fall into one of those situations.
So what we really need you to do is come up with all the rules (not example, but the actual rules - only you know what all your data looks like).

If you are not able to do that because there are too many variations to define any clear-cut rules, then you are probably not going to be able to program anything to do it for you automatically. In which case, you will probably need to manually update them.

One option may be to dump all the records out to Excel (Unique ID and Company_Name) where you can sort/group the records as see fit, and change all the values you want, then re-import to Access (you could import to another table and use an Update Query to update your current table).


I am not sure why you would want to touch that. If it is a Autonumber field, you will not have the ability to do anything to it anyway.

Dear

I appreciate your help, unfortunately i`m not familiar with programming and some times it's really hard to think as you guys.
However, next and i hope my last try.

Lets realize that we have table with 2 columns, named uniq_id and name_of_company

Uniq_id; name_of_company
1234 Company A a.b.c. City - General
3835 Company A a.b.c.-City 1
1137 Company A a.b.c.-City 2
1638 Company A a.b.c.-City 3
1339 Company A a.b.c.
1437 Company B
1834 Company C-W-M
2345 Company C.W.M

I want to create table like this

Uniq_id; name_of_company; new_names

1234 Company A a.b.c. City - General Company A a.b.c.
3835 Company A a.b.c.-City 1 Company A a.b.c.
1137 Company A a.b.c.-City 2 Company A a.b.c.
1638 Company A a.b.c.-City 3 Company A a.b.c.
1339 Company A a.b.c. Company A a.b.c.
1437 Company B Company B
1834 Company C-W-M Company C-W-M
2345 Company C.W.M Company C.W.M


Additionally i want to replace uniq_id which is unique for me, for access this only column with data


Uniq_id; name_of_company; new_names; new_uniq_id


1234 Company A a.b.c. City - General Company A a.b.c. 1339
3835 Company A a.b.c.-City 1 Company A a.b.c. 1339
1137 Company A a.b.c.-City 2 Company A a.b.c. 1339
1638 Company A a.b.c.-City 3 Company A a.b.c. 1339
1339 Company A a.b.c. Company A a.b.c. 1339
1437 Company B Company B 1437
1834 Company C-W-M Company C-W-M 1834
2345 Company C.W.M Company C.W.M 2345

[June7]

That is not a set of rules to use for programming, just more dummy examples which may or may not be all-encompassing.

Rules could be like:

Remove "City" and everything following
Remove "-City" and everything following
Change hyphen (-) to period (.)

However, my guess is "City" is not really the text in the data, but this is just a generic example and the text "City" is just a placeholder for actual city names. In which case the data is too varied for a well-defined and limited set of rules.

Consistency is critical in string manipulation. Examine your data - how many rules can you define? Basically, how would you edit a value to get the desired output? If every value requires its own rule, then this certainly cannot be accomplished with programming.

An example of a rule that could apply to all values (not your data, just some kind of data): Retrieve the first 15 characters

[JoeM]

unfortunately i`m not familiar with programming and some times it's really hard to think as you guys.

As I have mentioned before, we are NOT asking you to come up with any programming here, just the "rules" in plain English (not "techno-speak").
June gave an example of what a rule may look like.
Another rule may be like your first example, "If the entry contains any underscores, remove anything after the last underscore".

The problem with working with examples is that we may come up with a formula that works for those particular examples, and then you may come back and say, well that worked for SOME records, but I have other records that look like this... And we just keep going back-and-forth.

So what we really need you to do is to carefully analyze all your data, and see how many "different situations" there are, that will require "different rules". Until you have done that, there is really nothing that we can program for you that will work (since we don't know what the rules are that we need to follow).

[jagrok]

Sorry i marked in this posts that i`m not familliar with programming.
If i, then i will do it by my self. Unfortunatly not.
Additionally my english is not well, and this is 2nd time when i have a chance to read the similliar post from June7, who always saying for me that kind of "rule".
I cant imagine how to do that, so i use examples, which give chance for better person then me, thinking how it should work.
I hope you can try to understand me.

[June7]

We understand the issue. What we are stumbling over is getting you to understand it. Just might be too much language barrier.

[JoeM]

Sorry i marked in this posts that i`m not familliar with programming.

Who's asking you to do any programming? June and I have said time and again we aren't asking for any.
We gave you examples of what kind of "rules" we are talking about:

- Retrieve the first 15 characters
- If the entry contains any underscores, remove anything after the last underscore

"Rules" are just really explanations of what to do in different scenarios or situations. We aren't asking you to program them, just tell us what those situations are.
You already mentioned two, the one with underscores and the one with periods. What other ones might be out there?

If you like working with examples instead, what you would need to do is give us an example of all the different possible scenarios, and explain what you want to return in each situation.

For example,

Example 1: "Company_A_nr1"
Rule 1: Remove everything after last underscore

Example 2: "Company A a.b.c.-City 3"
Rule 2: Remove everything after last period.

But here is the tricky part. If there are "exceptions", it could make things very difficult.
For example, if you have an entry like this: "Company_A nr1", rule #1 would make it "Company", not "Company_A" since there is no underscore after the "A". If you have a lot of exceptions, your rules might get so complex that there really isn't a good way to do it automatically.

It might make it easier for you to think of it this way.
If you gave this for someone to do manually who had no knowledge of your business or this data, and asked them to drop off the ending, how exactly would you explain it to them, knowing all the variations you have in your data?
If you do not think there is any way you could explain it to them so that they know exactly what to do for each and every record you have, there is no way you can expect them to do all of them correctly.
Likewise, if you are unable to let us know exactly what to do in each "variation" you have, there is no way we can come up with something that would do what you want (because you are unable to tell us exactly what you want).

[jagrok]

So maybe we will start again:
I have some companies with their uniq names and ids, one of these companies has many sub-companies, which the first name of this companies is always the same.


Simple view for table GFW:


uniq_id; name_of_company
37500152 White Video ltd
26600328 Brown Serwis Sp. z o.o.
26800130 White Goods GmbH
26900597 Yellow Partner-Service
26101704 RED,a.b.c.
26300126 ORANGE a.b.c.
26500315 Black Bobby - company Bobby
26200321 GREEN PL a.b.c.
26300412 GREEN PL a.b.c. like - partner
26400275 GREEN PL a.b.c.- Berlin 3
26100276 GREEN PL a.b.c.- Berlin 4
26201020 GREEN PL a.b.c.- Berlin 8
26400052 Purple mountain center


Here what i want to do:


all fields which starting with first letters:


GREEN PL a.b.c. should be replaced by GREEN PL a.b.c.


additionally all uniq id equel to GREEN PL a.b.c. should be replaced by new entry id


in other cases leave without changes.

[June7]

What a coincidence! The rule is: Retrieve first 15 characters if value is like "GREEN PL a.b.c.*"

A query could be:

SELECT *, IIf([name_of_company] LIKE "GREEN PL a.b.c.*", Left([name_of_company],15), [name_of_company]) AS NewName FROM GFW;

Do you want to actually change the original raw data?

Now where does the new entry id come from? What do you want to use and what field do you want to update with this value?