Max function will not return highest value in SQL table.

[Kirbydog]

Hello fellow porgrammers,

I have tried to resolve a problem in the VB forum that is giving me a really difficult time. The problem is a SQL query that uses a MAX function to retrieve a value in a table that holds employee numbers and other pertinent data that supports an application that I built.

In a nutshell, I have a sub-routine that is designed to access my SQL table and using this criteria return the highest employee number in a given company, there are three companies that I have to select from and then within that company the employee's names are grouped together based on thier last name, such as, if the last name is "L" then all employees with the last name starting with "L" is selected by my query.

Next the query should retrieve the highest employee number in that group so that the sub-routine can then increment that number by one and then it is added back to the table. So far it has not worked, some of the other programmers have tried to help but to no avail, I have gotten halfway thier but no cigar, no hitting the nail on the head, nothing...

Here is a sample of my query:

SELECT MAX(EIDNUM) AS MaxID, empLName, empComp,EIDNUM
FROM dbo.EmpHistory
WHERE (empComp = 'CCW') AND (empLName LIKE 'L%') AND
(EIDNUM NOT LIKE '199%')
GROUP BY empLName, empComp, EIDNUM

This query will return all of the employees whose last names begin with "L", I only want the employee number that is the highest number so that it can be incremented and assigned to a new employee. Any help that any of you can provide will be most appricated.

Thank You

JC

 

[SQLBill]

You aren't asking the MAX out of all the matching results. You are asking for the MAX for each empLName.

First get all the data and then get the MAX.

SELECT MAX(EIDNUM) AS MaxID, empLName, empComp
FROM t1
WHERE (SELECT EIDNUM empLName, empComp
FROM dbo.EmpHistory
WHERE (empComp = 'CCW')
AND (empLName LIKE 'L%')
AND (EIDNUM NOT LIKE '199%') t1)

Is that what you are looking for?

-SQLBill

 

[Kirbydog]

Dear SQLBill,

Thanks for your info, however, I think there is a ghost in my machine, I can not get your sample to run, I keeop getting a syntac error at the end of the query...

I tried to get as close as your example as I could:

EHRS.Open "SELECT MAX(EIDNUM) AS MaxID, " _
& "empLName, " _
& "empComp, " _
& "FROM EmpHistory " _
& "WHERE (SELECT EIDNUM,empLName,empComp FROM EmpHistory " _
& "(WHERE (empComp = '" & Company & "') AND " _
& "empLName LIKE '%" & LastName & "%' AND " _
& "EIDNUM NOT LIKE '%" & 199 & "%' ) FROM EmpHistory)" _
, db, adOpenForwardOnly, adLockOptimistic

The above is my exact query, any additional suggestions, man, I am tired of pulling my hair out because it is almost all gone... :-(

Thanks for your help!

JC

 

[royjimenez]

u have a "," before the FROM clause

drop it !!

 

[Kirbydog]

I corrected that problem, now I keep geting the same error with this version of the query:

EHRS.Open "SELECT MAX(EIDNUM) AS MaxID, " _
& "empLName, " _
& "empComp FROM EmpHistory " _
& "WHERE (SELECT EIDNUM,empLName,empComp FROM dbo.EmpHistory " _
& "WHERE (empComp = '" & Company & "') AND empLName LIKE '%" & LastName & "%' AND " _
& "EIDNUM NOT LIKE '%" & 199 & "%' FROM dbo.EmpHistory)" _
, db, adOpenForwardOnly, adLockOptimistic

I keep going over the same problem with no solution, so what do your think, can see anything that I have missed? Or maybe I should try something different?

 

[JamesLean]

I think your problem is you are returning too many columns. All you want is the current highest id of names starting with 'L', correct? If so, try this:

SELECT MAX(EIDNUM) AS MaxID
FROM dbo.EmpHistory
WHERE empComp = 'CCW'
  AND empLName LIKE 'L%'
  AND EIDNUM NOT LIKE '199%'

--James

 

[Kirbydog]

Dear Fellow Programmers,

Success, James Lean's helpful insight as to the number of columns that I was trying to return hit the nail on the head. I used the colum that has the employee ID numbers and filerted for the last name and excluded the temporary numbers.

The only other problems was that I did not have the LIKE clause set up correctly in VB, once I corrected the syntax it all fell together. Again thanks for your help, here is my final query:

EHRS.Open "SELECT MAX(EIDNUM) AS MaxID " _
& "FROM dbo.EmpHistory " _
& "WHERE empComp = '" & Company & "' AND " _
& "empLName LIKE '" & LN & "%' AND " _
& "EIDNUM NOT LIKE '" & 199 & "%'" _

Again, thanks ever so much...