What I Learned Today

Access vs. the Recursive Query

Today I was creating a tiered billing system (again in Access). The
natural solution in a (forgive the pun) normal database would be a
recursive/hierarchical query (each tier for a client has a parent tier
or is the root). Before even creating the structure for this query, I
was pretty sure that access wouldn't support such an advanced (and
until recently, also unavailable in it's big brother - SQL Server)
feature. A quick google comes up with this MSDN article from the
Access cookbook:
http://msdn.microsoft.com/en-us/library/aa188216(office.10).aspx.

The article explains a very manual way to query against a "self join"
(basically, make n copies of the table depending on how deep your
hierarchy is). This is, of course, *not* the correct way to write a
true recursive query, but it does give me a little bit of insight as
to why access/vba developers think the way they do. They are (to a
certain extent) trained into overly-simplified, somewhat limited
solutions.
They are the epitome of "good enough" solutions. Which, in a lot of
scenarios, is fine.

In the end, I added an "Order" column. Ugh.