Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Sow all rows & columns of data between 2 tables

    I am trying to show all rows of data between 2 tables and merge what is common. One table has the header "SHIPPING LOCATION" and the second table has a header "NAME". These two fields are my unique lookup between the two.



    My database shows the "SHIPPING LOCATION". If the data in these two columns do not agree, it is leaving the SHIPPING LOCATION blank in the query but it populates the other data. How do I get the name, along with the rest of the data I need on each row, to appear in my even if it only appears on one table.

    Also, in my attempts to make this work, I broke the datatbase (second time today) and am now getting a syntax error "Query Expression SELECT DISTINCT Ramco_Modified.[Shipping Location].

    Please be gentle in your responses, Access not strong point for me! PLEASE HELP!

    SELECT DISTINCT Ramco_Modified.[Shipping Location], Protos_Modified.Name, Ramco_Modified.[Contract #], Ramco_Modified.Day, Ramco_Modified.Date, Ramco_Modified.[Shift End Date], Ramco_Modified.[Employee Name], Protos_Modified.Start, Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1], Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In], Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs], Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day], Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified LEFT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name
    UNION SELECT DISTINCTROW SELECT DISTINCT Ramco_Modified.[Shipping Location], Protos_Modified.Name, Ramco_Modified.[Contract #], Ramco_Modified.Day, Ramco_Modified.Date, Ramco_Modified.[Shift End Date], Ramco_Modified.[Employee Name], Protos_Modified.Start, Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1], Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In], Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs], Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day], Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified RIGHT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name
    Where Ramco_Modified.[Shipping Location] is Null;

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,574
    this looks wrong to me
    UNION SELECT DISTINCTROW SELECT DISTINCT

    would expect

    UNION SELECT DISTINCTROW

    or perhaps

    UNION ALL SELECT

    Also looks like your first part of the query is missing a WHERE clause, perhaps

    Where Protos_Modified.Name is Null;

    suggest provide some example data and what you want to achieve from your example data as it is not clear to me from your description.

    General tip, don't include spaces or non alphanumeric characters in field names, it can cause misleading errors, even if surrounded by square brackets

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,953
    The first thing I would do is fix the object names. Object names should be only letters and numbers (exception is the underscore). NO spaces, punctuation or special characters.

    Code:
    SELECT DISTINCT Ramco_Modified.[Shipping Location],  Protos_Modified.Name, Ramco_Modified.[Contract #],  Ramco_Modified.Day,  Ramco_Modified.Date, Ramco_Modified.[Shift End Date],  Ramco_Modified.[Employee Name], Protos_Modified.Start,  Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1],  Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next  Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual  Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In],  Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs],  Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day],  Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified LEFT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name
    UNION SELECT DISTINCTROW SELECT DISTINCT Ramco_Modified.[Shipping  Location], Protos_Modified.Name, Ramco_Modified.[Contract #],  Ramco_Modified.Day, Ramco_Modified.Date, Ramco_Modified.[Shift End  Date], Ramco_Modified.[Employee Name], Protos_Modified.Start,  Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1],  Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next  Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual  Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In],  Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs],  Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day],  Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified RIGHT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name
    Where Ramco_Modified.[Shipping Location] is Null;
    Cannot have BOTH SELECT DISTINCTROW and SELECT DISTINCT. Pick one.....


    Code:
    Protos_Modified.Name, 
    Protos_Modified.Start, 
    Protos_Modified.End, 
    Ramco_Modified.Day, 
    Ramco_Modified.Date,
    These are reserved words in Access and shouldn't be used for object names.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I made the changes but the output is still not correct. I did remove the second WHERE statement because my output got smaller. I am having great difficulty finding a format acceptable for this forum to attach a sample , but basically, if it it appears on the Ramco.Modified table but not on the Protos.Modified table, it appears on my query but not all columns are populated even tho the information exists on the Ramco.Modified table.

    SELECT DISTINCT Ramco_Modified.[Shipping Location], Ramco_Modified.[Contract #], Ramco_Modified.Day, Ramco_Modified.Date, Ramco_Modified.[Shift End Date], Ramco_Modified.[Employee Name], Protos_Modified.Start, Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1], Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In], Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs], Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day], Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified LEFT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name


    UNION ALL SELECT Ramco_Modified.[Shipping Location], Ramco_Modified.[Contract #], Ramco_Modified.Day, Ramco_Modified.Date, Ramco_Modified.[Shift End Date], Ramco_Modified.[Employee Name], Protos_Modified.Start, Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1], Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In], Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs], Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day], Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified RIGHT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name
    Where Ramco_Modified.[Shipping Location] is Null;

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,574
    I am having great difficulty finding a format acceptable for this forum to attach a sample
    take a copy of your db, remove all unnecessary objects (sounds like all you need is 2 tables and the query), compact the db and zip it, go the the advanced editor and upload the zipped file

  6. #6
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Ok, I attached it. Hope this helps:

    1. Not all names from table "Protos_Modifiied appear in query (Missing ID #7). Only a portion of the data appears. I need the name column to appear in the Shipping location, as well as the the day, date, shift end. The contract # and Employee Name would not appear as this info is only found on the Ramco.Modified table.

    2. BLM-000154 appears 4 times (Showing as a dup in query) - even though there are only 2 with on each table. Both lines in each table show the exact same data.

    I appreciate your help on this!
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,466
    Have you considered using Power Query to bring your two tables into the PQ editor and then creating a full outer join

    v A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI BJ BK BL BM BN
    1 ID Company Attendance Tran # Contract # Shipping Location Day Date Employee Name Actual Time In Actual Time Out Lunch Hrs Effective Hrs Actual Hrs Bill Rate Customer Name Schedule # Shift End Date MINUTES TOTAL MINUTES Current Day MINUTES Next Day MINUTES Total Hours MINUTES Var F22 F23 F24 F25 F26 F27 F28 F29 12:00 AM Protos_Modified.ID Protos_Modified.Company Protos_Modified.Name Protos_Modified.Position Protos_Modified.Ramco Contract # Protos_Modified.Date Protos_Modified.Day Protos_Modified.Start Protos_Modified.End Protos_Modified.In 1 Protos_Modified.Out 1 Protos_Modified.Unpaid Break Hours Protos_Modified.Actual Hours Protos_Modified.Delta Hours Protos_Modified.Shift End Date Protos_Modified.MINUTES Current Day Protos_Modified.MINUTES Next Day Protos_Modified.MINUTES Total Hours Protos_Modified.MINUTES Hours Var Protos_Modified.ROUNDED Current Day Protos_Modified.ROUNDED Next Day Protos_Modified.ROUNDED With Lunch Protos_Modified.ROUNDED Total Hours Protos_Modified.ROUNDED Hours Var Protos_Modified.F24 Protos_Modified.F25 Protos_Modified.F26 Protos_Modified.F27 Protos_Modified.F28 Protos_Modified.12:00 AM Protos_Modified.F30 Protos_Modified.F31 Protos_Modified.F32 Protos_Modified.F33 Protos_Modified.F34
    2 1 RM PA-00000029915-20 R0054155 BLM-000154 Wednesday 9/9/2020 0:00 21741 9/10/2020 12:00 9/10/2020 19:00 0 7 7 17.25 PS WR0224405 9/9/2020 0:00 7 7 0.00 7 0 1 PT BLM-000154 UUG R0054155 9/9/2020 0:00 Wednesday 12:00 19:00 11:56 19:00 0 7 0 9/9/2020 0:00 7 0.00 7 0 7 0.00 7 7 0
    3 1 RM PA-00000029915-20 R0054155 BLM-000154 Wednesday 9/9/2020 0:00 21741 9/10/2020 12:00 9/10/2020 19:00 0 7 7 17.25 PS WR0224405 9/9/2020 0:00 7 7 0.00 7 0 2 PT BLM-000154 UUG R0054155 9/9/2020 0:00 Wednesday 12:00 19:00 12:01 18:05 0 6.07 -0.93 9/9/2020 0:00 6.07 0.00 6.07 0 6.07 0.00 6 6.07 0.07
    4 2 RM PA-00000029916-20 R0054155 BLM-000154 Wednesday 9/9/2020 0:00 24443 9/10/2020 12:00 9/10/2020 18:01 0 6 6 17.25 PS WR0224405 9/9/2020 0:00 6.016666667 6 0.00 6 -0.016666667 1 PT BLM-000154 UUG R0054155 9/9/2020 0:00 Wednesday 12:00 19:00 11:56 19:00 0 7 0 9/9/2020 0:00 7 0.00 7 0 7 0.00 7 7 0
    5 2 RM PA-00000029916-20 R0054155 BLM-000154 Wednesday 9/9/2020 0:00 24443 9/10/2020 12:00 9/10/2020 18:01 0 6 6 17.25 PS WR0224405 9/9/2020 0:00 6.016666667 6 0.00 6 -0.016666667 2 PT BLM-000154 UUG R0054155 9/9/2020 0:00 Wednesday 12:00 19:00 12:01 18:05 0 6.07 -0.93 9/9/2020 0:00 6.07 0.00 6.07 0 6.07 0.00 6 6.07 0.07
    6 3 RM NY-00000047751-20 R0047699 TJ1223 Wednesday 9/9/2020 0:00 24458 9/10/2020 9:53 9/10/2020 20:13 0 10.25 10.25 21 PS WR0222751 9/9/2020 0:00 10.33333333 10.25 0.00 10.25 -0.083333333 3 PT TJ1223 UUG R0047699 9/9/2020 0:00 Wednesday 10:00 20:00 09:51 20:13 0 10 0 9/9/2020 0:00 10 0.00 10 0 10 0.00 10.25 10 -0.25
    7 4 RM NY-00000043950-20 R0047696 TJ1325 Wednesday 9/9/2020 0:00 23845 9/10/2020 10:07 9/10/2020 20:00 0 10 10 21 PS WR0222555 9/9/2020 0:00 9.883333333 10 0.00 10 0.116666667 4 PT TJ1325 UUG R0047696 9/9/2020 0:00 Wednesday 10:00 20:00 10:04 19:59 0 9.92 -0.08 9/9/2020 0:00 9.92 0.00 9.92 0 9.92 0.00 10 9.92 -0.08
    8 5 RM NY-00000044500-20 R0047700 TJ1335 Wednesday 9/9/2020 0:00 23501 9/10/2020 15:56 9/10/2020 20:00 0 4 4 21 PS WR0222754 9/9/2020 0:00 4.05 4 0.00 4 -0.05 5 PT TJ1335 UUG R0047700 9/9/2020 0:00 Wednesday 16:00 20:00 15:57 20:16 0 4 0 9/9/2020 0:00 4 0.00 4 0 4 0.00 4.25 4 -0.25
    9 6 RM PA-00000028381-20 R0047698 TJ1507 Wednesday 9/9/2020 0:00 24449 9/10/2020 13:30 9/10/2020 20:09 0 6.75 6.75 16.37 PS WR0222552 9/9/2020 0:00 6.65 6.75 0.00 6.75 0.1 6 PT TJ1507 UUG R0047698 9/9/2020 0:00 Wednesday 13:30 19:30 13:30 20:02 0 6.53 0.53 9/9/2020 0:00 6.53 0.00 6.53 0 6.53 0.00 6.5 6.53 0.03
    10 7 RM PA-00000028867-20 R0048620 Ult1477 Wednesday 9/9/2020 0:00 24368 9/10/2020 10:00 9/10/2020 19:02 0 9 9 16.37 PS WR0222737 9/9/2020 0:00 9.033333333 9 0.00 9 -0.033333333
    11 8 RM NJ-00000011052-20 R0049606 West Gate Lumberton Wednesday 9/9/2020 0:00 23911 9/10/2020 6:45 9/10/2020 7:15 0 0.5 0.5 33.72 PS WR0222758 9/9/2020 0:00 0.5 0.5 0.00 0.5 0
    12 9 RM NJ-00000011054-20 R0049606 West Gate Lumberton Wednesday 9/9/2020 0:00 24008 9/10/2020 22:45 9/10/2020 23:16 0 0.5 0.5 33.72 PS WR0222758 9/9/2020 0:00 0.516666667 0.5 0.00 0.5 -0.016666667
    13 10 RM NJ-00000011054-20 R0049606 West Gate Lumberton Wednesday 9/9/2020 0:00 24008 1/0/1900 14:45 1/0/1900 15:15 0 0.5 0.5 33.72 PS WR0222758 9/9/2020 0:00 0.516666667 0.5 0.00 0.5 -0.016666667
    14 7 PT SB881 UUG R0049981 9/9/2020 0:00 Wednesday 15:00 20:00 15:01 20:02 0 4.98 0.02 9/9/2020 0:00 4.98 0.00 4.98 0 5 0.00 5 5 0
    Attached Files Attached Files

  8. #8
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I tried the Power Query - it didn't work correctly.

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,574
    The reason is your second part of the union query is reporting the null fields from the record that is not there

    e.g. you currently have

    UNION ALL SELECT Ramco_Modified.[Shipping Location], etc

    should be
    UNION ALL SELECT Protos_Modified.Name, etc

  10. #10
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Before I change this in Access - does below look correct?

    SELECT DISTINCT Ramco_Modified.[Shipping Location], Ramco_Modified.[Contract #], Ramco_Modified.Day, Ramco_Modified.Date, Ramco_Modified.[Shift End Date], Ramco_Modified.[Employee Name], Protos_Modified.Start, Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1], Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In], Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs], Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day], Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified LEFT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name


    UNION ALL SELECT Protos_Modified.Name], Ramco_Protos.[Contract #], Protos_Modified.Day, Protos_Modified.Date, Protos_Modified.[Shift End Date], Ramco_Modified.[Employee Name], Protos_Modified.Start, Protos_Modified.End, Protos_Modified.[In 1], Protos_Modified.[Out 1], Protos_Modified.[MINUTES Current Day], Protos_Modified.[MINUTES Next Day], Protos_Modified.[Unpaid Break Hours], Protos_Modified.[Actual Hours], Protos_Modified.[Delta Hours], Ramco_Modified.[Actual Time In], Ramco_Modified.[Actual Time Out], Ramco_Modified.[Lunch Hrs], Ramco_Modified.[Effective Hrs], Ramco_Modified.[MINUTES Current Day], Ramco_Modified.[MINUTES Next Day]
    FROM Ramco_Modified RIGHT JOIN Protos_Modified ON Ramco_Modified.[Shipping Location] = Protos_Modified.Name
    Where Ramco_Modified.[Shipping Location] is Null;

  11. #11
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,466
    I tried the Power Query - it didn't work correctly.
    That doesn't help me to help you. Want to show your Mcode for the sample. You can see my Mcode in the file that I attached. Does my presentation in post #7 look like you would expect. If not, what differences. Really need to understand what your output should look like.

  12. #12
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I am not understanding what you are asking?

    Quote Originally Posted by alansidman View Post
    That doesn't help me to help you. Want to show your Mcode for the sample. You can see my Mcode in the file that I attached. Does my presentation in post #7 look like you would expect. If not, what differences. Really need to understand what your output should look like.

  13. #13
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,574
    does below look correct?
    no - check your table names, you are referencing one called Ramco_Protos

    just a tip, copy your code from SELECT and paste into a new query - does it give the results required? Better when starting from scratch, create in a new query, then copy/paste the sql to your union query

  14. #14
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,466
    Ok, so here is my code (Mcode is the language that Excel uses to quantify the manual steps) that merges the two tables in Power Query. You said that PQ did not work for you. What did you do when you tried to merge the tables in PQ. It is a full outer join of the two tables.

    Code:
    let
        Source = Table.NestedJoin(Ramco_Modified, {"Contract #"}, Protos_Modified, {"Ramco Contract #"}, "Protos_Modified", JoinKind.FullOuter),
        #"Expanded Protos_Modified" = Table.ExpandTableColumn(Source, "Protos_Modified", {"ID", "Company", "Name", "Position", "Ramco Contract #", "Date", "Day", "Start", "End", "In 1", "Out 1", "Unpaid Break Hours", "Actual Hours", "Delta Hours", "Shift End Date", "MINUTES Current Day", "MINUTES Next Day", "MINUTES Total Hours", "MINUTES Hours Var", "ROUNDED Current Day", "ROUNDED Next Day", "ROUNDED With Lunch", "ROUNDED Total Hours", "ROUNDED Hours Var", "F24", "F25", "F26", "F27", "F28", "12:00 AM", "F30", "F31", "F32", "F33", "F34"}, {"Protos_Modified.ID", "Protos_Modified.Company", "Protos_Modified.Name", "Protos_Modified.Position", "Protos_Modified.Ramco Contract #", "Protos_Modified.Date", "Protos_Modified.Day", "Protos_Modified.Start", "Protos_Modified.End", "Protos_Modified.In 1", "Protos_Modified.Out 1", "Protos_Modified.Unpaid Break Hours", "Protos_Modified.Actual Hours", "Protos_Modified.Delta Hours", "Protos_Modified.Shift End Date", "Protos_Modified.MINUTES Current Day", "Protos_Modified.MINUTES Next Day", "Protos_Modified.MINUTES Total Hours", "Protos_Modified.MINUTES Hours Var", "Protos_Modified.ROUNDED Current Day", "Protos_Modified.ROUNDED Next Day", "Protos_Modified.ROUNDED With Lunch", "Protos_Modified.ROUNDED Total Hours", "Protos_Modified.ROUNDED Hours Var", "Protos_Modified.F24", "Protos_Modified.F25", "Protos_Modified.F26", "Protos_Modified.F27", "Protos_Modified.F28", "Protos_Modified.12:00 AM", "Protos_Modified.F30", "Protos_Modified.F31", "Protos_Modified.F32", "Protos_Modified.F33", "Protos_Modified.F34"})
    in
        #"Expanded Protos_Modified"

  15. #15
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Now I am completely lost...where are you saying this MCode should be placed? Excel? Access? In either case, I have no clue where either should be placed.

    Quote Originally Posted by alansidman View Post
    Ok, so here is my code (Mcode is the language that Excel uses to quantify the manual steps) that merges the two tables in Power Query. You said that PQ did not work for you. What did you do when you tried to merge the tables in PQ. It is a full outer join of the two tables.

    Code:
    let
        Source = Table.NestedJoin(Ramco_Modified, {"Contract #"}, Protos_Modified, {"Ramco Contract #"}, "Protos_Modified", JoinKind.FullOuter),
        #"Expanded Protos_Modified" = Table.ExpandTableColumn(Source, "Protos_Modified", {"ID", "Company", "Name", "Position", "Ramco Contract #", "Date", "Day", "Start", "End", "In 1", "Out 1", "Unpaid Break Hours", "Actual Hours", "Delta Hours", "Shift End Date", "MINUTES Current Day", "MINUTES Next Day", "MINUTES Total Hours", "MINUTES Hours Var", "ROUNDED Current Day", "ROUNDED Next Day", "ROUNDED With Lunch", "ROUNDED Total Hours", "ROUNDED Hours Var", "F24", "F25", "F26", "F27", "F28", "12:00 AM", "F30", "F31", "F32", "F33", "F34"}, {"Protos_Modified.ID", "Protos_Modified.Company", "Protos_Modified.Name", "Protos_Modified.Position", "Protos_Modified.Ramco Contract #", "Protos_Modified.Date", "Protos_Modified.Day", "Protos_Modified.Start", "Protos_Modified.End", "Protos_Modified.In 1", "Protos_Modified.Out 1", "Protos_Modified.Unpaid Break Hours", "Protos_Modified.Actual Hours", "Protos_Modified.Delta Hours", "Protos_Modified.Shift End Date", "Protos_Modified.MINUTES Current Day", "Protos_Modified.MINUTES Next Day", "Protos_Modified.MINUTES Total Hours", "Protos_Modified.MINUTES Hours Var", "Protos_Modified.ROUNDED Current Day", "Protos_Modified.ROUNDED Next Day", "Protos_Modified.ROUNDED With Lunch", "Protos_Modified.ROUNDED Total Hours", "Protos_Modified.ROUNDED Hours Var", "Protos_Modified.F24", "Protos_Modified.F25", "Protos_Modified.F26", "Protos_Modified.F27", "Protos_Modified.F28", "Protos_Modified.12:00 AM", "Protos_Modified.F30", "Protos_Modified.F31", "Protos_Modified.F32", "Protos_Modified.F33", "Protos_Modified.F34"})
    in
        #"Expanded Protos_Modified"

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Moving data from rows into columns
    By Guitarzan in forum Access
    Replies: 1
    Last Post: 08-07-2019, 07:38 AM
  2. Replies: 2
    Last Post: 07-30-2019, 01:56 PM
  3. Replies: 1
    Last Post: 10-07-2018, 10:07 PM
  4. Transpose Data from Rows to Columns
    By Crdmster in forum Queries
    Replies: 1
    Last Post: 08-08-2017, 02:18 PM
  5. Replies: 6
    Last Post: 02-14-2015, 05:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums