Our Studio had an Access database that it used for off-site auditions containing the usual information like name, address, audition place & time, etc. We have since transferred it to Tessitura this last summer. Now, as auditions start up again, I am tasked with outputting the appropriate information back into Access so that the Studio can be mobile in places where there is supposedly no connection.
My idea is to set up a view for the dozen or so tables involved that outputs the couple of dozen fields needed to populate this Access database form they're used to seeing. I figure Access' linked tables function will allow us to grab the live data, populate a temporary table upon which the form is based and off they go. Upon return, I'm then supposed to facilitate the transferral of any updated information back into Tessitura.
So, am I going about this the right way? And, for my view, how would you suggest I get phone1 and cell phone into the same row for a given customer_no since they're two different rows in the T_PHONE table? Would I use a subquery for each phone number type in my view?
Your advice/comments are most welcome. Thank you, Tessiturians.
BONUS TRIVIA OUTPUT: Richard Gere turned down Bruce Willis' role in Die Hard.
I think your general approach should work. If this is for a view-only application that doesn't need a lot of formatting on the screen, you might consider ignoring access and doing it in Excel, but the principle is the same in either case.
In the case of the phone numbers, you can join to T_PHONE twice using different aliases. I'm doing this without peeking at the database so don't quote this but....
select t_customer.*, cell.phone_no,home.phone_no
from t_customer (nolock)
join t_phone cell (nolock) on t_customer.customer_no = cell.customer_no and cell.phone_type = 'cell'
join t_phone home (nolock) on t_customer.customer_no = home.customer_no and home.phone_type = 'home'
To clarify.... doing this in access means you would write a local view in Tess that combines everything you want into a single record and then connect to it with Excel.