Access grid queries are QWE?

Question

Query Without Example....?

I’m intrigued why Microsoft touts Access’s grid query interface as Query by Example (QBE).

Where please is the ‘example’?

QBE was invented by Moshe Zloof and first published in IBM Systems Journal in 1977* with its first implementation available to IBM customers under IBM’s VM/CMS operating system soon afterwards.

It certainly used the familiar grid for users to insert query constants and formulas, but the key innovation was using an ‘example’ item (which was underlined to distinguish it from a constant). ‘Examples’ were particularly useful for joining fields. For example, I have a table with three fields and want to print (P.) the employees who earn more than their manager:

employee	manager	employee’s salary
P.	NAME	>SAL
NAME		SAL

Clear, concise and elegant.

But how would I do that with an Access grid?

* -  Volume 16, Issue 4, pp. 324-343 (1977)

Answer 1

Well, I think every PC database program from Knoweldgeman Reflex, Paradox, Revelation, dBase IV, FoxPro and <insert entire history of the personal computer industry for the last 30 years) has used the term QBE in one way or another – but I never seen that the “general” term implied, restricted to use of the term was referring to some original term cooked up many years ago by IBM.

And in fact Oracle, MySQL, PostgreSQL, SQL server, etc. again all have for EVER used the term query by example for their systems – again none ever were referring to some industry standard or propriety term that restricted the use of the term to IBM’s viewpoint.

So while the term QBE is attributed to Zloff, the general term used by every database vendor on the planet never restricted or used the term QBE to refer to Zloff’s particular approach or implementation – especially for desktop database systems like Access as opposed to server based ones as per your reference.

>>  Clear, concise and elegant

No, it not. Things always sounds simple when all the details are left out. It is ONLY simple and elegant until you share how the manager table (if one exits) was first selected? And how is that table going to be related to the employee table? (or in fact in this case not related, but the ONE value for the given manager table is going to be used as criteria for ANOTHER table).

So without how such two related tables were selected and defined into the QBE is the REAL question and meat here. (and without such details, then the “simple” part is really some imagined fantasy of simple when it is not the case at all).

Anyway, semantics aside from the above computer history lesson?

It likely would be far more user friendly to build a form or some type of UI and prompt the user. You could built the form to select the manger with the wizards and then launch the employee salary report.

I am reasonable sure that Zlfoof’z original QBE would NOT allow you to type in “text” values such as “SAL” in the salary column – you have to use the SAME datatype. In this case we talking about a number value – so you can’t just type in TEXT into the salary for the QBE, you need to provide a number. The "cool" underlining feature really only proved a few lines of “preview” data is VERY different then allowing one to mix and match data types in a QBE criteria box.

And if you going to print the results? Well, you don't really much print out query results - for printing

you are talking about a report – not a query. However, let’s use the Access QBE on a report. This is how you would approach this:

In the above you select advanced filter to launch the Access QBE. It will display on top of the report, but I moved it to the side in above and typed in a criteria > 60000.

As I stated, you can’t type in “different” data types into the Access QBE.

However, now let’s use the QBE to get everyone with a salary more than SAL. As noted, until you describe your table structures, we really can’t give a solution. But assuming that you have a table of managers?

This this would work:

So just keep in mind every product in the marketplace has its own definition of “QBE”. And the QBE from the 1970's was a challenge for aerospace engineers, let alone desktop PC users.

So from Oracle, SQL server, Access and darn near every product on the planet that has a QBE? None to my knowledge allow you to “mix” the data type in say a salary column UNLESS such a definition was created beforehand to “define” what SAL will mean. (in other words, you only gain such ease of use if you anticipated the question before hand). And I have used IBM’s “universe” system in which a natural like query language existed – but they did not use a QBE in that system!

So above shows how to answer the question of finding those with a salary > SAL with standard query designers that exist in the marketplace today.

And I used a report since you wanted to “print” the results. You can just create a query and use the above same criteria and not have to build a report.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

******@msn.com

Answer 2

Many thanks for your detailed reply Albert

I posed the question because I was working on a bill-of-materials application where - as it the nature of BOM - one doesn't just have N->1 relationships (as in employee -> manager, matrix management apart...) but N<->N (#part<-> #part) and trying to work out easy ways for users to make simple queries about sub-assemblies.

And I then remembered IBM's original VM/CMS QBE implementation of which I acquired a copy the day it was released in the UK (I was working in the automotive manufacturing industry at the time).  The implementation was unsuitable for heavyweight use but the paradigm was surprisingly easy to teach to non-technical parts specialists and expediters who had unpredictable queries about sub-assemblies that involved self-referential use of the parts database. 

My over-simplistic example was of a personnel database, where each unique #employee has associated with it their current salary and the #employee of their manager. There was no separate managers table. 

And, as I remember the implementation,  'examples' could be any underlined text string. What mattered was that the same example was used to connect fields (as in SAL or NAME).

I guess I was railing not just about Access's appropriating the term QBE but everyone else as well. 

But to me, one cannot Query by Example without Examples!

Answer 3

It not clear what database system they were running on that IBM box (I believe the first variant of what was to become DB2).

CMS is only the name of the system that allowed multiple users, not the database system which in term would use some kind of QBE to create a query on that data system.

And to be fair, such systems were not that hard to use. It is always surprising that our computer history past is full of gems and pillars that the current industry stands on. I used Sperry mapper, IBM’s U2 (universe) and a few others that included natural language query systems. In fact their underlying designs were very much like XML systems we have today. The multi-value (or so called post relational systems from that time era are exactly laid out like xml - but without markup tags.

And if there was only one table, them my above example would still work – the query for SAL would simply use the same table name.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 4

I never knew what the back-end database management system to QBE under IBM's VM/CMS was.

But it will probably have been a single-user system since inter-'machine' communication in VM/CMS was almost non-existent - as one would expect with a virtual machine.

There was comment at the time that it was a cut-down System R (DB2's forerunner). But this would imply that a QBE query generated SQL, and this would be out-of kilter with Zloof's definition of a linear syntax for QBE. It sounds inefficient, but perhaps there was even a two-stage conversion: tabular QBE to linear QBE and then linear QBE to SQL. It was certainly possible to do this since any linear QBE statement will map to functionally equivalent SQL.