Left Join with 3 tables?

[andream02]

I have three (3) tables in my query. The first table (Table 1) has customer data such as name but does not have the account id. Please note that the customer name in Table 1 does not match our customer database so some records require manual lookup. The second table (Table 2) has name and account id from our database. Third table (Table 3) is the list of any previous manual lookups of the account name.

What I'm hoping to do is create a query that states, if you can not find the account name that is in Table 1 when joined to Table 2, then return the value from Table 3.

I'm doing a left join from Table 1 to Table 2 on company Name and another Left Join from Table 1 to Table 3 on Company name. The issue when I do this is that I end up creating duplicates. I

Below is my SQL. I must be doing something wrong, but not sure what exactly. Any and all help is much appreciated.

SELECT DISTINCT Registrations.[User ID] AS RegID, Registrations.[Registration Date], Registrations.[First Name], Registrations.[Last Name], Registrations.Email, Registrations.[Company Website], Registrations.[Company Name], Registrations.[Job Title], Registrations.[REG: Job Title Category], Registrations.[REG: Primary Role2], Registrations.[REG: Market1], Registrations.City, Registrations.State, Registrations.Zip, Registrations.Country, Registrations.Phone, Registrations.[Alternate Phone], Registrations.[User ID], Registrations.[REG: Registration Type], Accounts.[Account ID], Accounts.[Account ID 18], Accounts.[PS Company Id], IIf(IsNull([Accounts].[Account Name]),1,0) AS Not_Found, IIf([Not_Found]=1,[Manual Account Lookup].[Account Name],[Accounts].[Account Name]) AS FoundAccountName, Registrations.[Current Packages (comma delimited)]
FROM (Registrations LEFT JOIN [Manual Account Lookup] ON Registrations.[Company Name] = [Manual Account Lookup].Company) LEFT JOIN Accounts ON Registrations.[Company Name] = Accounts.[Account Name];

 

[Micron]

Duplicate what? Rows or field values? If the latter, try the DISTINCT ROW predicate. I think this
IIf(IsNull([Accounts].[Account Name]),1,0) AS Not_Found, IIf([Not_Found]=1,[Manual Account Lookup].[Account Name]
means you expect the 2nd part to decide the outcome based on the first, which technically really doesn't exist until the record is created. I'm not an Access architecture guru, but I'd say that's dicey at best. Maybe it'll work, but not sure how reliable it will be.

You really need to affect some degree of normalization otherwise you are only going to face barriers for practcally everything you try to do. I'm thinking the first table ought to be linked to the db, the second be joined to that one on name=name and have an id field that links to the manual values table id. That way, either the 2nd table feeds a query its values (or the foreign ones where the names are the same as they are in the first) or the manual table values (based on the join between id's) . A simple select query should then be able to do the job. I may not have pinpointed the solution; I doubt I fully understand the schema based on your short description, but perhaps you can see what I'm driving at. It ought to be doable.

 

[tiedied61]

To start with, your table structure is completely out of whack... Let me explain

Look carefully at your SQL. Your Registrations HAS a Company Name, so use it... if it is Null then there is nothing join on the other 2 tables, so you will always get a Null returned.

You will need to get the Account ID into your registrations and Manual Account Lookup table ... you already have it in Accounts table. Once this is done, you can then do the joins on the Account ID and your IIF logical field will work just fine. Until this is done, however, nothing you are trying to do will get you what you want.

I am not sure this was helpful, but more of an explanation of why you cannot get the desired results.

Art
Certified MS Access Developer
25+ years experience

 

[Micron]

I agree with tiedied61 in that the data structure is bad, and that's why it can't be presented as desired without a lot of work-around.

However, after 700+ posts here in 9 months (plus what I do at Access Forums.net) sometimes I choose not to expound upon the subject as it gets tiring to do so . Often when I do, the answer is that the data comes from some other source like a MySql database or Excel workbook and the organization of it can't be changed. Then one ends up giving the answer anyway. It's good that someone else chooses to fill in the facts that haven't been addressed, and that's the beauty of a well functioning forum.

 

[xenou]

One possible solution (not tested - probably suspicious):

SELECT T3.*, T2.[Account Name]

FROM
	(
		SELECT 
			IIF(Not IsNull(M.Company), M.Company, R.[Company Name]) AS CompanyNameWLookup
		FROM 
			Registrations R
		LEFT JOIN 
			[Manual Account Lookup] M 
			ON R.[Company Name] = M.Company
	) AS T1

LEFT JOIN 

	Accounts AS T2
	ON T1.[CompanyNameWLookup] = T2.[Account Name]

LEFT JOIN
	
	Registrations T3
	ON T2.[Account Name] = T3.[Company Name]

The idea is to first get a single list of all the company names using the corrected ones from the manual lookup table if it is there. Then use *that* as the basis for the joins that follow. Anything that ends up Null afterwards means there is new company names that need to be added to the manual lookup table. (Be careful to index this table so that it does not allow duplicates on those values being entered as lookups as that would be a hard to find source of duplicates).


Note: Perhaps post some sample data so we can test on something realistic for your dataset and tables. You also don't really need all the fields for the purposes of creating the query - just the important ones that matter for the joins (simplifying what we are looking at).