Join statement not working (1 Viewer)

Randy · Feb 16, 2018

I thought I had this correct, but it will not work

FROM TBLProvisionData LEFT JOIN TBLEmployeeMasterListing
WHERE TBLProvisionData.Left([UserDirectory],Instr([UserDirectory],"@")-1) = TBLEmployeeMasterListing.NETWORK_ID

so in TBLProvisionData my name is randyetheridge@unisource
in TBLEmployeeMasterListing it would be just randyetheridge

I need to join the tables and felt this statement was correct. what am I missing.

plog · Feb 16, 2018

1. Each discrete piece of data must be stored by itself. Since you want to use the username seperate from the entire email, then you should store the email in 2 fields--one for the domain, one for the user name.

2. You do not use a WHERE like that. A JOIN needs two keywords--the type of JOIN and the keyword 'ON'. Replace WHERE with ON.

3. TableName.FieldName is how you reference the field. You need to put the LEFT function around that whole thing, not just the field name.

isladogs · Feb 16, 2018

Adding to plog's comments, I suggest you show the entire sql including the SELECT section so we can comment on the entire string in case there are other issues as well

Randy · Feb 16, 2018

SELECT TBLProvisionData.application, TBLProvisionData.role, TBLProvisionData.UserDirectory, TBLProvisionData.inheritanceInformation, TBLEmployeeMasterListing.LAST_NAME, TBLEmployeeMasterListing.FIRST_NAME, TBLEmployeeMasterListing.SUPVSR_LAST_NAME, TBLEmployeeMasterListing.SUPVSR_FIRST_NAME, TBLEmployeeMasterListing.JOB_TITLE, TBLEmployeeMasterListing.EMPLOYMENT_STATUS
FROM TBLProvisionData LEFT JOIN TBLEmployeeMasterListing ON (TBLProvisionData.Left([UserDirectory],Instr([UserDirectory],"@")-1) = TBLEmployeeMasterListing.NETWORK_ID)

Randy · Feb 16, 2018

Ok, yes I thought about creating an update query that would put the name in its own field in TBLProvisionData

For some reason I thought I could do it in a SQL statement.

plog · Feb 16, 2018

See my #3 above.

Randy · Feb 16, 2018

ok missed the intent of that first time. got it now, let me try

Randy · Feb 16, 2018

Updated statement, still did not work. in the meantime I just created a new field in TblProvisionData and parsed out the UserID field and it works. For some reason I was thinking it would be easy in a SQL statement.

SELECT TBLProvisionData.application, TBLProvisionData.role, TBLProvisionData.inheritanceInformation, TBLProvisionData.NETWORK_ID, [First_Name] & " " & [LAST_NAME] AS UserName, [Supvsr_First_Name] & " " & [SUPVSR_LAST_NAME] AS SupvsrName, TBLEmployeeMasterListing.JOB_TITLE, IIf(Left([Term_Date],4)>1900,"T",[EMPLOYMENT_STATUS]) AS Status
FROM TBLProvisionData LEFT JOIN TBLEmployeeMasterListing ON Left([TBLProvisionData]![UserDirectory],InStr([TbLProvisionData]![UserDirectory],"@")-1)=[TBLProvisionData]![NETWORK_ID]

plog · Feb 16, 2018

Code:

LEFT JOIN TBLEmployeeMasterListing ON Left([TBLProvisionData]![UserDirectory],InStr([TbLProvisionData]![UserDirectory],"@")-1)=[TBLProvisionData]![NETWORK_ID]

This is not how JOINs work. After the ON keyword you tell the computer how your to tables JOIN--- field(s) from one table maps to field(s) from the other. Your ON includes fields from the same table--that's not a valid JOIN. In the ON you must specify fields from both TBLProvisionData and TBLEmployeeMasterListing. You must specif

Even though you might be getting results, your SQL is incorrect.

Randy · Feb 16, 2018

yes sorry I missed that, you are of course correct the last statement should be
[TBLEmployeeMasterListing]![Network_ID]

Randy · Feb 16, 2018

corrected, still does not work, but thanks that was a good catch

SELECT TBLProvisionData.application, TBLProvisionData.role, TBLProvisionData.inheritanceInformation, TBLProvisionData.NETWORK_ID, [First_Name] & " " & [LAST_NAME] AS UserName, [Supvsr_First_Name] & " " & [SUPVSR_LAST_NAME] AS SupvsrName, TBLEmployeeMasterListing.JOB_TITLE, IIf(Left([Term_Date],4)>1900,"T",[EMPLOYMENT_STATUS]) AS Status
FROM TBLProvisionData LEFT JOIN TBLEmployeeMasterListing ON Left([TBLProvisionData]![UserDirectory],InStr([TbLProvisionData]![UserDirectory],"@")-1)=[TBLEmployeeMasterListing]![NETWORK_ID]

plog · Feb 17, 2018

Define "doesn't work". Error message? No results? Unexpected results?

You might create a subquery on TblProvisionData and make a calculated field out of that expression. Then use that query in another and do a simple JOIN on the calculated field.

Mark_ · Feb 17, 2018

Randy,

For myself, I would build one query first that is based only off of one table but includes your expressions (such as

Code:

IIf(Left([Term_Date],4)>1900,"T",[EMPLOYMENT_STATUS]) AS Status

) just to make sure each piece works. After you get the first part working, then start adding in the joined pieces, without the expressions. Once that works, see if you can add in the next set.

For myself, I would use an expression within the first query to create the [Network_ID] you would be using to join rather than trying to get the expression to figure it out for you. That way you KNOW it works first.

MarkK · Feb 17, 2018

In addition to other good advice given, also note that the bang operator is not valid in SQL to refer to a field in a referenced table...

Code:

TBLProvisionData!UserDirectory

Use a dot...

Code:

TBLProvisionData.UserDirectory

hth
Mark

Join statement not working (1 Viewer)

Randy

Registered User.

plog

Banishment Pending

isladogs

MVP / VIP

Randy

Registered User.

Randy

Registered User.

plog

Banishment Pending

Randy

Registered User.

Randy

Registered User.

plog

Banishment Pending

Randy

Registered User.

Randy

Registered User.

plog

Banishment Pending

Mark_

Longboard on the internet

MarkK

bit cruncher

Similar threads

Users who are viewing this thread