Homework 08
Dynamic queries to access remote data
Goal
you will learn about automating 'dynamic' SQL queries. These are queries that can be customized by the user to obtain desired
results.
As a bonus, you will learn about combo boxes.
Context
It is early morning and you just started your first cup of coffee when Ms. Lendalot walks into your office.
"Goooood morning! That app you built is faaan-tas-tic! None of my managers uses the old client reports anymore. They
ALL use your Excel app to check the lists of customers from the various states that we serve. It is faster and always updated.
Exceptionally well done!"
Clearly your app was a success and Ms. Lendalot is in a good mood. Your day is starting well.
"I wonder if you could do something like that for our financial advisors... They often need to access historical stock data
to answer customer questions... I bet that they would love it!"
"Well, Ms. Lendalot, I...."
"But wait... maybe we cannot do it. Sometimes they need to see a basket of stock prices for a given date, and other times
they need to see a range of dates for a single stock. That sounds more complicated than the apps you did before. Do you
think you can handle that?"
You flash a confident smile: "I am on it, Boss!"
Task and Requirement
The SmallBankDB contains a table called StockMarket, which stores several months of closing prices for a basket of a dozen stock that have been selected by your financial director. Your job is to create an app that:
- connects with the database at startup.
- at startup the screen shows no stock data in the listobject
- fills up two combo boxes with distinct dates and distinct tickers.
- shows all the available stock info for a given date at the press of an orange button (show all columns, orange cbox, and orange listobject). The video mentions a "TableStyleMedium3". Change it to the appropriate in the new Excel palette if necessary.
- shows all the available info for a given stock when a blue button (show all columns, blue cbox, and blue listobject) is pressed.
- uses only one listobject, recolored when the buttons are pressed
- column size must not cut out any data or headers
The vLab below will put you on the right path towards a solution. As always, the video will get you close, but it is not the solution. Your stock market data will be different from what is shown in the video.