First Published 9 Sept 2022

This article was based on a discussion between various Access MVPs in a private email group.
The discussion started sensibly until I hijacked it with increasingly ridiculous suggestions!



I’m always amazed when I see something like this being done:



If you try to run that query, Access will inform you that it contains ambiguous joins:



When I first saw someone post something like that wondering why it didn't work, they believed this was a CROSS join query due to its appearance in design view.

Despite the name, a CROSS join is another name for a no-join AKA Cartesian join query



I often call the join shown in the first screenshot, a British Rail join based on their old logo :



The logo was often derided as 'British Rail - we don’t know if we’re coming or going' or as the ‘arrow of indecision'

Even so it lasted several decades with little change

Now called National Rail - this is the new and, in my opinion, even worse version:



However a fellow MVP then mentioned the 'twisted join' logo from a Dutch railway company, Nederlandse Spoorwegen:



I have to wonder how on earth any rail operator came up with a logo like that showing buckled rails with a crash waiting to happen

On the other hand, perhaps that’s a perfect metaphor for Access in the light of the recent spate of monthly bugs.

It could also be said the Access development remains frustratingly slow due to the need to try and keep it on the rails!

Anyway that 'inspired' me to come up with an example of a working twisted join in an Access query

The query is based on the following table joined to itself using outer joins:



I modified the original query to create what looked like the 'twisted join'.



As before this results in an ambiguous join error

Surprisingly, that query can be made to work by using a self-join query with non-equi joins and an unmatched filter.



Doing that makes the query non-ambiguous despite the opposing arrow flows. Here are the results:



Notice that the query output is identical to the table except that it is read only. Somewhat pointless you might think!

Hence. this twisted query should perhaps be referred to as a:
Non-Equi Non-Ambiguous Pointless Unmatched Twisted Self join or NENAPUTS join - pronounced NEE NAA PUTS

As I'm sure you realise, this is the sound of an emergency ambulance coming to put Access into intensive care . . .

However, perhaps an even more appropriate name is a Self Twisted Unmatched Pointless Idiotic Design or STUPID query



Anyway, here is the query execution plan using the JET ShowPlan feature



For an explanation of what this means, see my article: ShowPlan - Run Faster

The screenshot below shows how Access stores the query in the system table MSysQueries



For an explanation of what that means, see my article: How Access Stores Queries



NOTE:
No other MVPs were harmed in the creation of this article!

Clearly, I really do need to get a life . . .



Colin Riddington           Mendip Data Systems                 9 Sept 2022

Return to Access Blog Page

Return to Top