Regrettably not unfortunately. Which is a pity because Access has really been annoying me today due to its extreme shitness. If the worst comes to the worst, I'll just have to update 2 fields every time a loan comes or goes. Hardly an ideal solution though.

Might have another go at it tomorrow. Might be possible to find some way of doing it by modifying the table design a little perhaps.

Solved!

OK it's a bit of a bodge and one fuck of a work-around, but it now works.

The final solution involves 4(!!) queries and a temporary table. The first query empties the temporary table, the second query fills it with a list of current loan counts (you can append whilst using "Count" in a sub query, but not update - bizarre!), the third query resets the "Amount available" field in the main "Tests" table to match the number of copies (needed so that returned items were being properly updated) and the fourth query takes the data from the temporary table, calculates the amount available from the total number of tests and the amount out on loan, then stuffs it into the main "Tests" table.

Bung in a couple of action events to execute that lot whenever the loan information is updated and the end result is that the form/table correctly updates itself whenever loan information is changed. One fuck of a bodge admittedly, but the part I'm bothered about is that it works. Plus this isn't going to be a heavily used database so overhead won't be a problem. Most of the time it's just going to be viewed and in which case, no queries are executed at all.

So. Thanks very much for the suggestions, help, etc - particulaly with the second query which lends most of its code from Ally and BP. And I now fucking hate Access (not that I exactly loved it before mind you)!