How do I match first 16 characters?

[DevilDog1978]

I collect data from two different sources and I have no control over how it
is entered. I would like to match [310_MOCC]![MODEL] to [ISR]![MODEL_NUMBER].
The model in the 310_MOCC table is limited to 16 characters and the ISR table
as a limit of 50 so 310_MOCC will be the shorter model number. As an example:
310_MOCC model number- 8648BOPT1EA1E2
ISR model number- 8648BOPT1EA,1E2,1E5,1E6,H31
I want Access to recognize this as the same thing. Any ideas?

 

[Beetle]

What do you mean by match? Are you trying to create a
relationship on two table fields? Find matching values in
a query?

Without knowing more the only thing I could suggest right
now would be to use something like the following to extract
the first 16 characters from the longer field (removing the
comma in the process);

Left(Replace([MODEL_NUMBER], ",", ""), 16)

then match on the result.

 

[John W. Vinson]

I collect data from two different sources and I have no control over how it
is entered. I would like to match [310_MOCC]![MODEL] to [ISR]![MODEL_NUMBER].
The model in the 310_MOCC table is limited to 16 characters and the ISR table
as a limit of 50 so 310_MOCC will be the shorter model number. As an example:
310_MOCC model number- 8648BOPT1EA1E2
ISR model number- 8648BOPT1EA,1E2,1E5,1E6,H31
I want Access to recognize this as the same thing. Any ideas?

If you just want to join on the left 16 characters you could use

SELECT <whatever fields you want to see>
FROM [ISR] INNER JOIN [310_MOCC]
ON [ISR].[MODEL_NUMBER] LIKE [310_MOCC].[MODEL] & "*";


Whoever designed ISR to store this non-atomic field should be thwapped...

But these comma delimited values are alternative suffixes the above query
won't do that. You'll need a snarky VBA function to turn
8648BOPT1EA,1E2,1E5,1E6,H31 into all five possible combinations and try each.
Such a query will be VERY slow and inefficient, and certainly not be
updateable.

One possibility might be to create your own junction table - taking the ISR
model number from one record and generating five (in the example above)
records, one for each combination. This would have two fields - the
MODEL_NUMBER with the commas, and a 16-character MODEL; you could join ISR to
it by the one field, and 310_MOCC by the other. The code to generate this
table would require some thought and more knowledge of the actual contents of
the table than I have at the moment...

 

[DevilDog1978]

I already have a query that removes spaces and anything that is not
alphanumeric. Let me try to explain what I mean by matching. I get a report
310_MOCC of assets that are supposed to be in my custody. The ISR report
shows exactly what is in my custody. I want to run a query that compares the
first 16 characters of the model number, the CAGE code, and the SERIAL NUMBER
from the two tables. Assume there are only alphanumeric characters in the
model number field. The CAGE and SERIAL NUMBER fields will be exact match in
the two tables.

What do you mean by match? Are you trying to create a
relationship on two table fields? Find matching values in
a query?

Without knowing more the only thing I could suggest right
now would be to use something like the following to extract
the first 16 characters from the longer field (removing the
comma in the process);

Left(Replace([MODEL_NUMBER], ",", ""), 16)

then match on the result.
--
_________

Sean Bailey

I collect data from two different sources and I have no control over how it
is entered. I would like to match [310_MOCC]![MODEL] to [ISR]![MODEL_NUMBER].
The model in the 310_MOCC table is limited to 16 characters and the ISR table
as a limit of 50 so 310_MOCC will be the shorter model number. As an example:
310_MOCC model number- 8648BOPT1EA1E2
ISR model number- 8648BOPT1EA,1E2,1E5,1E6,H31
I want Access to recognize this as the same thing. Any ideas?

 

[DevilDog1978]

Would the & "*" portion be attached to the shorter of the two model numbers?

I collect data from two different sources and I have no control over how it
is entered. I would like to match [310_MOCC]![MODEL] to [ISR]![MODEL_NUMBER].
The model in the 310_MOCC table is limited to 16 characters and the ISR table
as a limit of 50 so 310_MOCC will be the shorter model number. As an example:
310_MOCC model number- 8648BOPT1EA1E2
ISR model number- 8648BOPT1EA,1E2,1E5,1E6,H31
I want Access to recognize this as the same thing. Any ideas?

If you just want to join on the left 16 characters you could use

