Access Query table join question.

[Infiltrator64]

Hello all;

I need help modifying the code below so it only compares the last 3 digits of the login name field in the User table to the Code field in the Regions table when it does the outer join.

The login name field contains a variable length string for a user name that always ends in a 3 digit region code. The Code field only contains the 3 digit code name.


SELECT REGIONS.DESCRIPTION, USER.LOGIN_NAME
FROM USER LEFT JOIN REGIONS ON USER.LOGIN_NAME = REGIONS.CODE;


Thank you in advance of any help you can provide.

 

[Andrew Fergus]

Hi, I'm not sure that you can do this the way that you are trying to do it (someone please correct me if I am wrong). You can identify the last 3 characters of the login_name using something like =Right([login_name],3) in a query and then either use that in your region lookup query or store that value in your user table in a separate field (and link directly on that in the relationship screen). The code to automatically populate that field should be relatively simple if you want to follow the latter path.
HTH, Andrew.

 

[Norie]

Andrew

You can actually do this.

SELECT REGIONS.DESCRIPTION, USER.LOGIN_NAME
FROM USER LEFT JOIN REGIONS ON USER.LOGIN_NAME = Right(REGIONS.CODE.3);

Note that Access will probably not allow you to view this in the QBE view.

 

[Andrew Fergus]

Thanks for the tip Norie (that is going to help me out with another database!), I tried to do this previously by guessing the coding - I was using the '[' brackets around the field names without success. Shouldn't the final section be : Right(USER.LOGIN_NAME,3) = REGIONS.CODE?
Cheers, Andrew

 

[Norie]

Andrew

Your right - I got it the wrong way round.

By the way another way of dealing with this would be to create a query that extracts the last 3 digits and then using that for the join.