Access ELF Documentation Access ELF Tutorials Access ELF On-Line Help Access ELF Downloadable Help File Access ELF FAQ VB ELF Documentation VB ELF Tutorials VB ELF On-Line Help VB ELF Downloadable Help File VB ELF FAQ
Configuration & Licensing Options
Critical Opinions
Our Users Talk Back






Examples


Our customers tell us that Access ELF is a joy to use -- once they learn how to ask the right kind of questions. The hardest part about working with a natural language interface is "getting a feel" for what it can and can't do. Access ELF can do a lot -- probably more than any NLI yet created. It dashes off SQL that high-priced database wizards might envy. But it's still not in the same league, comprehension-wise, with a grade-schooler. So you'll have to learn the limits of what's possible with this software, and what's still just a dream.

To help you with the learning curve, this section present examples -- lots and lots of examples. We'll take the usual approach, starting with the easier questions, then working our way up. For a change, we won't be limiting ourselves to the Northwind database. These questions are selected from among the many thousands used to fully test the interface each time a change or addition is made. We've selected these questions to represent both the range of what can be expressed (the sorts of SQL that come out the other side) and the range of how it can be expressed.

The sections are grouped by general type of query. This lets you see all the variants (or sometimes the rigidness of the expressions required).

This first list of questions are the sort you'd use when there's something specific in your database, and you're just trying to get it out. They're of the "please fetch this" variety.

what fonts are either square or elongated
all customers whose name contains the string "alf"
list the products costing 12.50
which customers placed orders prior to 10.1.91
employees surname begin with p
show suppliers with phone numbers starting with "(503)"
please show the copyright dates for all non-fiction works
customers whose company names start with N-Z and who are located in either the United Kingdom or Paris
everything where base bid amount > 100,000 and bidder rank = $3
find the products that have between 10 and 20 units in stock
which species lodge themselves in reef crevices?
show the phone numbers of guests in 1994 from Wyoming
show the pictures from our European vacation
list some of the rivers in Europe
show everything we have about chinese costumes
show everything concerning Kirby and his authors and their cities and titles
list the countries ending with "ia" where suppliers are located
which are our Australian suppliers?
what department does James belong to?
show catalog information for the active products.
show the employees hired before June 1996 and after June 1992
any products that come in boxes
show photos of employees hired during 1991
which of Hemingway's hardcover books have "un" anywhere in the title
list the books that are like "*Bridge"
list disability applications
show phone number for Pinnacle Building
who is our contact at The Bergman Group?
fax numbers on the customers who've had ABC-related problems
who uses the conference room?
show the registration fee + (registration fee * sales tax) for each applicant


While we're looking for stuff that's "in there", we often want to bring it back in a specific order.

list products by unit price from highest to lowest
which books cost less than Catch-22 sorting according to publication date
subtotal and customer for orders shipped between 10/1/91 and 12/31/91, sorting on the value
show the songs by the Stones, the Airplane, and Hendrix, sorted by song name
show the fees for each event, arrange the events in reverse fee order
list the suppliers in alphabetical order
list the forms with Care Claim or Care Plan in the form name, sorting by form class and form number
show the fees for each event, in descending order
show stocks in the telecommunications arena sorted by rating and safety
show customer id and name reverse chronologically by order date
show the titles in A-to-Z order
show the employees sorted by pay rate and hire date


Here are some questions that show how you might go about looking for data that's (in some sense) not there. This can be a case where something's explicitly marked as "not having" an attribuute, or it can be because the item is missing. It might entail pairs or sets of conditions that aren't met. It can also be because something has never happened, not even once.

which American artists were not trained in the U.S.A.
which italic fonts are not loaded?
which italic fonts in box B are not loaded?
give me the name and address of customers who were not given a discount
which customers have not placed 6 orders since 1992.
where are the suppliers not from Germany located
what percentage of our guests have not confirmed?
which expense claims have not yet been paid?
which customers never placed any orders
who never wrote a hardcover book
which customers have never ordered seafood
which books were never published in hardcover.
which customers were never handled by Federal or Speedy
which clients were never called or visited this month
which customers have not placed 6 orders since 1992.
who neither lives in Las Vegas nor publishes at Bantam
suppliers who are not located in Canada
who published no Connells?
show me all the authors not born in Chicago.
show all authors who have no death date
which orders were not both shipped to Canada and sent via Speedy Express
employees who live in the USA but not in Seattle


Sometimes it's half one and half the other. Find these but makes sure they're not -- at the same time -- like this.

