Was today a Monday?! It sure seemed like it. At least from a work perspective. WARNING this is a brain download of my work day. Feel free to skip it, as it is mostly to empty my brain without boring Carl to death.
A rollercoaster of discovery. Working on a project I do every month.
- Finally had the data downloaded
- Got through 2 initial sets of data manipulation
- Onto the 3rd set of instructions to do my sewer metrics
- In the second or third step a query gave me an error I had never seen before.
- This query was one that kept getting longer every month.
- After poking around, comparing this month’s query with last month’s query, including looking at the sequel statement, I checked Google and discovered that you can only have 16 joins in an Access query. And it has been 16 months.
- Time to rethink the query and what I am really using the results for now. When I started this we were trying to figure out how we were going to present and use the data. Since then we have developed our presentation style, and the graphs are developed by exporting the data to Excel, and making charts there. So I don’t need to have all of the data in one Access table.
- Excitement
- I had figured out the problem.
- Now to re-do the query to only look at the current and previous month in the query. This is to allow catching any anomalies in the results, and then adding the results from the latest month into Excel to update the charts.
- The update to the query was also going to make it into a template, to reuse without as much rework each month
- Get the first draft of new query completed, and look at the data.
- And it seems off. It showed no new sewer customers, and in fact there were 5 fewer.
- Frustration- a new problem to figure out.
- Try to figure out which records had disappeared
- A few unmatched queries later I could find no missing records.
- What’s the opposite of missing data? Duplicate data, that comes and goes.
- Confusion
- Looked at several earlier months and found 3 different accounts that had duplicate accounts.
- Identified a field in the duplicate record that was different
- Checked around to understand what this field was. Not one I use. And based on the answer I got, a little more work to figure out why this account has two location IDs.
- This is impacting about 8 out of 20,000 records. Diminimus? If I were looking at just changes over each year, maybe. But looking at customer additions each month means small numbers matter.
I guess I know what I’ll be working on this weekend.
Yesterday I got a bug under my butt, and decided to look for an Airbnb for Carl and the boys for Spring Training, in case the staying with a friend option fell through. So I found a reasonably priced centrally located house with separate beds for just about everyone. And a generous cancellation date.
A good move as it turns out the friend could only put up a few of the boys for a few of the days. I feel good that part of the trip is under control. Now for a rental car.
Raincharm's Weblog 
Leave a Reply