How do I select the highest record in a table

[Kirbydog]

Hello,

I have an application that I built that I am having a most difficult time with. The application is an employee number generation app designed to generate employee numbers for three companies. The front end is Visual Basuc 6 and the back end is MS-SQL.

I store employee information in the form of the employee's name, ID number, start date, etc., the first letter of the employee's last name is used to select a group and then a filter and Max function is used to select the highest employee number. After the highest number is selected it is incremented and saved back to the employees table and then the front end screen is updated.

I am using ADO to connect to the MS-SQL tables, my query is: EHRS.Open "SELECT EmpHistory.EmpHist_ID, " _
& "EmpHistory.EIDNUM, " _
& "EmpHistory.EIDNUMBarCode, " _
& "EmpHistory.empLName, " _
& " EmpHistory.empComp, " _
& "EmpHistory.Time, " _
& "EmpHistory.LastUpdate_ts " _
& "FROM EmpHistory " _
& "WHERE (((EmpHistory.empComp) = '" & Company & "')) AND " _
& "(EmpHistory.empLName LIKE '%" & LastName & "%' AND " _
& "EIDNUM NOT LIKE '%" & 199 & "%')" _
, db, adOpenKeyset, adLockOptimistic

If EHRS.BOF = True And EHRS.EOF = True Then
Do While Not EHRS.EOF
If EHRS!EIDNUM <> moEmpNum Then
SintRC = EHRS!EIDNUM
SintRC = SintRC + 1

EHRS.MoveNext
End If
Loop

As you can see I am trying to loop through the record set and get the highest employee number given the first letter of the employee's last name. This will work and some times it will not work, the query will return the employee number that is maybe the first number or the second to the last number, it is inconsistent.

Does anybody know what is wrong, I have tried different constructs but no luck...

 

[Glasgow]

Can you not just use something like

EHRS.Open &quot;SELECT MAX(EmpHistory.EIDNUM) FROM .... etc...&quot;

then check value of EHRS.Fields(0)

 

[CCLINT]

Depends if you want to return one record (the max), or all records which meet the criteria and have the records order by the highest to lowest number.

For the latter, just add an ORDER BY clause:
& &quot; ORDER BY EIDNUM DESC&quot;

For the former, use a GROUP BY clause:

&quot;SELECT EmpHistory.EmpHist_ID, &quot; _
& &quot;MAX(EmpHistory.EIDNUM), &quot; _
& &quot;EmpHistory.EIDNUMBarCode, &quot; _
& &quot;EmpHistory.empLName, &quot; _
& &quot; EmpHistory.empComp, &quot; _
& &quot;EmpHistory.Time, &quot; _
& &quot;EmpHistory.LastUpdate_ts &quot; _
& &quot;FROM EmpHistory &quot; _
& &quot;WHERE (((EmpHistory.empComp) = '&quot; & Company & &quot;')) AND &quot; _
& &quot;(EmpHistory.empLName LIKE '%&quot; & LastName & &quot;%' AND &quot; _
& &quot;EIDNUM NOT LIKE '%&quot; & 199 & &quot;%') &quot; _
& &quot;GROUP BY EIDNUMBarCode,empLName,empComp, Time,LastUpdate_ts &quot;

 

[Kirbydog]

Dear CCLINY,

Yes, I only want to return one number, the highest and then I am trying to increment that number so it will become the new employee's number. I am trying the corrections that you gave me and I will let you know how it turns out, thanks...

 

[Kirbydog]

Hello,

The solution provided did not resolve the problem, the Max function will not return any records, it gives an error, &quot;Item cannot be found in the collection corresponding...&quot; the message is cut off. I tried removing the Max function, no change. If I try Glasgow's solution I still need to isolate the employee's by thier last name...

Any suggestions?

 

[Glasgow]

Does something like this work?

dim NewEmpNo as Long
EHRS.Open &quot;SELECT MAX(EmpHistory.EIDNUM) &quot; _

& &quot;FROM EmpHistory &quot; _
& &quot;WHERE (((EmpHistory.empComp) = '&quot; & Company & &quot;')) AND &quot; _
& &quot;(EmpHistory.empLName LIKE '%&quot; & LastName & &quot;%' AND &quot; _
& &quot;EIDNUM NOT LIKE '%&quot; & 199 & &quot;%')&quot; _
, db, adOpenKeyset, adLockOptimistic
NewEmpNo=EHRS.Fields(0)+1
MsgBox &quot;Next employee number is &quot; & NewEmpNo

 

[CCLINT]

Sorry, I overlooked a field:

&quot;SELECT Max(EmpHistory.EmpHist_ID), &quot; _
& &quot;EmpHistory.EIDNUM, &quot; _
& &quot;EmpHistory.EIDNUMBarCode, &quot; _
& &quot;EmpHistory.empLName, &quot; _
& &quot; EmpHistory.empComp, &quot; _
& &quot;EmpHistory.Time, &quot; _
& &quot;EmpHistory.LastUpdate_ts &quot; _
& &quot;FROM EmpHistory &quot; _
& &quot;WHERE (((EmpHistory.empComp) = '&quot; & Company & &quot;')) AND &quot; _
& &quot;(EmpHistory.empLName LIKE '%&quot; & LastName & &quot;%' AND &quot; _
& &quot;EIDNUM NOT LIKE '%&quot; & 199 & &quot;%') &quot; _
& &quot;GROUP BY EIDNUM, EIDNUMBarCode, empLName, empComp, Time, LastUpdate_ts &quot;