who published Connell but not Irving
which customers ordered Longlife tofu but not filo mix
who bought high margin products but not seasoned lamb
who took databases, but not skills?


Note the distinction between these examples and the last two in the preceding list. Each order goes via a single carrier to a single country. One can't live in more than one nation or city. But you can publish authors on many an occasion. Testing that Irving was never among them is different in kind than checking a unitary value.


On other occasions, you need'll to cross two requirements, to find the set that satisfies both conditions.

who published both Connell and Irving
who does not both live in Boston and publish at Dell
which authors wrote both hardcovers and paperbacks
percent of authors that have written both paperbacks and hardcovers
for which titles are both the schematics complete and the funding campaign continues
which customers have ordered both Konbu and Filo Mix?
customers that have ordered from both Ma Maison and Tokyo Traders
which orders were not both shipped to Canada and sent via Speedy Express
which courses were taken by both Smith and Jones?
which clients both have a 1580 and use Novell
which courses taught by Rogers were taken by Tannant and Karr?
which students have taken the same courses as Jefferson sorting by StudentID
which items have the same Status ID as computer sort by Status
which exercises are the same type as neck rolls?


These next questions involve comparisons, checking some value against a limit, either supplied or implied. These are "more", "bigger" , "less" and "smaller"-type questions, but the thing to be -er than might be a fixed value, a computation, a count, or an implied field (like May Brown's salary).

show the women that earn more than May Brown
which nationality has more birthplaces than average?
which publishers handle more than one author?
give the number of titles by authors that have more books than average.
which author has more publishers than average?
which clients have more than $9 in phone charges total
which books are priced at more than 10
percent of publishers that are associated with more than 1 agent
show me all agents that have 2 authors or more
percent of authors that have published more than the average number of books
show me all employees who took two or more courses
which products are more expensive than chai
which customers have placed more orders than average
date and description of races that had more than 35 drivers
who is on more than one committee?
which customers have spent more than $500 total?
which products have less than 50 units in stock
who earns less than Spade does?
which recipes take less than 30 minutes?
which recipes take at least 45 minutes?
who earns less than Spade does?
average the prices according to publisher and whether or not hardcover
which customers have placed exactly 2 tofu orders since 1991
show me the authors who wrote at most 1 book
which customers have ordered exactly 7 products
show the products with discounts between 6 and 12 percent


Here's the natural extension of the above questions, those that feature superlatives -- the largest, the most recent, the least profitable. These can be sums, or counts, or values in the database, or correspondences between database values, and so on.

what is the cheapest item
which state has the most vice presidents?
who uploaded the most files by McCartney
which nationality has the most muralists?
which habitat has the most species
which publishers have the most books by Faulkner
which comics have the most completes?
show me the employee who took the most courses
who ordered the most granola?
which salesperson sold the most units of tofu?
which marketing workers earn the most?
which opus was written most recently
which clients have the most billable hours for last month
which target audience has the most comedies
what is the most expensive book in each category?
what was the largest individual contribution?
which client has the highest beta?
who ordered the least expensive product
which recording artist had the fewest tracks?
which customers ordered the fewest items
list the 25 largest donations
which 10 employees have the highest current pay rate?
What are the 5 most expensive books in each category?
Which 3 classes have the highest capacity?
who has the highest salary of the workers with the lowest seniority
which composer has the shortest name


Just as comparisons can be taken to the extreme limit of superlatives, questions involving the matching of two conditions (both A and B, or B but not A) can be extended to the max. This results in the "all or nothing" type of question.

which customers bought products from every category
show customers that ordered only confections during May 1991
show customers that ordered only seafood and confections during May 1991
during 1994, which customers were handled exclusively by Federal
show the customers that only rent comedies
which customers drink every beverage?


Here's the type of thing you might say when you need to find a count or a sum. It's interesting to notice, based on our user logs, that people rarely ask for counts of things that don't exist, such as "How many of our customers don't order seafood." If if involves a negative, we usually want to know not just how many, but also who.

count the customers that are in either Virginia or North Carolina
how many vendors have fax numbers
how many formats are the teen films shot in?
how many movies were shot in 32mm
how many employees in California have a college education
how many fonts are there in each box
how many hospitals are associated with NJ doctors
how many customers in each country have ordered tofu?
how many customers ordered meat in each quarter?
during 1994, how many orders for seafood were there
since 1993, how many orders for seafood were there
how many races are run on each oval, d-oval and tri-oval track
how many groups are signed by each label?
how many centimeters is the firefish?
how many colleges are there with 7 year degrees in each area code?
how many products are handled by each store?
how many members joined in each year?
how many different exercises are there altogether?
how many exercises are there of each type?


Besides counting, we can of course do other kinds of mathematical operations.

what is the median of the age of the employees?
give the average number of pages in a hardcover book
what is the average price for gift and non-gift items?


Counts and percentages can also be combined with nearly every other style of question.

how many customers have ordered every meat/poultry product
what percent of forms are Card Jackets?
what percent of the authors were from New York and wrote hardcovers
what percent of the New York authors wrote hardcovers
what percent of authors that wrote hardcovers were from New York
percent of publishers that are associated with more than 1 agent
percent of authors that have written both paperbacks and hardcovers
percent of authors that have published more than the average number of books
what percentage of researchers have Email?
what percent of dramas were produced in Boston
what percentage of Democrat senators work at Dirksen
what percentage of drivers have had accidents?
what percentage of our guests need daycare?
what percentage of our guests have not confirmed?
what percent of titles comes from each category
what percent of total sales are high margin products?
what percent of our inventory is insured?
what percentage of our members are full members?
what percentage of the time did MSFT close at or above its high?
show the 20 percent of dates that had the highest volume between 1990 and 1993?
what percent of total freight does comedy account for?


Moving beyond simple calculations, we might ask to see blanket calculations that sum, count, average or apply other aggregate functions to whole classes of data. These questions either result in side-by-side comparisons of figures, or "pivot tables" (also called crosstabs). Sometimes its hard to know which you'll get when asking the question.

compare the count of holdings showing last name and industry
crosstab ExpenseItemAmount by year of expense date and department
pivot the average unit price showing employee and product
list the number of species like "[A-C]*" showing genus down and habitat across
crosstabulate the total reps showing workout date, workout ID and exercise type
compare the number of authors at each publisher
give the sum of subtotals showing the customer name across and month of order date down during 1998

Here we're getting into the area where there are specific keyword-like rules. The special verbs compare, crosstab, crosstabulate, pivot, or tabulate must be used to trigger the distinctive pivot table grid response. That is, unless down and/or across make it crystal clear that a pivot table is in the user's mind's eye. You should also be aware that simply asking for a pivot table in this way doesn't guarantee you'll get one.. Compare the number of authors at each publisher won't generate a pivot table despite the keyword. All you'll get -- all you can get -- is a simple list of the publishers with their author counts.

Also, just because you do get a pivot table doesn't mean it will make any sense. Compare the average unit prices of products with category across and units in stock down Does this make any sense? It's hard to say.


What with everything else to consider, we forgot all the special constructions that are used to describe dates, date ranges, birthdays, anniversaries past and future. Here's a taste.

orders that were placed during the month of February 93
which customers have tofu orders at least 6 months old
which orders were scheduled for shipment during the month of September
which orders were scheduled for shipment the month before last
show sum of parts amount during each month of 1995
employee with the most recent hiredate
which orders were scheduled for shipment the month before last
which orders are scheduled for shipment the day after tomorrow
all employees who have birthdays today
show contract dates more than 6 weeks in the past
when is the next scheduled maintenance for my computer?
show contract dates more than 6 weeks in the past
all employees who have birthdays today
all employees who have birthdays this week
all employees who have birthdays on July 2
how many customers ordered meat during the week of 5/6/93?
which orders were scheduled for shipment during the week of 6/8/93
how many orders were placed in each 6 week period during 1994
which orders were two weeks ago
how many orders were placed during 1994 in each 2 week period
list orders with product name tofu during the last 17 weeks
list orders with nonnull required date ordered during last week
how many orders were placed in each 3 week period from 1/1/92 thru 5/4/94
which orders were shipped on Tuesday
which orders will be shipped on Tuesday
show orders between 1/1/94 and last Monday
show the average volume per day for each year
how many orders were placed 5 years ago today


And finally, of course, there are the keywords that tell Access ELF that the preferred response includes some sort of graphic display.

graph the number of Seattle employees against London
show the average subtotal for each category as a pie chart
chart the number of tofu or chai orders
graph the orders by category as a pie
show count of customers per country as a bar chart
plot products by supplier
draw the products per category


For more information on example queries, see the Viewing Samples section of the Query topic. The Books.mdb and other online sample databases also have many more reference samples.


Last Updated: August, 2009