Need processing ideas!

[lespaul]

I need to record payments to jurors and store the check number. I have a table that is populated with the jurorid for all jurors who are receiving payment. The accounting clerks would like a "wizard" where they input the beginning and ending check numbers and it will automatically pair the juror with the check number. Sounds easy, but...(there's always a but!), sometimes the checks are not in sequential order (there are gaps). So the first 50 checks are 25001 - 25050, the next set of 50 is split 25101 - 25110; 25120 - 25150. So, I'm looking for some ideas on how to update the table so that the process completes only the number of checks in each range then refresh the query so only people without a check number are left for the next set of checks. Thanks for your thoughts!



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[lespaul]

Just a little more info I forgot! The query I use to get the jurors to be paid is joined (which complicates things!).

Thanks!

leslie

 

[cmgaviao]

This doesn't sound like it should be too difficult. It does sound like its more of an sql question though...

which would probably mean another forum depending on what kind of db you're using.

and it would be helpful to know what the basic structure of your tables are and the joins between them.

select * from table
where checknum between x and y
or checknum between a and b

?

 

[richardchaven]

I would use two queries: one to get the set of unpaid jurors and one to pay them individually. I would not try and use a clever query to do it all at once.

Notes: a TDBDataSet is just an ancestor of TTable and TQuery in case you wanted to use either. The function will start processing at the current row of the dataset. It will return the last check number actually inserted into JUROR_CHECK in case there are fewer payees than check numbers.

function PayJurors(UnpaidJurorsDataSet : TDBDataSet; StartingNumber : Integer) : Integer; 
var
   PayJurorQuery : TQuery;
begin
   PayJurorQuery := TQuery.Create(nil);
   try
       PayJurorQuery.DatabaseName := UnpaidJurorsDataSet.DatabaseName;
       PayJurorQuery.SQL.Text := 'INSERT INTO JUROR_CHECK (JUROR_ID, CHECK_NUMBER) VALUES (:JUROR_ID, :CHECK_NUMBER)';
       PayJurorQuery.Prepare;

       Result := StartingNumber - 1;   
       while (not UnpaidJurorsDataSet.EOF) and (Result < EndingNumber) do
       begin
           Inc(Result);

           PayJurorQuery.ParamByName('Juror_ID').AsInteger := UnpaidJurorsDataSet.FieldByName('Juror_ID').AsInteger;
           PayJurorQuery.ParamByName('Check_Number').AsInteger := Result;
           PayJurorQuery.ExecSQL;
    
           UnpaidJurorsDataSet.Next;
       end;
   finally
       PayJurorQuery.Free;             
   end;
end;

Cheers

 

[lespaul]

Richard, that's exactly what I came up with!!! Great minds think alike! I have a query that returns jurors with no check number, have the user enter the check number range and for i := startingnumber to endingnumber run the update query, then I run a second query to display the check number with the person's name to allow the clerk to review and update the information! I already have the jurornumbers in the check table so it's only an update query.

I started mulling this over last Friday and after posting it all came together!

Thanks again for the suggestions!

Leslie