Two different formats of the same field data

[lp4799]

I have to work with two tables - Service Request Table and Audit Talble in order to get the data I need.

One of the fields in the Service Request Table contains a field named: Service_Request_No. The data in the Service_Request_No field shows the serv. req. no in the following format:
00000000000000000000000000000004491388

One of the fields in the Audit Table contains a field named: KEY_INFO. The data in the KEY_INFO field contains the same data as the Service_Request_No field, but the data is in the following format: 4491388 (in this format, the 32 leading zeros are not used).

Also, these two particular fields are the only fields that could join the tables because they are the only common fields. Also, these tables are linked tables.

If I want to run a query to search TN_NPA, TN_NXX and TN_LINE from the Service Request Table and the User_ID from the Audit Table, how would I do that and get the Srv_Req_No and KEY_INFO fields to match up?

Please help. Thanks.

 

[Pat Hartman]

Create the query using the QBE grid. Draw a join line between the two Service_Request_No fields. Then switch to SQL view. Use the CInt() function to convert the text Service_Request_No to an integer. Once you make this modification to the join, you will not be able to switch back to QBE view since QBE view does not support this.

By the way, this join must be processed locally so Jet will have to copy ALL rows from both tables to memory on the local PC to perform the operation. If the tables are large this could be quite time consuming. It would be far better if at all possible to change one of the source tables so the two fields have the same data type so they can be joined with a standard inner join.