I use tables a LOT for user interaction not queries. I also like to give users the facilities to filter and reorganise the data. I use indices for the latter. The former would, if I only used a filter be limited to the data actually contained in a specific table. So to give an idea

Companies
Stes
Contacts
Career

Companies: Sites is 1:M

Contacts: Career is 1:M

Career: Companies is 1:M with Sites sort of as an interposer with Career:Sites being 1:1

Just concentrating on Companies and Sites

I want the users to have the ability to select Companies based on (say) the town they're in but that data is in Sites. The "correct" way of doing that would be to use a query. To me that's a bit of a no-no because then if the user wants to flip the sort order 1) you have to run another query and 2) there is a horrible flicker as the display is altered.

Waiting for a complex query to run is OK but lets say that Companies were displayed in name order then I wanted to alter the display to organisation type order. There are indices on both columns and a simple

SELECT * FROM Companies ORDER BY Name

or

SELECT * FROM Companies ORDER BY _fkOrgType

would give the info. Its the display flicker I can't tolerate plus a lot of my code is navigational in nature and I have been able to customise TEDBTable to do what I want rather than what the SQL standard wants Add to that the fact that canned (woops sorry - insensitive) datasets can't be edited and I want to stick with tables.

A filter is a nice simple thing. Generally dynamic so that if you make a change to the column(s) the filter is based on it can take it out of the display.

What I do for my "static" filters is use SQL via my query generator to create a list of primary keys for the table which are then built into an IN statement eg

_ID IN (1000001,1000856....9999999)

Users can't alter the primary key, and no matter how they alter the data the selected records stay, the index can be flipped but until the filter is turned off or changed to something else the data is static

Simples!


Roy Lambert