[Wolves] Yet another non Linux question ; -) - well nearly at end

[Chris Ellis]

> Foxpro (spit) based database for tracking tenants payments in rented
> accommodation - commercial package that promises everything, but does it all
> really badly -
> one of the built in reports generates a 40 page report on arrears -
> detailing every last transaction missed when all we really need is balance,
> have they made a payment in last couple
> of weeks (so at least they are trying) if so how much  - two items per line.
> Tech support answer - there isn't a 'balance' field , everything is
> calculated on the fly from data in paid table, owing table - can't give you
> a database schematic or field list -
> you should be able to work out what field is what (there are 300+ all oddly
> labelled!) , and 'that will be a really complex query to compile, good luck'
> Not getting anywhere with making a query as don't really know where to
> start!
> Horrible thing.

Does the supplier not offer consultancy?

It sounds like you need a report which uses aggregate functions, eg:

    SELECT tenant, sum(paid) FROM paid_in;

Or maybe you just want a list of tenants who have not paid in the last month:

   SELECT tenant FROM tenants WHERE tenant NOT IN (
        SELECT tenant FROM paid_in WHERE paid_date > (now() - '1 month')
   );

Note: Pseudo SQL

>
> Against -
>  a MS excel spreadsheet - the old system used prior to purchase of the above
> - only dropped because the only thing no one could figure was how to add an
> audit
> trail to the file - ie who changed what last to prevent fraud by users (eg
> collecting £300 from tenant, updating spreadsheet to show paid...)

How does a single spreadsheet work in a multi-user scenario, with out
it being a
risky mess?

>
>
> So which is the easiest thing to achieve - bugger about getting the right
> foxpro query or creating a security level over a spreadsheet?

Securing a spreadsheet is a lost cause.  You would need some method of
merging concurrent edits and cryptographically signing the changes.

>
> Or is there a nice little mysql/php package out there that will just be easy
> to fiddle with and start again?

Surely there are many solutions on the market for this situation? It
has a to be
a pretty common use case.

If the fraud risk is a real concern, then surely the expense of either
buying a new
system or getting a system built is justifiable and worthwhile.

_snip_

Regards,
Chris