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

[sunilbsrv2k]

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]

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?

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).

 

[sunilbsrv2k]

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]

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]

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]

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:

WHERE [COLOR=#333333]MID(A.[Cost Center],4,8)=B.[Cost Center][/COLOR]

if "Cost Center" in table B is text, or

WHERE [COLOR=#333333]MID(A.[Cost Center],4,8)=LEFT(B.[Cost Center],8)[/COLOR]

if "Cost Center" in table B is numeric.

 

[sunilbsrv2k]

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:

[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]

Sorry, we need to remove the JOIN part too, and add the other table to the FROM clause, i.e.

[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]

Hi Joe,

Sorry for the delayed response.

Your suggestion worked well. Thanks for the help.

Regards,

 

[Joe4]

You are welcome.