Wednesday, December 14, 2016
Saturday, November 19, 2016
- A1C (Quarterly)
- Blood Glucose (daily)
- Blood Pressure (daily)
- Pulse (daily)
- Weight (daily)
- Daily Exercise by type and location
- Duration of activities
- Mileage, when appropriate
- Medications Taken
- Calories consumed
- Carbohydrates consumed
Monday, November 7, 2016
Access is not just alive; it's growing again.
Saturday, October 29, 2016
Sunday, October 23, 2016
Saturday, September 17, 2016
Wednesday, September 7, 2016
The inclusion of the BigInt data type increases the compatibility between Access and modern versions of SQL Server. That's a good thing from any perspective.
Restoring the ability to link Access to .dbf format files addresses a HUGE pain point for a couple of industries. Most of us probably shrugged our shoulders when it was deprecated in Access 2013, but to those people who NEED this ability, it was a deal-breaker.
Thursday, September 1, 2016
Tuesday, August 30, 2016
Wednesday, August 17, 2016
- Autonumbers can be generated with gaps in the sequence
- Somewhat frighteningly, they can be duplicated.
Thursday, August 4, 2016
Today's developer community seems to have gotten the bug to search out "Best Practices". Probably they try to implement them, but that's beyond my ability to assess, so let's call that one my "Best Guesstimate on Standard Practices".
Now the bad news.
The Realm of Best Practices is in the same place you will find Nirvana, the Lost Realm of Cardolan, Álfheimr, Asphodel Meadows and a nice little cottage with a small dock by the lake.
It's possibly worth the effort to look for it if you happen to be searching for enlightenment, fighting orcs and demons, or even just waiting out eternity. Just don't give up your day job while searching for it.
Sometimes, just good enough is, well, just good enough. And all we really need to do our jobs well, I submit, is to make things work out as good as we can.
If there's a better way to parse a text string than bog-standard:
well, then, so be it. I'm not going to waste my time fretting about it. If you want to share YOUR version, I'll listen attentively and maybe even adopt it next time.
If I can get the same results with an In() clauses as I can with Or'ed parameters, I'm not going to stop and ask BingGoogle for advice. Of course, with that one, it's probably not quite so simple as all that. Sometimes an In() clause isn't very efficient. On the other hand, if my code with an In() clause works, doesn't drag like a flat tire on an overloaded hay wagon, and gets the right results, I'm good enough with that. I have a handy alternative or two in my tool kit, if need be. I'm just not going to burn my time and my client's money looking for the magic sauce of a Best Practice. Note I said "magic" not "secret". And yes, that's about how I see it. Your metaphor may vary.
Sunday, June 26, 2016
Friday, June 24, 2016
Sooner or later, they get it or they give up. Either way, the world is happier place, IMNSHO.
Sooner or later, they get it or they give up. Either way, the world is happier place, IMNSHO.
Wednesday, June 15, 2016
I love MS Access. It's been very good to me. I’ve made a living using (primarily) Access for more than a dozen years. There’s one thing about it, though, that keeps me up at night. It’s too flexible.
Unlike Ray Kinsella in the Field of Dreams, Eli Whitney or Henry Ford, most new Access users come to the task with no background in the field, no experience designing and building a prototype, or a vision and burning passion to realize their dreams to make the world a better place through the power of their creation. They just have a job to do, and most often, little time to do it.
While I do applaud the creative, inventive use of Access, I have to wonder if enough novices really grasp the significance of learning how to do it right. And by right, I mean the five Rules of Normalization. Actually, there are many, many discussions of normalization floating around on the internet. If you want to know more, just ask Bingoogle. They both know where to look.
*I’ll get some argument on this point. Access ACE is not a full-fledged RDBMS and was never intended to be. I know, I’m glossing over that fact on my way to a larger point. Take it up with me in an email; I’ll be happy to hear your opinions on the issue. I'll probably share them too. Depends on how many nasty words you use--or don't use, if you get my meaning.
Wednesday, June 8, 2016
Wednesday, April 27, 2016
Detailed Comparison of Plans
What Do You Get in an Office 365 Plan?
Getting Access Services
Wednesday, April 20, 2016
Friday, April 8, 2016
Wednesday, March 30, 2016
One of the most valuable aspects of Access Database development is that it is almost entirely custom.
That means nearly every new Access application is different in some non-trivial way from every one that preceded it--ever.
Need a database to track family addresses and phone numbers? Great. You'll find templates all over for that. But no two will be exactly alike and that's a good thing, IMO.
However, for a lot of new Access developers, that turns out to be a stumbling block to getting off the ground. If all you want to do is copy out a template and go to work, it's almost never possible to do so. Naming conventions, at the very least, are those chosen by the template designer. They may or may not be the same as those used in your organization.
And that’s just the trivial part.
You want to track one or more phone numbers for each contact. No problem, that’s a job for a related table of Phone Numbers. But what if the template maker decided every contact can have three Phone Numbers (Home, Work and Mobile)? That’s a limitation you’ll have to accept as a compromise. Or, you can modify the template.
At some point, such trade-offs and compromises become more of a hindrance to getting on with the task than simply starting out from scratch with your own design. And the more complex the business process, the more likely it will be that any template you find won’t stretch to fit it.
All of that can be summed up in the saying which is the title of this post. One Size Does Not Fit All.
You’ve been warned.
Wednesday, March 16, 2016
That's so obvious it seems silly to even comment on it in a page that's all about database design, doesn't it? But, every day, while addressing Access questions on my favorite Access forum, Utter Access, I run across questions can only be answered by looking at the specific data involved, not at the code used to manipulate it.
A recent example might help explain what I’m talking about. I’ve rephrased the question so as to avoid making it too easy to identify the source.
“My query raises a division by zero error. The query includes two calculated fields. The data is from three subqueries. The SQL for the final is shown below. How do I avoid the division by zero issue?”
Not picking on anyone, but the basic mathematician in me says “this ain’t a database question, it’s a math question. You avoid division by zero by not including zeros in the divisor.” But to the questioner, that simply hadn’t occurred, I guess. He or she was looking for “an Access solution” to the math problem.
There are, of course, two answers to this.
First, if records in the underlying tables have either Nulls or zeroes in one of the fields going into the calculation, then those values have to be resolved before you even START writing queries against that data. Exclude those records from the selection before you try to do any math on the remaining records.
There is, also in that sense, “an Access solution” to the question, "How do you handle Nulls and Zeroes in SQL so that they don’t blow up calculations?" The answers, of course, involve functions to convert Nulls and zeroes, as needed, when they appear. But that’s not the point of today’s comments.
Second, there is an even earlier, more fundamental, question to be addressed. If you are doing math (and a division by zero error definitely signals that math is being attempted), then you have to decide whether Null is valid for the data at hand, and whether Zero is valid for the data at hand. If so, then why do you then want to do math on either of those values, knowing that your calculation is not valid under any circumstances?
For example, if you want to calculate the average number of days between the date an order is placed and the date it is shipped, you have to decide, right at the beginning, if you want to try to include orders without a ship date. Those orders are still being processed and not yet shipped. I submit the answer to that one is obvious. You must exclude them because it’s only meaningful to ask about average processing days for orders that were actually processed and shipped. So, by the time you write that SQL with a division in it, there are no nulls to fret over.
Another example would have to do with calculating error rates in a manufacturing process. Let’s say you want to calculate a percentage of errors detected in Quality Control versus errors reported by customers after products are delivered. Unfortunately, if the calculation is QA Errors divided by Customer Reported Errors, it’s entirely possible (and one would have to hope, likely) to have one or more errors found in QA and Zero reported by customers for a particular product. See what’s going to happen there? Yup, a divide by Zero error.
The answer to that one is equally obvious, I think. You don’t do the arithmetic that way in the first place. You come up with a more appropriate, mathematically valid, way to calculate this metric. And how you do that is as much a business rule as it is a math problem. Maybe the next approach might be, well, okay, lets SUM the QA and Customer Reported errors first and then divide the QA errors by that total to get a ratio of QA errors. Uh uh! If there are no errors of either sort, then you’re still dividing by Zero.
At this point, I will step in and acknowledge that there is an Access way to handle it, but you can only get there by understanding the math—and the business rule—behind it.
It's all about understanding your data first. After that, it's all just code.