Join doesn't see duplicates

[grnzbra]

I have two table with the same key field. However, in one table it is a text field while the other is a number.

I created a query from the table with the numeric key and converted it to text using the str() function. When the query is run, the values appear, left justified as if they are text.

However, when I create another query using the first query and the other table, I get no records out even though there are matching key field values in both tables (there are no leading zero problems).

What can I do to join these two tables?

 

[The_Doc_Man]

Change the format of the table that currently contains a text version of the field. Make it a numeric field (integer, long) of the same size as the numeric in the other table. Then your joins should work.

To do the actual conversion, take it easy on yourself. ADD a field of the right size and give it an arbitrary name. Then build an UPDATE query to convert the text value and store it in the new field. Then remove the text field and rename the temporary to its permanent name.

Remember also that to do a join, you get best results performance-wise if both fields have keys and for 1/many cases, have the 1-side be a unique key.
It TECHNICALLY doesn't have to be the PRIME key of the table. However, if it is not the prime key, it was a candidate key for being PRIME and you would suddenly have a case of a redundant prime candidate key. I forget offhand which obscure normalization rule that breaks but I'm sure it breaks one.