I'm working on a database that contains information about the art collections of multiple clients. I have been asked to set up a function that will allow the administrator to see the number of works that each client has purchased by year. We already have fields for global information about the collections (total works of art, total amount paid, etc.), but now I am trying to set up a dropdown box that would allow the administrator to select and view this information by year. If the administrator selects 2016, they should only be able to see the number of works purchased by the client in 2016. In the client table and layout, I have a field called "Purchase Year" (configured as a dropdown) that allows the administrator to choose the year that they want to look at. In a related table called Inventory, I have a field called "Date Purchased Year" which contains the year that it was purchased. I think that I need to use an executeSQL function to format my "Total Works by Year" field, but I am having trouble figuring out how to set it up. In plain language I need to count the number of records where the Record Owner in the Client table = the Record Owner in the Inventory table and where the Client table field Year Purchased = the Inventory table field Date Purchased Year. I set the following up to no avail:
"SELECT Count ( * ) FROM \"Inventory\" WHERE \"Inventory\"Date Purchased Year = Purchase Year"
; "" ; "" )
But even if this had worked, it doesn't take into account the Record Owner contingency. Feeling in over my head. Might anyone be able to offer a hand?