A query is simply a question. In Simple Accounting your question is answered by the program giving you a set of records that answer your question, either on a browse, a report, or a graph. For example, you might want to say,
`Show me all my customers who owe me more than a $1000.00 in alphabetical order’
or
`Show me all my unpaid bills in order of urgency.’
In both cases there are two components to the question: The order in which records are displayed and the selection of records. The Query window lets you specify both elements so that the program can then either display or print the records which are the answer to your question.
In Simple Accounting the selection of records is specified with Filter Options and the order of records is specified with Sort Options.
After you select a filter and a sort, the program gathers and organizes the records according to your selections. This usually only takes a few seconds, although it could take longer if you are asking a complicated question or dealing with a large group of records.
Filter Options
The top half of the Query window concerns the filter. There are six criteria you can mix and match to display only certain records on a report or browse. Each of these six is a filter option. Filter Options are at the heart of Simple Accounting’s reporting capabilities, so studying that section in detail is very important.
Basically, the filter works by letting you enter one or more simple math expressions (dont get frightened!) to indicate the records you want to display on the report, screen, or graph. For example, you might want to see a list of all customers with Year To Date activity greater than $10,000 which we might write on paper as:
Year To Date Sales >= 10000.00
So we would click on Criteria One and select Year To Date Sales from the drop down list, select >= as our Operator, then type 10000 in the Value field.
And if we only want to see those customers with Year To Date activity greater than $10,000 and who are in the AUTO industry, we can add another expression to further target our list of valid records:
Year To Date Sales > 10000.00
And
Industry = AUTO
Now look at the example. There are a series of rows with four columns each: the first column is the Field Name, the second is a Comparison Operator, the third column is the Field Value, and the fourth column is the And/Or Operator. Each row taken as a whole is called a Filter Option. In the example above, ‘YTD’ is a field name, ‘>’ is a Comparison Operator, ‘1000.00’ is a Field Value, and the ‘&’ sign is the And/Or Operator. You can apply filtering to any or all of the Field Names displayed in the window by filling in the desired expression for one or more Field Names.
The Comparison Operator is used in combination with the Field Value to determine which records will be selected for viewing, reporting, or graphing. Only records which meet the criteria for both the Field Value and the Comparison will be displayed. Options for the Comparison Operator are as follows:
= Records approximately equal to the entered Field Value
Name = SMITH All names beginning with SMITH, such as SMITH, SMITHWICK, SMITHERS, etc.
$ Records whose field contains the text of the entered Field Value
NAME $ WILL All names containing WILL, such as WILLIAM, GOODWILL, SWILLING, etc.
< Records less than or equal to the entered Field Value
AMOUNT < 10000.00 All amounts <= 10000.00
NAME < C All names starting with A, B, or C
> Records greater than or equal to the entered Field Value
AMOUNT > 10000.00 All amounts > 10000.00
NAME > C All names starting with C, D, E,…
= Records exactly equal to the entered Field Value
AMOUNT Exactly Equal To 10000.00 All amounts = 10000.00
NAME Exactly Equal To SMITH All names which are spelled S-M-I-T-H
! Records not equal to the entered Field Value
AMOUNT Not Equal To 10000.00 All amounts not equal to 10000.00
NAME Not Equal To C All names not starting with the letter ‘C’
When a Field Value and its Comparison Operator are left blank, Simple Accounting ignores that particular Field when building a list of valid records. So if you leave all fields at their default (blank or =), all records in the data file will be selected.
Date Macros
If the field you have selected is a date field, then you have several other Comparison Operators available which function as macros; that is, they automate date entry to save you keystrokes. They are:
Current Date>= Todays date
Current Date<= Any date less than or equal to todays date
Current Date>= Any date greater than or equal to todays date
Month= Any date in which the month is equal to the value entered in the Value field
Month>= Any date in which the month is greater than or equal to the value entered in the Value field
Month<= Any date in which the month is less than or equal to the value entered in the Value field
Year= Any date in the current year
Year<= Any date in which the year is equal to or less than the current year
Year>= Any date in which the year is equal to or greater than the current year
Current Month Records in the current month for the Field (date fields only)
DATE Current Month / / All records entered in the current month
Current Year Records in the current year for the Field (date fields only)
DATE Current Year / / All records entered in the current year
Current Date Records in the current date for the Field (date fields only)
DATE Current Date / / All records entered today
For example, if you want to see all records invoiced during the month of February you would enter as one of your filter criteria:
Invoice Date Month= 2
The M tells Simple Accounting to select all records in which the Month of the Invoice Date field equals the month in the value date you entered (02)
As another example, if you want to see all records invoiced during the current year no matter what that may be, you would enter as one of your filter criteria:
Invoice Date Y=
The Y= tells Simple Accounting to select all records in which the Year of the Invoice Date field equals the current year. So for this field we do not even need to enter anything in the value field (it will be ignored even if you do.)
OK, lets look at one more example. If you want to see all records invoiced on todays date no matter what that day might be, you would enter as one of your filter criteria:
Invoice Date T
The T= tells Simple Accounting to select all records in which the Invoice Date field equals Today. Again, for this field we do not need enter anything in the value field.
Now you may be asking yourself why you wouldnt just enter your range of dates with the other Comparison Operators (<, >, =, etc.). The reason is that once you save a query with a date macro, you can re-use it without having to re-key the date ranges. For example, if you dont use a date macro, every time you want to see a list of this months sales orders you need to key in the range of dates. But if you use the date macro MC, and save the query, you can call up the query every time you need it with no re-keying.
Not to be too obvious, but you can combine date macro filter options freely with other filter options in the same query.
Joining Criteria
If you select more than one Field Name for filtering, Simple Accounting by default assumes that you wish to And both Field Names when building a list of valid records. In other words, if Field Name #1 meets the condition and Field Name #2 meets its criteria, then the record is valid and should be included in the displayed records. If a record meets the criteria for one of the filters, but not the other, then it is not included in the display. You can override this default behavior by using the Join Operator to determine how the results of each data field will be combined with those of other fields:
And Records which match this data field and records whose value matches other selected data fields. This is the default if you leave this field blank.
Or Records which match this data field or records whose value matches other selected data fields.
When printing a list of customers, you may wish to list only those customers who are not entered as ‘AUTO’ in CATEGORY #1. To do this, enter ‘AUTO’ in the CATEGORY #1 data field, and ‘<>’ (not equal) as the operator, leaving the other filter fields blank. Your customer list would then print out only the customers 'not in CATEGORY AUTO'.
You may want a report of sales comparing the performance of two sales people: John and Mary. To do this, enter ‘JOHN’ for the first EMPLOYEE data field, and ‘=’ (equal) as the first operator and ‘or’ as the second operator. Now arrow down to the second EMPLOYEE data field, type MARY, and ‘=’ (equal) as the first operator. Since there are no other fields we wish to filter, leave the second operator blank. This will yield a report including all sales credited to 'John or Mary'.
Don’t be afraid to experiment with complicated filters. Learning to customize your reports will increase the effectiveness of this program greatly. We’ll encounter the Filter Window and Sort Window used together at many points in this manual, so don’t worry if all is not crystal clear right now. The main things to keep in mind are:
- The Filter determines which records will be included, but not the order in which they will be displayed. The Sort determines the order in which records display.
- If you leave a data field blank, that field will be ignored when records are filtered. Leaving all data fields blank cancels out the filter, causing all records to be included.
- You may press bypass the filter entirely, including all records. (Be careful, though, as you may get more information than you bargained for!)
- The Join Operator connects the selected data field with the next selected data field in the filter expression. The filter defaults to And if this operator is left blank.
- As with Sort Window Options, additional Filter Window Options can be added to the program for a nominal fee, contact us for details.
PRECEDENCE: THE PROBLEM WITH PARENTHESES!
Sometimes a Query may not come out the way youd intendeded it to. Typically, what you thought was a properFilter Expression printed a report 500 pages long or with No Records Found. Usually this is because, although you entered the various Filter Options, correctly, the overall expression is not evaluating as you intended. Thats what the Left and Right Parentheses fields are for. They establish the Precedence. An example would be Show me Sales Order Lines Items with an Invoice Date of Today AND where the Salesperson was JIM OR MARIE. In our Queries, this translates to: InvoiceDate = TODAY() AND EmployeeID=JIM OR EmployeeID=MARIE of course, you know what you mean, but without further information the Query engine could decide that the you mean either InvoiceDate = TODAY() AND (EmployeeID=JIM OR EmployeeID=MARIE) which is what you want, or it could decide to evaluate as follows: (InvoiceDate = TODAY() AND (EmployeeID=JIM) OR EmployeeID=MARIE which makes no sense to you. The point is that, without a bit more help, the Query engine cannot tell what choice to make, so you may not get what you want. The solution is to use parentheses as we showed in the examples above. This creates an unambiguous Filter Expression and is guaranteed to give you the results you expect.
Sort Options
The lower half of the Query Window contains four criteria you can mix and match to determine the order in which records will be displayed on a report or browse. Each of the four is a sort option.
Sorts can have multiple components: a primary sort and secondary sorts, which are separated by a slash (`/’).
If you were running a list of customer invoices from the HISTORY menu, one of your sort options might be ‘COMPANY NAME/DATE’. In this case, the primary sort is by company name and the secondary sort is by date. This means that all invoices will appear in customer name order (the primary sort) but if there is more than one invoice for each customer, those invoices will be sorted by date.
CUSTOMER DATE
JAMESCO 01/16/96
JOHNSON BROS 12/13/95
JOHNSON BROS 01/05/96
JOHNSON BROS 01/18/96
JONES 12/14/95
Again from the HISTORY menu, one of your sort options might be ‘EMPLOYEE/PRODUCT ID/DATE’. In this case, the primary sort is by Employee ID and the secondary sort is by Product ID. This means that all items sold will appear in Employee ID order (the primary sort) but if there is more than one sale of a product per employee, those products will be sorted by Product ID and if there is more than one of the same Product ID for each employee, then those will be sorted by date.
EMPLOYEE PRODUCT DATE
JIM A12367 01/14/96
JIM X98-38902 12/13/95
JOHN A12367 12/29/95
SALLY C95ST56 01/05/96
SALLY C95ST56 01/18/96
SALLY D41004 12/14/95
Like many functions in Simple Accounting, sort options are extendable: If you need data for a report sorted in a particular order not included with your module, call us, we can supply additional Sort Window options to you at a nominal cost.
Saving Queries
When you press the [Save] button in the Query Browse you save all the filter options and sort options you have entered for that query. The next time you wish to make these same selections, simply select that record from the browse. (They are sorted alphabetically.) You can save as many queries as you like.
Editing Queries
Queries may be edited just like any other record edit in Simple Accounting by pressing the [Change] button. The really handy thing here is that you can edit a Saved Query that closely matches the type of query you need (but not quite) and simply change what you need before running the Query. You do not have to save your changes in order to run the edited query.
For example, lets say you have a query that gives you a list of sales for one employee ID JIM over a range of dates, for a particular range of products. Today you would like to run the report, the same in all respects as usual except that this time you wish to run it for SALLYs records. To do this:
1. Highlight the query as usual, before selecting it.
2. Press [Change] to edit the query.
3. Change the filter value to the right of Employee ID from JIM to SALLY.
4. Press [Proceed]
5. From the Save Queries window, press [Do Not Save] which runs the query, but does not overwrite the existing query (with JIM as the Employee ID.)
