Microsoft Access shortcomings
I sometimes like to use Access as a quick and dirty tool in order to prototype a database idea. I generally much prefer to use SQL Server for obvious reasons.
Anyway, I was mightily surprised and somewhat disappointed the other day to run into a design flaw in Access that makes using Yes/No data types a bad idea.
After asking around on irc for an answer and getting none I decided to do what I should have done from the off and Google the problem.
I found out pretty quickly that Access has a flaw in the way it handles Yes/No fields – they can be only Yes or No and not Null – this is a problem when you do an outer join and then try to group by the Yes/No field.
Anyway, a full write up of the problem is provided by Allen Browne at http://allenbrowne.com/bug-14.html
My disappointment comes from the fact that he wrote this article in 2006 and I’m using Access 2007 and this isn’t fixed.
I am guessing that Microsoft have stopped working on the underlying Jet database engine and that this is the reason this has not / will not be fixed?
There is an Access workaround which consists of using a numeric data type with constraints – that too is detailed on the link above.