none
Return dataset based on (2) tables RRS feed

Answers

  • Try this:

    SELECT New_PFP_Request.AssignedCode, New_PFP_Request.[Created By], New_PFP_Request.Created, New_PFP_Request.RequestStatus, UserInfo.[Work Email]
    FROM New_PFP_Request INNER JOIN UserInfo ON New_PFP_Request.[Created By] = UserInfo.ID
    WHERE New_PFP_Request.RequestStatus = 'Complete';


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Thursday, July 12, 2018 5:41 AM
    • Marked as answer by cdtakacs1 Thursday, August 2, 2018 12:53 PM
    Wednesday, July 11, 2018 3:26 PM

All replies

  • If Created By is a Number field, it must be a lookup field that gets the text of the name from another table - perhaps the UserInfo table?

    You can check this by opening New_PFP_Request in Design Wiew, selecting the Created By field, and activating the Lookup tab in the Field Properties at the bottom of the table design window. What is the Row Source property?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 11, 2018 2:41 PM
  • Try this:

    SELECT New_PFP_Request.AssignedCode, New_PFP_Request.[Created By], New_PFP_Request.Created, New_PFP_Request.RequestStatus, UserInfo.[Work Email]
    FROM New_PFP_Request INNER JOIN UserInfo ON New_PFP_Request.[Created By] = UserInfo.ID
    WHERE New_PFP_Request.RequestStatus = 'Complete';


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Thursday, July 12, 2018 5:41 AM
    • Marked as answer by cdtakacs1 Thursday, August 2, 2018 12:53 PM
    Wednesday, July 11, 2018 3:26 PM
  • What should the result contain?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 2, 2018 2:38 PM
  • You can do that using a Union query:

    SELECT New_PFP_Request.AssignedCode
    , New_PFP_Request.Alias
    , New_PFP_Request.ParentNode
    , UserInfo.[Name]
    , UserInfo.[Work Email]
    , New_PFP_Request.Modified
    , New_PFP_Request.RequestStatus
    FROM
    New_PFP_Request INNER JOIN UserInfo ON New_PFP_Request.[Created By] = UserInfo.ID
    WHERE
    ( New_PFP_Request.RequestStatus = 'Complete' OR  New_PFP_Request.RequestStatus = 'Rejected')
    AND
    New_PFP_Request.Modified >= DATE()-1
    AND
    New_PFP_Request.Modified < DATE()
    UNION SELECT Null
    , New_PFI_Request.N_Alias
    , New_PFI_Request.N_Parent_Node
    , UserInfo.Name
    , UserInfo.[Work Email]
    , New_PFI_Request.Modified
    , New_PFI_Request.Request_Status
    FROM
    New_PFI_Request INNER JOIN UserInfo ON New_PFI_Request.[Created By] = UserInfo.ID
    WHERE
    New_PFI_Request.Modified >= DATE()-1 AND New_PFI_Request.Modified < DATE();


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 2, 2018 8:37 PM