Left to a space?

[Doug A]

I have a field where the data has not been normalized but
requires to be analyzed.

sample data in the field:

big bob company
joe company 1
Joe company 2

the numbers are not in every record but represent
different units for each company. the problem is I need to
group based on the company not the unit. the number can be
3 digits.

so as per the example my groups would be Big Bob company
and Joe company


Any help would be appreciated. thanks in advanced Doug

 

[Tom Ellison]

Dear Doug:

The first step, it seems to me, is to propose a "rule" to be used to
test whether there is a 1-3 digit "unit" present, and to remove it.
Perhaps it is everything that follows "company" or perhaps it is
everything following the last space in the string if and only if it is
1 to 3 digits. You should them program for this rule and look at the
results in a query to see how well it worked, improving the rule as
you learn how well it applies.

Only someone who can see the data can begin to formulate what kind of
rule may work well.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts