I need to use the Min function in my left join

Randy

Registered User.
Local time
Today, 00:51
Joined
Aug 2, 2002
Messages
94
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
 
You really need to rethink and explain what you are trying to do. Even if you could syntatically get your SQL to work, you have a logical issue:

Code:
SELECT B.Company AS Company...

B.Company isn't a field available to your query. The only field in B is the MIN(employeeid). If you want other fields avialable via B, then you need to bring them into B's SELECT clause.

You have tried to create a sub-query directly in your query. I suggest you build a whole new query to act as your sub-query, give it a name and then use it in your query, instead of trying to nest a SELECT into what you have there.

Actually the best thing might be to demonstrate with data what you have and want you want. Provide 2 sets of data:

A. starting sample data from your tables. Include all relevant table and field names and enough data to cover all cases.

B. Expected results of the data in A. Show what you expect this query to show when you feed it the data in A.
 
good point about a new query as the subquery. In fact after thinking about it yesterday that is the solution I came up with. Yes the need to do this is not reasonable. But I am taking over a prior effort and will not have time to redesign it for a couple months. because there are duplicate values for employeeid in tblemployeename when the prior builder joined it to the tbltravelexpenses it doubled the amount for those few employeeid's that where duplicated. So if employeeid 12595 was in the table twice, and there was 100.00 of expenses in the tbltravelexpneses their answer set showed 200.00. I needed a quick fix. the new query worked for now. thank you.
 

Users who are viewing this thread

Back
Top Bottom