Pfthb

I am working with Delphi 2010. I have a Microsoft Access database called tblUserInfo, in it there is a field called NUMOFREPORTS, containing 11 numbers. I need to search through the data items and find the maximum number and store it in a variable.
My plan was to assign the values in the database to an array of integers and then sort through the array to find the maximum.

This is what I have so far:

i:=1;
while NOT tblUserInfo.eof do
begin
 arrNumOfReps[i]:= tblUserInfo['NUMOFREPORTS'];
 tblUserInfo.Next;
 INC(i);
end;

NumOfReps:= arrNumOfReps[1];
for I := 1 to length(arrNumOfReps) do
begin
 if arrNumOfReps[i] > NumOfReps then
 begin
 NumOfReps:= arrNumOfReps[i];
 end;
 showmessage(inttostr(NumOfReps));
end;

When I run the program it does not work and breaks at NumOfReps:= arrNumOfReps[1]; and I get an error that says "Access violation".

Does anyone have any corrections to my code or alternative solutions?
Thanks

As it was pointed out by MartynA in the comments you should let database to find you the maximum value rather than retrieving all the values first and then search for the maximum value by yourself. Why?

1. In your case you claim that you only have 11 values in this field so retrieving that data from database wouldn't be difficult. But what if you would have one million values there. Retrieving 1M values from database can be quite taxing on the database and would also consume significant amount of memory on your client computer if you store these values in an array for further processing (finding maximum value).


2. Database would surely be able to find the maximum value much faster than you since it can easily split this task between multiple cores. Not to mention the fact that if that specific field is indexed the database most likely already has the maximum value stored as one of the index values in which case database could return the maximum value instantly. And even if the maximum value isn't directly stored as one of the index values database would only have to check part of the records instead of checking each and every one.

And if for some special reason you really can't use database built-in Select Max function (perhaps value isn't stored as numeric value but as special string and the maximum value is determined by some part in the middle of the string) and have to really find the max value by yourself don't bother storing all of the values into an array. Instead do the needed comparison for finding the max value directly while retrieving records from database. Perhaps something like this where I just modified your loop for retrieving records from the database:

while NOT tblUserInfo.eof do
begin
 if tblUserInfo['NUMOFREPORTS'] > NumOfReps then
 begin
 NumOfReps:= tblUserInfo['NUMOFREPORTS'];
 end;
 tblUserInfo.Next;
end;

How did you declare your array? Based on the way you work with it, it should probably be declared like this:

var
 ArrNumOfReps: array[1..12] of Integer;

But that's limited, since the program only supports a maximum of 11 values. Rather use a dynamic array

var
 ArrNumOfReps: array of Integer;

i:=0;
while NOT tblUserInfo.eof do
begin
 SetLength(ArrNumOfReps, Length(ArrNumOfReps) + 1);
 arrNumOfReps[i]:= tblUserInfo.FieldByName('NUMOFREPORTS').AsInteger;
 tblUserInfo.Next;
 INC(i);
end;

This will allow the program to work with a variable number of reps.