You may have to change as needed because I am assuming that EmpHist_ID is the field you want the Max on.
Make sure all other fields which an aggregated function is not used are listed in the Group By clause.

Test it with-out the criteria to make sure it is working, and then add the criteria back (WHERE clause)

 

[Kirbydog]

Dear Glasgow,

Thanks for the info, we do have partial success, the query is retrieving the correct group but it will only retrieves the first employee number in that group. When I tried to use the MAX function on the EIDNUM field it constantly gives error 3709 or &quot;Item cannot be found in the Ordinal&quot; or something like that.

I tried to construct the query using the &quot;Group&quot; clasue but it seems to work better when I do not use it. I know this does not sound right but here is the code:

EHRS.Open &quot;SELECT EmpHistory.EIDNUM, &quot; _
& &quot;EmpHistory.EIDNUMBarCode, &quot; _
& &quot;EmpHistory.empLName, &quot; _
& &quot;EmpHistory.empComp, &quot; _
& &quot;EmpHistory.Time, &quot; _
& &quot;EmpHistory.LastUpdate_ts &quot; _
& &quot;FROM EmpHistory &quot; _
& &quot;WHERE (((EmpHistory.empComp) = '&quot; & Company & &quot;')) AND &quot; _
& &quot;(EmpHistory.empLName LIKE '%&quot; & LastName & &quot;%' AND &quot; _
& &quot;EIDNUM NOT LIKE '%&quot; & 199 & &quot;%')&quot;, db, adOpenKeyset, adLockOptimistic

NewEmpNo = EHRS.Fields(&quot;EIDNUM&quot + 1
MsgBox &quot;Next Employee Number Is &quot; & NewEmpNo
txtEIDNUM.Text = NewEmpNo
txtBarcodeRep.Text = CStr(&quot;*&quot; & NewEmpNo & &quot;*&quot

Again, this brings back the correct group but not the highest number located in the EIDNUM field.

Dear CCLINT,

Yes, we do need to use the EIDNUM field, it is the field that containe the employee's ID number that I am trying to increment. So far, we are able to retireve an employee number and we are able to increment that number by one, it is also returned to the screen so the only area that is a problem is that I can n ot seem to get the MAX function to work properly.

Any suggestions?

Thank all of you for your help and participation, I believe this is what this forum is all about, thanks again.

 

[roccorocks]

A couple things JColbert. First it would be wise to post something like this in the SQL Server forum, you would get quicker resolution (even though you are dealing with VB). Second, based off your query, you will return a lot more than just one record. For example, if you have 'Smith' and your company has 20 smiths, then all 20 might be returned. Also, is it necessary to search by company (are you trying to get the &quot;highest&quot; for that particular company?). Try this code below. This query should be used just to find the max. The subquery will get the max ID and is then used in the criteria of the main query to pull the employee information. You will still need to use your original query fo doing searches on a particular employee.

&quot;SELECT EmpHistory.EmpHist_ID, &quot; _
& &quot;EmpHistory.EIDNUM, &quot; _
& &quot;EmpHistory.EIDNUMBarCode, &quot; _
& &quot;EmpHistory.empLName, &quot; _
& &quot; EmpHistory.empComp, &quot; _
& &quot;EmpHistory.Time, &quot; _
& &quot;EmpHistory.LastUpdate_ts &quot; _
& &quot;FROM EmpHistory &quot; _
& &quot;WHERE (((EmpHistory.EIDNUM) = &quot; _
& &quot;(SELECT MAX(EmpHistory.EIDNUM) &quot; _
& &quot;FROM EmpHistory)&quot;

Hope this helps,

rocco

 

[Glasgow]

I will not be around much today but, in case I get a chance to explore further, where are you getting this error 3709 - i.e. what line of code?

 

[Kirbydog]

Dear Glasgow,

When I use the MAX(EmpHistory.EIDNUM) function I get the 3709 error or &quot;Item not found in the Ordinal...&quot; type error. When I take out the MAX function I am able to retrieve a record but it is the first record and not the highest record. Rocorocks suggest a slightly different approch and that is to do a subquery, maybe it will work, I will give it a try.

Just think, it isn't that difficult but I am having a difficult time with this tiny little problem...

Thanks

JC

 

[Glasgow]

I assume the error is triggering on the Open statement?

I wonder whether replacing 'SELECT MAX( etc' with 'SELECT MaxId=MAX( etc' might help - i.e. assigning the returned field a name. You could then reference it as EHRS!MaxId.

Sorry, clutching at straws a bit.

I imagine most uf us spend 80% of our time resolving these 'tiny little problems' - you are not alone!