Alan McDonald wrote:

> I would consider an active table, a history table and a summary table.
> Active ticket transactions are in the active table, when tickets are
> completed or at month's end I would move the transactions into the history
> table. At this time I would run analysis routines on them to insert into the
> summary table.
> I would also have year-end processes which move history tables into the data
> into the appropriate year table.

No use or need in this case, the tickets are only 'active' while someone
is in the office or on the phone, and all of the 'history' table needs
to be live to see when people made previous visits. Performance on even
ten years of live data is not a problem - Firebird works well - it is
just turning the data round for the 'times during day' report that is
causing a problem. Mainly in my head, but the pokes so far have had the
right effect :)

> I use this process for accounting data. It works well with people who like
> month-end, year-end movements and provides manageable size units to archive
> without disturbing current views.

Used to build a CALENDAR table with consolidated results, but some
offices want weekly, 4 weekly, monthly, and 'target' periods so more
often than not you were having to recalculate anyway. Windowing on a
time frame is pretty fast and most of the time the complex reports can
be run on the backup machine anyway - don't need the live data at all :)

--
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