Header Image
Home / Skills: Access

Skills: Access

"She says we have to conform to their format."

Andrew was telling me about his discussion with our partner who was the lead in the outsourcing contract that we were on. We were talking about the format of the invoice. At the time of the discussion, we had already been delivering service to our client alongside our competitor for a couple of years but this year, we were to act as one vendor, with our competitor acting as the lead.

The AR lead was trying to get everybody to submit the same kind of invoice. The problem was our competitor's invoice did not conform to the stipulations of the contract and the numbers didn't make sense. It was not clear how the numbers were adding up. Andrew and I were stunned that even with the argument that the contract stipulated a certain format (which was a very clear design), our AR contact was obstinate that we do our invoices their way. Our competitor tended to be very arrogant and pushy, and with them being the lead meant we had no choice.

I told Andrew, "Okay, let's do it their way, but let's use Excel as a temporary measure, kind of a copy and paste measure. We continue to use our Access because it's a database and can hold the data but we use Excel as a copy and paste measure. I think after BP sees our "new" invoice, they will tell her that the invoice needs to match the terms of the contract and to use our format instead."

And sure enough, after we sent BP the new invoice, we got complaints immediately that it did not make sense, the numbers didn't add up, and to go back to the format we had. After much discussions with BP and then after discussions between BP and our competitor, we all went to the format that we had been utilizing.

Why did I use Access for the invoice? Because of two things: 1) Access can hold more data than Excel can (Excel is not a database). The financial data would grow to a year's worth of data, composed of multiple sites and multiple kinds of services. 2) Access can easily incorporate new services or sites without any formula set ups. You just add a site or service to a table and the data starts to roll into the invoice. With Excel, you have to set up formulas which can be tedious and error prone if you have to make constant changes.

Today, I use Access whenever there is too much data for Excel to handle. I first try to use Excel so that others can use the file but if the data is too much or the number crunching involves numerous formulas that Exel starts to hang up, then I start using Access. It does take a little upfront effort to set up the "formulas" in Access to do certain things that I want it to do, but once I've done it, the calculations run so much faster. There is just no comparison. Then once Access is set up, I can use Excel's pivot tables to retrieve the data from Access.