Relating similar fields???

[smoothlarryhughes]

Is it possible to relate tables where the fields are slightly different? For example table 1 has a reference column with AB1300033370A100, table 2 has AB13000333700000, and table 3 has AB1300033370A1A0...I want to relate based on the first 12 digits in each reference, but I will want to be able to see the full reference on my queries.



Anyone know the best way to go about doing this?

[pbaldy]

Should be possible. Create the query with a regular join. In SQL view, add the Left() function around the field names in the join.

[smoothlarryhughes]

Should be possible. Create the query with a regular join. In SQL view, add the Left() function around the field names in the join.

something like this?
SELECT FROM tbl_tf INNER JOIN tbl_tf_sec_processing ON LEFT(tbl_tf.SENDERREF,12) = LEFT(tbl_tf_sec_processing.REFERENCE,12);

this gives me an error. incorrect punctuation.

[pbaldy]

You don't have any fields selected.

[smoothlarryhughes]

You don't have any fields selected.

THANKS...this seems to work, but the query takes a good 2 - 3 minutes to run and then freezing up the computer pretty much indefinitely....any ideas on a better way this can be done?

[orange]

How many records in your tables? You are performing the left function for every record which could account for some of the time.

Is this a one time thing or recurring?

[pbaldy]

Yeah, it has to apply the function to every record in both tables, and probably can't use indexes, so it won't be fast. This might be a situation where saving that value in the tables would make sense.

[smoothlarryhughes]

Is there any easy way to append the new data when I am importing it into the table? I would like to keep the column with the full references and just have another column added to cut off the last 4 digits.

[orange]

Yes you can do that. For each table involved:
Create the new field, populate it with an update query. I would index the new field.
Then create a query where you join on the new fields.

A little time in the set up, but it should run very fast.

Good luck.

[pbaldy]

You can probably do it during import too, depending on your method.

[smoothlarryhughes]

Do you have any info on how to do it on import? Could I do it via a macro?

Thanks for your help