Specify if capable of handling complex queries. The more complex the query is, the more comprehensive your understanding of data structure must be.
First, lets take a look at the make-up of the query.
1) This is the table you are asking Specify to look in. Fields inside that table are listed alphabetically, and have no icon on their right. Subforms have a an arrow, and any number of dots.
2) The arrow lets you know when you select this, a new table will appear in the top part of the query. Now, it's important to remember the 1st table is the one being searched. You have asked the database to tell you the name of the person who created a Collection Object record.
3) Next, we'll look at some of the variables. The 'Not' check box is how you would search by exclusion- for example, clicking the not box and typing Geigerman as the criteria (5), we'd be saying 'find all Catalogers NOT named Geigerman.'
4) Operators let you do different things with the criteria.
- Contains: look for records that contain the entry in the criteria field- NPL00012345 will give you all NPL00012345 with suffixes. 'Tho' as a last name criteria will return Thompson, Thomas, Thorn and so on.
- =: this is for exact matches.
- in: this is how you search multiple values for the field. Just separate each value with a comma and Specify will return to you all the information for those values. This is helpful when you have a series of non-sequential values. Setting the Operator to 'In' and looking for Date Accessioned (Year) 2016, 2013, 2009 would give you a list of all accessions in those years.
- between: opens up 2 fields for you to give a range. You can search for all catalog numbers between NPL00012345.000 and NPL12349.009.
5) Criteria: this is where you tell Specify what to look for. This field takes wildcards, , so if you don't know a suffix, you can enter NPL00012345.0*, and Specify will select all records that have a suffix from .001 through .099.
6) Sort: Set which field you want to be set into alphabetical or numeric order. This setting also does reverse order.
7) Here are the selection and entry
The query shown here is very simple, but if you were to speak it out loud, you's say something along the lines of "Ask the Accession table how many records it has for 2016. I want to know the accession number and the collection name.
