Results 1 to 10 of 10

Thread: Combine/Join 2 tables and get an output in 3rd table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,065
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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 by Joe4; Oct 4th, 2019 at 10:12 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,065
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,065
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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 MID(A.[Cost Center],4,8)=B.[Cost Center]
    if "Cost Center" in table B is text, or
    Code:
    WHERE MID(A.[Cost Center],4,8)=LEFT(B.[Cost Center],8)
    if "Cost Center" in table B is numeric.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    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);

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,065
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Sorry, we need to remove the JOIN part too, and add the other table to the FROM clause, i.e.
    Code:
    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);
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    May 2018
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Joe,

    Sorry for the delayed response.

    Your suggestion worked well. Thanks for the help.

    Regards,

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,065
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •