Most of my work today involved me fiddling around with Excel to format the data in convenient ways. First I finished up the work from yesterday by getting all the data into one Excel sheet. From there I divided it by day of the week (for those of you who don't know, Excel has a super handy DayOfWeek function that I highly recommend) and put each of those into their own sheets. With each day I created a line chart that showed the number of runs at each hour of the day for each individual day, and another chart that took the average for each hour and plotted that along with the standard deviation bars to show what the template for that day might look like. Overall, today's work was tedious and a good amount of work to get done, but it was not altogether very challenging. Oh, I also made a folder in Google Drive for all our project materials today, along with a document that expresses my current questions and concerns about the data.
It's sunny again today! When I left yesterday afternoon I was super close to getting my VBA code with the embedded SQL to work, or so I thought. Turns out that the reason my code wouldn't run was based on many, many problems that I had to debug including, but not limited to, the fact that spaces are important to watch for when inserting SQL that is multiple lines, DoCmd.RunSQL won't work for SELECT statements, and the database I was using to get my data was actually another query (Access uses like views) so I had to find some clever way to get VBA to allow me to do that.
After my meeting with Anat at the end of my day yesterday, I had a good idea of what I had to get done in the next week. The hard part was just figuring out exactly how to do that. Mainly, today I toyed around in Access until I found a way to create SQL queries that would help me count how many runs are out for any one provider at any one time during any particular day. To do this I had to go back to some of my old notes on SQL and really plan out what information I needed and wanted to record. My real challenge was once I finished my initial work and had to start to figure out how I could automate the process to do this many times for many different days, times, and providers. Most of my afternoon was spent looking up information on macros in Access and with that also VBA code and how to use SQL code with that.
My first full day of work at the university, and the first problem I ran into was simply connecting my laptop to the CSE wifi. Turns out I had to have Anat create a guest account for me to sign on since I am neither a student nor faculty. After getting this to work, I went about reading a paper published in 2008 that aimed at making the Minnesota Paratransit System more efficient in it's routing system. Mainly it looked at using algorithms and heuristics to improve the routes that the MM (Metro Mobility) system was already using by cutting down on run times, and it investigated the possible financial benefits that are associated with offering certain passengers non-dedicated vehicle options (i.e. taxis) instead. In addition, today I got a different school issued laptop that actually has Microsoft Access this time, so I was able to actually view the data for the first time today.
Today I met with Anat for the first time in person. She got me set up with my own tablet from the university, and my access card that gets me into my lab on the sixth floor. We went over some of the details of the project, and she forwarded to me the dataset that Matthew had sent her. Unfortunately, I the tablet does not have Microsoft Access on it, so I couldn't view it from my own computer while we had our conference call with Matthew at 3:30. Still, Anat asked a lot of clarifying questions about the data in our conference call, and now I feel like I have a little bit better of an idea of what we need to do and which variables will be important for us to inspect more closely.