Joining/linking two fields

[Access_Novice]

In query design view, table 1 has a field called VIPno (27 alpha characters). Table 2 has a field called ChargeNo (30 alpha characters).

In Table 2, the first 27 digits are the same as the VIPno in table one. Therefore, I need to use the LEFT function to extract the first 27 characters in ChargeNo from table 2, then join them.

So in my design grid, I have VIPno in there. For the second field, I have VIPcode: Left([ChargeNo],27). Then in the table area I simply drag and drop between the two fields I want to join together.



When I go to view my results, it is blank. Do you have to drag and establish the links before entering the LEFT function or vice versa, or does that not matter. As far as I can tell it does not matter.

I suspect that there are some inherent restrictions on joining a field to another field that has a function (e.g. LEFT) acting upon it.

[June7]

I just tested joining tables on fields that are both Calculated type using Left() function.

You can do this in table Calculated field.

Or build a query that extracts the value then join that query to the other table. This query doesn't have to be saved query object, can be nested with an alias name, typed into SQL View.

SELECT table1.*, Q1.* FROM table1 INNER JOIN (SELECT *, Left([ChargeNo],27) AS VIP FROM table2) AS Q1 ON table1.VIP = Q1.VIP;

[Access_Novice]

I just tested joining tables on fields that are both Calculated type using Left() function.

You can do this in table Calculated field.

Or build a query that extracts the value then join that query to the other table. This query doesn't have to be saved query object, can be nested with an alias name, typed into SQL View.

SELECT table1.*, Q1.* FROM table1 INNER JOIN (SELECT *, Left([ChargeNo],27) AS VIP FROM table2) AS Q1 ON table1.VIP = Q1.VIP;

I went with your suggestion about building another query and that worked. Thank you.

What do you mean by table calculated field? Do you mean having the 30 digit alphanumeric in one column, and then in the next column set up a calculated field with the LEFT function to extract the first 27 characters?

[June7]

Yes, Calculated type field in table is an option.

How is the 30 character value input into table? Is this imported data?

[Access_Novice]

Yes, Calculated type field in table is an option.

How is the 30 character value input into table? Is this imported data?

Yes it is imported. Why might that matter? Does it affect the use of a calculated field?

[June7]

No, does not. Was just wondering why it is not imported as the 27-character value you need. The remaining 3 characters could be in another field.