Today I finished a report that my boss wanted as soon as possible at the beginning of the month, or at least no later than end of business on Friday. Last Friday, I mean. He even told me to get it done ahead of several other things that he wanted immediately at the beginning of the month (or at least by the end of Friday).

It took so long–beside of course the fact that it wasn’t the only thing I was working on–because it require combining multiple incongruent data sets. The primary data concerned how often we changed the promise on orders. To get the different perspectives on this that my boss wanted, it was necessary to compare the primary data with both shipment records and open order records. And shipment records and open order records are different because an order can ship several different times (as partial shipments) but there is only one record for the open (balance of the) order. All that we wanted was permanent information about the order like what item was ordered on a particular line and where the order was shipping to, but we don’t store that information by itself (where I can access it) so I have to skim it off of open and shipped orders.

And we actually store the current month’s shipments in a different place than rest of the current year. So I had to combine month to date shipments with year to month shipments and combine those (which had multiple records per line) with open orders (with only one record per line).

And that is when I found out that in our system you can book a particular line, ship it completely, then book the exact same line number for a completly different item. Not to mention that you can change the shipping information at any point–even while lines are partially shipped. And you can move lines from one site to another.

So I had to use union queries to combine the shipments records (just the quasi-static fields such as order, line, item, and shipping address), then another union query to combine that union with the open order records, adding a “preference date” (date of shipment or date of open order snapshot). Then I had to pour that into a new table,  sorted by order, line, and preference date, so that I could query and group by order and line taking only the last record (by prefernce date) for the other fields. The idea of “last by date” doesn’t seem to work if you try to do it from a query that’s sorted; from my experience the records have to appear in order in the table for the concept to work (using Microsoft Access).

Some of the data never associated at all because a promise on and order had been changed and then later the order had been cancelled (or some other exception) so that while we had a record of changing the promise on the order, I from my sources did not have any record of the order ever existing.

And then after that I had to do the actual analysis my boss wanted, including comparing my results to analysis done by my bosses’ peer, who is considered less of an expert on the data extraction, and explain any differences. And that’s when I found out Access doesn’t have built-in functions for Mode and Median–only Mean.

Yesterday I told my boss I needed to talk to him and then I told him I wasn’t sure what kind of message he was trying to send in talking about my job six to nine months down the road, but I wasn’t going to relocated and I wouldn’t feel comfortable having the company pay for my training if we were going to part ways. I think it seemed abrupt to him.

In the summer when I was talking to someone about my current job and my future career and how a different baccalaureate degree could help me get a position that I did not think I could get with my present company–but believed my company would pay for, per their policy–he advised me personally not to do such a thing. Even if the company would pay, by dint of some policy, he said I should consider if I wanted to take their money knowing full well I wasn’t going to use the training for their benefit.

I realized I agreed with him, and I had for a while been turning this idea over in my head to see if I could find a way to make it look appealing and justified. Now this present training I am trying to get paid for is not four years of college, but I found myself thinking that if the future of my job was in doubt I really needed to make sure I secured this training now to establish my future career.

But my boss said he sees my future role as a resource for all sites; special reinforcements or general substitution,  whatever may be needed. I can readily picture what that job will be like.

Today I rescheduled my travel. I was supposed to be on a plane about six in the morning tomorrow at an airport about an hour away. I spent last week hacking and snorting snot. I was at work, yes, and I was embarrassed to be so contagiously ill. I started feeling really seriously sick the Friday before and thought I was going to get over the worst of it on Saturday and Sunday. But I kept right on feeling lousy all week.

I used to save up my vacation so I could take time off if I got sick. But my boss–everyone’s boss–has really been after us to schedule all our vacation and not carry it over. So in early September most of my vacation got used up. I have a few days left, two or three, but it wouldn’t have covered for all last week. And this week.

I think I finally turned the corner (no, really, this time I really did). But I went and got a prescritption anyway. The last think I want is to still be coughing — feeling better, maybe, but still spreading germs — when I actually travel two weeks from now.

But I am guiltless on rescheduling this week’s travel. My boss already wanted to amend how long I would be down there and that was already going to incur the rescheduling fee. And two weeks later is a cheaper fare, partially offsetting the fee. The plane doesn’t come back in on Friday until about 11 p.m. but I get paid travel time.

It occurs to me that at the current air fares I probably get paid enough in travel time to pay for an earlier flight back. Oh well. The cheap tickets are partly due to the long layover in Detroit this airline is treating me too, so it’s hard to get an earlier flight without leaving midway through the work day at best.

