I am connecting to a table where some records might have the same value (long story as to why) Anyway seems simple enough to use the MIN function in my left join. but it is not working this is the statment
SELECT B.Company AS Company,IIF(ISNULL(B.CostCell),"HR",B.Department) AS Department,IIF(ISNULL(B.CostCell),"Recruitment",B.CostCell) AS CostCell,A.Status,A.ID AS EmployeeID, B.[first] & " " & B.[last] AS employeename, B.Last AS LastName, B.First AS FirstName, A.ExpenseType AS ExpenseType, A.Transactiondate AS ExpDate, A.Amount AS ExpAmtUSD, Month(A.[Transactiondate]) AS MonthID, Year(A.[Transactiondate]) AS YearID, A.Merchant AS Vendor, A.index,System, 0 AS MissedSavings,A.ADC AS Description
FROM tbltravelexpenses A LEFT JOIN Select MIN(B.employeeid ) From tblemployeename B on A.[ID] = B.employeeid
This is the part that is not working
LEFT JOIN Select MIN(B.employeeid ) From tblemployeename B
Any suggestions are appreciated. thank you
SELECT B.Company AS Company,IIF(ISNULL(B.CostCell),"HR",B.Department) AS Department,IIF(ISNULL(B.CostCell),"Recruitment",B.CostCell) AS CostCell,A.Status,A.ID AS EmployeeID, B.[first] & " " & B.[last] AS employeename, B.Last AS LastName, B.First AS FirstName, A.ExpenseType AS ExpenseType, A.Transactiondate AS ExpDate, A.Amount AS ExpAmtUSD, Month(A.[Transactiondate]) AS MonthID, Year(A.[Transactiondate]) AS YearID, A.Merchant AS Vendor, A.index,System, 0 AS MissedSavings,A.ADC AS Description
FROM tbltravelexpenses A LEFT JOIN Select MIN(B.employeeid ) From tblemployeename B on A.[ID] = B.employeeid
This is the part that is not working
LEFT JOIN Select MIN(B.employeeid ) From tblemployeename B
Any suggestions are appreciated. thank you