can i use a 'query' in a calculated field?
i have a database of clients, with 2 tables, a client table and a gig table (I am an entertainer). I can 0-many gigs for a client.
I would like to have an indicator on a client form, showing whether I have a gig scheduled in the future for this client (so that I don't call that client )
My first idea was to create a calculated field, which would be set if there was a row in the Gig table for that client, that had a show_date in the future. My problem is that I'm not sure if that is possible, or how to do it.
The sql query would be something like
select 'y' from Gigs, Clients where Gig.client_id = Clients.client_Id and gigs.showdate > today.
Any thoughts would be appreciatted.