And Access is really beginning to irritate me know. It also won't allow me to take the results of one query and use them in another if a "Count" is performed in the first query. Even sub-queries aren't allowed. I've been trying to use an "Update" query to dump the new "available" figures back into the Tests table - seeing as Access won't allow me to reference a query and that query's source table in the same form.
Closest I've come is this:
SQL code:
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)));
The big Select was derived from the first code that BP/Ally provided. I did get the code working in it's own query to produce a list of Test IDs with number of loans for each item. Since then it's been modified slightly in here to try and get Access to accept it, but the fact that there's a Count in a sub-query means that it's just not having it.
For what it's worth, the code which worked for the first bit is:
SQL code:
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];
Anyone got any ideas?
<goes looking for more SQL sites and info>