Yesterday I helped someone check our inventory. We have plantwide physical inventory coming up at the end of the month and we are trying to clean out all the really obvious garbage from our system. By “garbage” I mean things like Part A in location 8 with a quantity on hand of 0, and in location 2 with a quantity on hand of 4, and location 5 with a quantity on hand of 326.

This fellow B. E. who works in the stockroom asked for my help getting a list of what the system thought  was in a certain area so he could consolidate things and elimate null locations and so forth. Retrieving the locations, part numbers, and (nominal) quantities on hand was straightforward, but then he wanted the results sorted in a specific way.

The location designations are in the following format: O4GH9. For our area of interest, the first letter (O) never varies and the second letter (G) never varies. Both of the numbers could be two digit numbers (4 could be 14, 9 could be 11). B.E. wanted the locations sorted first by first number, and then by the last letter and number.

For MS Access, a number is a different “thing” than a letter, a different type of data. But numerals can be used in text (as letters, so to speak). A string of characters that contains letters and numbers will be considered letters, and the numbers will not be recognized as numbers per se. What this means is that if you have three strings, 1A, 2A, and 11A, Access will consider each ‘1’ to come before any letter and before the number two, resulting in this sort: 1A, 11A, 2A. You may have seen this kind of behavior when you were working in Excel or some other program, too.

So I had to add some extra columns in my query that broke out bits of these locations and used them to sort. And I did it, pretty quickly too. Here’s what I did:

I actually constructed the second sort first because that’s the first sort he told me about, and I use a bit of the second one in the first one. First, I found the second letter that never changes by using the InStr() function, which returns the number of characters into the string that the searched character is found: InStr([location], “G”).

To get my sort key I took a piece of the string starting with that letter using the Mid() function, which begins at the specified number of characters into the string and continues for the specified number of characters (or until the end if you do not specify. I did not specify an end location and I am repeating the function above (which gives me my starting location) inside this formula:

Mid([location], InStr([location], “G”)). On our example location, this would result in “GH9.”

The second sort I built (which ultimately was the first-priority sort) was more complicated. I started with the Mid function to get a piece of the string,  skipping the first letter which was always the same (so starting on the second character): Mid([location], 2, ….

Then I needed to know how many more characters to go forward. To know that I needed to know how far away the next letter than never changed was. Finding that next letter means I need to use InStr: Mid([location], 2, InStr(…

But I don’t want to use InStr to search my whole entire location string. I only want to search the part ahead of that first letter. So that would be the entire string starting from the second charcter, using Mid: Mid([location], 2, Instr(Mid([location],2)…

And what I am looking for is that second letter that never changes:  Mid([location], 2, Instr(Mid([location],2), “G”) …

Close off our original Mid function: Mid([location], 2, Instr(Mid([location],2), “G”))

Running this gave me a result the included the second letter that never changed: from our example, 4G. This is because I was starting my search after the first letter that never changed, on the first character of the number. So 4 = 1st character, G = 2nd character, and that tells the mid function to proceed from the starting point 2 characters. So I told it to back up one: Mid([location], 2, Instr(Mid([location],2), “G”)-1)

Now I had my number, but because the numeral came out of text-numeral string, Access still thought of it as a letter. So I converted it to an integer using CInt(): CInt(Mid([location], 2, Instr(Mid([location],2), “G”)-1))

And that was easy. Today’s job was hard. So I won’t explain it.

I was going to write about a clever sort of a mixed alphanumeric string that I did, and congratulate myself (not for the first time) on how quickly I did it, but I got all confused trying to remember what I did. So that story will have to wait.

Almost every day these days I get at least one call from someone in shipping. With all the people who have left I have some of the broadest knowledge of technicalities in shipping. I was called today because an order that was supposed to be out for pick couldn’t be found, and C. M. wanted to make sure it was okay to delete it. C. M. was working with the very newly trained semi-clerks (the clerk positions are gone so he’s not a clerk) and they said they had checked both the main Orders system and the shipping software and they had no record of the shipment, nor could they find the paperwork.

I couldn’t remember the screen on the orders system that I wanted and I felt like a dunce because it was one of my favorite screens for troubleshooting. It was this cold I have, I think. I had to look it up on the cheat sheet I wrote for C. M. last week. I was going to use the screen to look up the shipment number. As I did so C. M. told me they already had the shipment number. But they didn’t; they had the shipment number for an earlier shipment on that order. “This is why we call you,” C. M. said.

Lately I have been touching a lot of old databases I have built over the years, as far back as the first year I was here. I love it. It makes me feel so special, so appreciated and needed.