SELECT <whatever fields you want to see>
FROM [ISR] INNER JOIN [310_MOCC]
ON [ISR].[MODEL_NUMBER] LIKE [310_MOCC].[MODEL] & "*";


Whoever designed ISR to store this non-atomic field should be thwapped...

But these comma delimited values are alternative suffixes the above query
won't do that. You'll need a snarky VBA function to turn
8648BOPT1EA,1E2,1E5,1E6,H31 into all five possible combinations and try each.
Such a query will be VERY slow and inefficient, and certainly not be
updateable.

One possibility might be to create your own junction table - taking the ISR
model number from one record and generating five (in the example above)
records, one for each combination. This would have two fields - the
MODEL_NUMBER with the commas, and a 16-character MODEL; you could join ISR to
it by the one field, and 310_MOCC by the other. The code to generate this
table would require some thought and more knowledge of the actual contents of
the table than I have at the moment...

 

[DevilDog1978]

So how would I do it here?

INSERT INTO 310_ISRmatch ( MODEL, FSCM, SERIAL, NOMEN, ECN, LOCATION,
HOLD_CODE, CALIBRATION_DUE_DATE, MASTER_LIST_KEY )
SELECT DISTINCTROW [310_MOCC].MODEL, [310_MOCC].FSCM, [310_MOCC].SERIAL,
[310_MOCC].NOMEN, [310_MOCC].ECN, ISR.LOCATION, ISR.HOLD_CODE,
ISR.CALIBRATION_DUE_DATE, ISR.MASTER_LIST_KEY
FROM 310_MOCC LEFT JOIN ISR ON ([310_MOCC].SERIAL = ISR.SERIAL_NUMBER) AND
([310_MOCC].FSCM = ISR.CAGE) AND ([310_MOCC].MODEL = ISR.MODEL_NUMBER)
WITH OWNERACCESS OPTION;

I collect data from two different sources and I have no control over how it
is entered. I would like to match [310_MOCC]![MODEL] to [ISR]![MODEL_NUMBER].
The model in the 310_MOCC table is limited to 16 characters and the ISR table
as a limit of 50 so 310_MOCC will be the shorter model number. As an example:
310_MOCC model number- 8648BOPT1EA1E2
ISR model number- 8648BOPT1EA,1E2,1E5,1E6,H31
I want Access to recognize this as the same thing. Any ideas?

If you just want to join on the left 16 characters you could use

SELECT <whatever fields you want to see>
FROM [ISR] INNER JOIN [310_MOCC]
ON [ISR].[MODEL_NUMBER] LIKE [310_MOCC].[MODEL] & "*";


Whoever designed ISR to store this non-atomic field should be thwapped...

But these comma delimited values are alternative suffixes the above query
won't do that. You'll need a snarky VBA function to turn
8648BOPT1EA,1E2,1E5,1E6,H31 into all five possible combinations and try each.
Such a query will be VERY slow and inefficient, and certainly not be
updateable.

One possibility might be to create your own junction table - taking the ISR
model number from one record and generating five (in the example above)
records, one for each combination. This would have two fields - the
MODEL_NUMBER with the commas, and a 16-character MODEL; you could join ISR to
it by the one field, and 310_MOCC by the other. The code to generate this
table would require some thought and more knowledge of the actual contents of
the table than I have at the moment...

 

[John W. Vinson]

Would the & "*" portion be attached to the shorter of the two model numbers?

On rereading the post, this suggestion will NOT work. It would only retrieve
the first model number.

Your longer model number violates the rules of normalization: it has multiple
values in one field (it's not atomic); it splits up the key into two pieces
(e.g. in model number "8648BOPT1EA,1E2,1E5,1E6,H31" the implication is that
"8648BOPT1E2" is a key, but that text string does not appear anywhere in the
record, it must be constructed.

As I said... whoever designed this crappy table should be thwacked... hard!

You will not be able to do this with a Query, as best as I can tell; you will
need some sort of VBA function to pick apart the composite model number and
generate the individual model numbers.

If the last three characters are in fact a "different field" (i.e. they can
vary independent of the first 8) then they should be stored in a different
field. I know, I know - you have no control over the data, but the malformed
data is the cause of your problem!

All I can suggest is constructing an intermediate table with two fields, as I
stated in my previous post. If you could explain more about the structure and
contents of the MODEL_NUMBER field I might be able to write a VBA subroutine
to construct this intermediate table - e.g. how long does the field get? How
many comma separated suffixes? Are they all three characters, or might they
vary?