I'm trying to create a calculation which will give me the total number of books for sale with the same author.
eg. I have 40,000 books in the database of which 25,000 are for sale. There are 30 Dan Brown books of which 20 are for sale. I want each of the Dan Brown records to have a field stating 20.
And every other author to also have their quantity of books for sale listed in each of their records.
Summary and aggregate type functions only work when the records are sorted which is not an option (because of the next step in my plans).
I have tried Execute SQL with the following:
ExecuteSQL ("SELECT COUNT (Author) FROM Books WHERE Author = Author)" ;"" ; "") but it doesn't seem to like a field name where the last "Author" is, and I suspect it wants a string.
From my research so far ExecuteSQL appears to be the only way to get the result I want but as I am very new to SQL I would appreciate some help in the matter. Any other alternatives would be welcome too.