1) If the main concern is to follow up on the persons that owe the most amount of total debts, you can do a query to select the persons that owe a total of $2000 or more in a master grid.

SELECT Person, SUM(Amount) As OwesTotal
FROM XXXXXXX
GROUP BY Person
ORDER BY SUM(Amount) DESC
HAVING SUM(Amount) > 2000

You can then use a detail query to show the individual amounts of this Total in a detail grid when the user changes person.

SELECT Person, Amount, Date, etc
FROM XXXXXXX
WHERE BY Person=:Person

2) If you use the query SELECT * FROM XXXXXXXX WHERE PERSONSNAME = RANKLEY ORDER ON OWNERNAME and the table is indexed by PERSONSNAME, the query should be efficient, otherwise it would have to read the entire 3M records. You mention OWNERNAME ans an index but not PERSONSNAME. This is VERY important.

Regards
Jay

----- Original Message -----
From: John Costanzo
To: IBObjects@yahoogroups.com
Sent: Tuesday, August 15, 2006 10:10 AM
Subject: Re: [IBO] Problem using locate


I understand that you are saying BUT, this data is provided by the
state. The lawyer who uses it wants to be able to find a specific
person in the small file (which limits his view to persons who owe
over a specific amount of money) but also see all matching record in
the large file so they can see all monies owed. They don't want to
wast time persuing someone who owes $500 but if they owe over $2000
they might also owe $500, $300, etc. In which case they go after
all monies owed.

This is the point I am trying to make. It is my desire to convert
all of my BDE/Paradox applications to IBO but I am not necessarily
in control of what the client wants. Understanding that my
knowledge of IBO and Client/Server program is limited I did a few
test programs. In the BDE/Paradox test I wrote a small application
with one non-databound grid, a table, and a button. The table is
index on personsname and has 3 million + records. The button code
locates then first record whose onwername is Rankley then simply
loops, filling the grid, until the ownername is no longer Rankley.
When the button is clicked the grid is filled almost instantly. I
then wrote a IBO test application. One databound grid, 1 IBOQuery,
1 button. The SQL for the query is SELECT * FROM XXXXXXXX WHERE
PERSONSNAME = RANKLEY ORDER ON OWNERNAME (Ownwername is a defined
index). The Grid is linked to the query. The button code simply
says query1.active := true. When I click the button the IBO cursor
is displayed and the program goes to never never land for almost 2
minutes before the grid is displayed. Unless I am doing something
real wrong, this would mean that every time the user selected a name
from the top grid there would be a lag of at least 2 minutes (I say
at least because I selected Rankley because he only had a dozen or
so records). What would happen if I selected SMITH?

--- In IBObjects@yahoogroups.com, "James N Hitz" <jamhitz@...> wrote:
>
> I have never attempted to answer a question of this nature but ...
always a first time.
>
> In my view, it does not make any sense to have 1 million+ records
showing at any one time. Realistically, one would never be able
to "see" all these records at the same time... not even 2% of these
records. In other words you "spend" resources on records you will
probably never use during the entire session.
>
> Assuming your user makes at least 50 searches per hour (400 for an
8-hour day), and assuming each search generates an interest in 100
other records, this boils down to a hitrate of 40000 records for an
entire day!! (so you would be utilizing only about 1.5% of your 3m
records cache). This is inefficient by any standards. Try and
point it out to your end user from this perspective.
>
> I would suggest using a master-detail interface, and then forming
some kind of "association" that would allow searching for one person
to show "related" records from other people as well.
>
> An example would be an association by region, transaction date,
type of transaction or some other form of "association" that your
users would normally be looking out for.
>
> James N Hitz
>
> ----- Original Message -----
> From: Lester Caine
> To: IBObjects@yahoogroups.com
> Sent: Tuesday, August 15, 2006 4:03 PM
> Subject: Re: [IBO] Problem using locate
>
>
> John Costanzo wrote:
>
> <snip>
> While crude conversions can be made, as you have found this is
not the
> idea way of working.
>
> > The user scrolls or searched in the small grid and the
> > large grid follows along. In IBO this works but there is a
many
> > minute delay. Is there a way to establish position into a file
> > without selecting. Any help would be appriciated. Thanks.
>
> You need to try building this with IB_ components rather than
bothering
> with the legacy stuff. It was what IBO was designed for. You set
up a
> master and detail in IB_Query datasets and link them via the
relevant
> fields. One will track the other nicely, but when attached to
IB_Grid
> the system will only load those records needed. I wasted 6
months trying
> to convert a BDE application to IBO. It took a week to re-write
it from
> scratch AND gain a lot of functionality that the original simply
did not
> have - such as incremental search ;)
>
> --
> Lester Caine - G8HFL
> -----------------------------
> L.S.Caine Electronic Services - http://home.lsces.co.uk
> Model Engineers Digital Workshop -
> http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
> Treasurer - Firebird Foundation Inc. -
http://www.firebirdsql.org/index.php
>
>
>
>
>
> [Non-text portions of this message have been removed]
>






[Non-text portions of this message have been removed]