SELECT [Test ID], Count([Loan ID]) AS Loans
FROM [Loan DATA]
WHERE [Date Loaned Out] IS NOT NULL
AND [Date of RETURN] IS NULL
GROUP BY [Test ID]

Cheers, that pretty much works for creating a query which lists the number of current loans. The next problem is how to make use of that data. Access won't let me use the "Loans" field from the query in my form because the data for that query comes from one of the tables being referenced in the form. Not a big deal I'd say, but Access disagrees.

Gah I hate Access some times >:-(

Thanks for the help so far mind you!

UPDATE Tests INNER JOIN [Loan DATA] ON Tests.[Test ID]=[Loan DATA].[Test ID] 
SET Tests.[Available Copies] = Tests.[No of Copies]-(SELECT Count([Loan DATA].[Loan ID]) AS Loans 
     FROM Tests INNER JOIN [Loan DATA] ON Tests.[Test ID] = [Loan DATA].[Test ID] 
     WHERE ((([Loan DATA].[Loaned TO]) IS NOT NULL) AND (([Loan DATA].[Date of RETURN]) IS NULL)));

SELECT [Loan DATA].[Test ID], Count([Loan DATA].[Loan ID]) AS Loans
FROM Tests INNER JOIN [Loan DATA] ON Tests.[Test ID] = [Loan DATA].[Test ID]
WHERE ((([Loan DATA].[Loaned TO]) IS NOT NULL) AND (([Loan DATA].[Date of RETURN]) IS NULL))
GROUP BY [Loan DATA].[Test ID];

UPDATE Tests
 
FROM [Loan DATA] 
 
SET [Available Copies] = Tests.[No of Copies]-(SELECT Count([Loan DATA].[Loan ID])
 
WHERE Tests.[Test ID] = [Loan DATA].[Test ID]
AND  ((([Loan DATA].[Loaned TO]) IS NOT NULL) AND (([Loan DATA].[Date of RETURN]) IS NULL)))
;

Access added those so I assume they're allowed!

Thanks for the new code. Unfortunately you can't use a "FROM" statement with an "UPDATE". :(

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