• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

Combine/Join 2 tables and get an output in 3rd table

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
hi all,

I have 2 different tables in Excel; I have managed to get them into Access database in Table format.

But I have a task to join these 2 table and get a new table.

I have written the query, but its not working; while executing I get the error that its too complex....

Request your help. Please find below the query I have written:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]SELECT ([First Name]+' '+[Last Name]) AS [Employee Name], [Person Number], [Work Email], [Termination Date], [Last Day Worked], [Legal Employer Hire Date], [Termination Action], [Termination Reason], [Assignment Number], [Primary Assignment Flag], [Assignment Status], [Assignment Category], [Person Type], [Business Unit], [Department], [Job Code], [Job], [Local Job Title], A.[Location], A.[Cost Center], A.[Cost Center Description ], [Grade], A.[Manager], [Manager Person Number], [Manager Email], [Manager Type], MID(A.[Cost Center],4,8) AS [Cost Center Number], B.[SBU Name], B.Manager AS [SBU Leader], B.LOB, B.Location AS Site
FROM AKS_DP_Headcount_Master_DLY AS A LEFT JOIN AKS_DP_Cost_Center_Master AS B ON A.[Cost Center Number]=B.[Cost Center];

Thanks in advance.

Regards
sunil[/FONT]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,136
Office Version
365
Platform
Windows
How big are these tables (how many record each)?
What is the relationship between these two tables (One-to-one, one-to-many, many-to-many)?

Are there any blanks or errors on the two fields you are joining ("Cost Center Number" and "Cost Center")?
Are these two joining fields both the same Data Type (i.e. both Text or both Numeric)?

I find that the best way to debug issues like this is to start small, and work your way up, i.e. does the following work?
Code:
SELECT A.[Cost Center Number] 
FROM AKS_DP_Headcount_Master_DLY AS A 
INNER JOIN AKS_DP_Cost_Center_Master AS B 
ON A.[Cost Center Number]=B.[Cost Center];
If so, then change the join type of LEFT JOIN, and see if that works.
If it does, try adding your other fields one-by-one, until you encounter the error (and then you will know where to focus your attention).

I would also recommend prefacing EVERY field in your SELECT clause with the alias that it is coming from (A or B).
 
Last edited:

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi Joe
Thanks for your response.

I tried applying table names A or B to the respective fields; it worked...

However, not sure why it popped up asking me to enter a Parameter for A.Cost Center Description and again for A. Cost Center Number. The values entered reflected in the entire column...

Further, this query gave results with columns from B table were completely blank..

Could you please advise...
Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,136
Office Version
365
Platform
Windows
However, not sure why it popped up asking me to enter a Parameter for A.Cost Center Description and again for A. Cost Center Number. The values entered reflected in the entire column...
If it asks you for parameters, then it means that you have a typo. It means it cannot find a field by that name. So either you:
1. Included a field that does not exist
2. Made a type
3. Attributed the field to the wrong table (A or B)

Further, this query gave results with columns from B table were completely blank..
You need to decide what kind of join that you want.
- An Inner Join will only return records where you having matching records in both tables
- A Left Outer Join will return ALL records from Table A, and all the matches from Table B. So if there are some records in Table A with no matching value in Table B, any Table B fields for those record will show as Null/Empty/Blank in your query. You can use the NZ function if you like to return so default value, in those spaces, if you like.

Also make sure that the fields that you are joining on do not have any errors or blanks in them in the underlying tables.
 

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi Joe,

Thanks for your advice.

I checked the query again and found that there is a mismatch in the type of data in the field named "Cost Center".

The column in table A contains IN-12345678-101 but Table B contains 12345678.

If you could look at my query above, I am extracting 12345678 as well...

But not sure how to use it in Joining A and B

Could you please help.

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,136
Office Version
365
Platform
Windows
Is "Cost Center" in Table B a Text/String or Numeric field?
If numeric, try this:
Remove the "ON" clause from the query, and instead add:
Code:
WHERE [COLOR=#333333]MID(A.[Cost Center],4,8)=B.[Cost Center][/COLOR]
if "Cost Center" in table B is text, or
Code:
WHERE [COLOR=#333333]MID(A.[Cost Center],4,8)=LEFT(B.[Cost Center],8)[/COLOR]
if "Cost Center" in table B is numeric.
 

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi Joe,

Thanks much.

I tried both the syntaxes. However, I am getting error as Syntax error in From Clause.

Below is the updated query giving me error:

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]SELECT (A.[First Name]+' '+A.[Last Name]) AS [Employee Name], A.[Person Number], A.[Work Email], A.[Termination Date], A.[Last Day Worked], A.[Legal Employer Hire Date], A.[Termination Action], A.[Termination Reason], A.[Assignment Number], A.[Primary Assignment Flag], A.[Assignment Status], A.[Assignment Category], A.[Person Type], A.[Business Unit], A.[Department], A.[Job Code], A.[Job], A.[Local Job Title], A.[Location], A.[Cost Center], A.[Cost Center Name], A.[Grade], A.[Manager], A.[Manager Person Number], A.[Manager Email], A.[Manager Type], MID(A.[Cost Center],4,8) AS CCnum, B.[SBU Name], B.Manager AS [SBU Leader], B.LOB, B.Location AS Site
FROM AKS_DP_Headcount_Master_DLY AS A LEFT JOIN AKS_DP_Cost_Center_Master AS B WHERE MID(A.[Cost Center],4,8)=LEFT(B.[Cost Center],8);[/FONT]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,136
Office Version
365
Platform
Windows
Sorry, we need to remove the JOIN part too, and add the other table to the FROM clause, i.e.
Code:
[FONT=Verdana]SELECT (A.[First Name]+' '+A.[Last Name]) AS [Employee Name], A.[Person Number], A.[Work Email], A.[Termination Date], A.[Last Day Worked], A.[Legal Employer Hire Date], A.[Termination Action], A.[Termination Reason], A.[Assignment Number], A.[Primary Assignment Flag], A.[Assignment Status], A.[Assignment Category], A.[Person Type], A.[Business Unit], A.[Department], A.[Job Code], A.[Job], A.[Local Job Title], A.[Location], A.[Cost Center], A.[Cost Center Name], A.[Grade], A.[Manager], A.[Manager Person Number], A.[Manager Email], A.[Manager Type], MID(A.[Cost Center],4,8) AS CCnum, B.[SBU Name], B.Manager AS [SBU Leader], B.LOB, B.Location AS Site
FROM AKS_DP_Headcount_Master_DLY AS A, AKS_DP_Cost_Center_Master AS B 
WHERE MID(A.[Cost Center],4,8)=LEFT(B.[Cost Center],8);[/FONT]
 

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
58
Hi Joe,

Sorry for the delayed response.

Your suggestion worked well. Thanks for the help.

Regards,
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,136
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,077,965
Messages
5,337,441
Members
399,147
Latest member
Raviteja KOTHA

Some videos you may like

This Week's Hot Topics

Top