Or rows, between and unique values in Base query conditions

Simple conditions get you a long way. Town equals Leeds, amount over 100, status is Open. Sooner or later, though, the question you want to ask is a little more awkward: customers in either of two towns, orders between two dates, members not on a list, or just each different product that has ever been sold. The query designer in Base by Zaweba handles those without asking you to write SQL.

Alongside the simple conditions, it supports Or rows, between, in list, not in list, does not contain, and unique values only.

Or rows

Conditions on the same row must all be true. Conditions on separate Or rows mean any one of them will do. That is how you ask for customers in Leeds or in York: put Leeds on one row and York on the Or row below it.

The thing to watch is that every other condition has to be repeated on each Or row if it should apply to both. If you want active customers in Leeds or York, the active condition goes on both rows. Leave it off one and you get every customer in York, active or not. It is the most common surprise with Or rows, and it is easy to check by looking at the results.

Between, in list and not in list

Between takes a lower and an upper value and matches anything from one to the other. It is the natural choice for date ranges, like orders in March, and for numbers, like prices between 10 and 50.

In list matches any of several values in a single condition, which is tidier than an Or row for each one. Customers in Leeds, York or Hull is one in list condition. Not in list does the opposite, and is useful for leaving out a few known values, such as test records or a status you never want in a report.

Does not contain

Contains finds text with a word or a few letters anywhere inside it. Does not contain leaves those records out. Use it to drop records whose notes mention a word, or product names that include "sample". As with any text search, keep the words specific so you don't remove more than you mean to.

Unique values only

Switch on unique values only and the query returns each different result once, which is what SQL calls DISTINCT. Pick the product field from an order lines table and you get a list of every product ever bought, each named once, instead of one row per sale. Pick a town from the customers table and you get every town you have customers in.

It works on the whole row, so keep the column list short. Add the order date as well and every product appears once per date, which is usually not what you wanted.

Putting them together

The real power comes from combining these with sorting, totals and parameters. A query of unpaid invoices between two dates, for customers not in a list of accounts handled elsewhere, sorted by due date, is a few conditions in the designer. Save it, and it is ready for a report, a form or an export.

If a result looks wrong, open the SQL tab to see what the designer has written. Even if you never write SQL yourself, reading the conditions back as text is a quick way to see an Or row that is missing a condition.