Dbuy, The user won't necessarily know that the result is wrong, unless they do add it up. They could run it again, but how do they know that the report is still flawed ?
To take the most trivial example (Sorry you may already have got this):
A new business is adding invoices continuously. The report is requested. There are invoice in the table, totalling £10000 plus £2000 in VAT.
The first select query runs to get the Sales analysis. We get the result, Sales of Widgets, £10000. The VAT content is saved in the same records, £2000
Another invoice is added.
The second query runs to get the VAT. Now we have £11000 of sales and £2200 of VAT saved in the table.
We get VAT £2200 returned
Another invoice is added.
The third query runs to get the debtors, by summing the net and VAT values. Now we have £12000 of sales and £2400 of VAT saved in the table.
We get £14400 returned as debtors total.
It might not be just new transactions that are being added, the existing ones might be edited. All this could happen in two seconds, couldn't it?
So the user is presented with the result
You generated sales of widgets, £10000
You charged VAT of £2200 and owe the taxman £2200
You are owed by customers £14400.
And the user is going to say, at the moment in time when that report was presented:
how is that I'm owed £14400 when I generated sales of only £10000 and charged VAT of £2200 ?
how is that I owe the taxman £2200 when the VAT on sales of £10000 is only £2000 ?
On big numbers, they may not realise but I think they expect to see a consistent result, or we disclose that it might not be.
Currently I pop up a warning saying that the results may be inconsistent, if they choose to report on 'pending' transactions. Which is sort of OK if there's no better way.
Really interesting to hear everyone's thoughts about this. Doc_Man, thank you, the timestamps would provide a cut-off for new records but wouldn't stop existing ones from being edited. I'm pretty sure the queries can't be combined for a one-hit read, because of the relationships. I've wondered if it's a possibility but it's definitely beyond my skill level.
It's an Access BE.