Compare data from tables one has letters added to data

  • Thread starter Thread starter msnews.microsoft.com
  • Start date Start date
M

msnews.microsoft.com

I need to compare data from fields in two tables. The data is numeric how
ever in table one a letter can appear at the beginning of the data in some
of the records but not all. Table two is all numeric. I need to update
data in another field inn Table 2 based on the record matching a record from
Table 1 based on the numeric data only.

Example

Table 1 Table 2
A123456 = 123456
Z456890 = 456890

How can I match them in a query and ingnore the letter?


Jeff Byrd
 
Create a query that will include a calculated field to give you the "no
letter" number:

SELECT *, IIf(IsNumeric(Left([FieldName], 1)) = True, [FieldName],
Mid([FieldName], 2)) AS JoinField
FROM [Table 1];

Call this query qryFix.

Then use this query as the source table instead of Table 1:

SELECT qryFix.*, [Table 2].*
FROM qryFix INNER JOIN
[Table 2] ON qryFix.JoinField =
[Table 2].FieldName;
 
Back
Top