Also, this query has a criterion under the Term field that will return all matching records for the summer term. For example, the following query prompts you to enter a subject code, as specified in the criteria. In that case, you can use brackets to specify a user-entry criteria field.
#MICROSOFT ACCESS QUERIES CODE#
Now, let’s say you want to be able to select the subject code whenever you run the query, without having to hard code it into the criteria field.
For example, the following query pulls all sections with a subject code of Physics or with a class type of “Lecture,” so you might have a Physics Lab or a Biology Lecture returned, among many other possibilities. If you code the criteria on separate lines in the query, they function as an OR condition. Note that the criterion under the field Schd_desc could also be coded as “Lecture” Or “Lab.” Including the * wildcard character both before and after the M and W ensures that you get TWF as well as just W. Under the Days field you would use the slightly more complex criteria Like “*M*” or Like “*W*” to pick up any record with M or W anywhere in the field. For example, under the Term field you would use the criteria Like “2014*” to select all records with a term code of 201420, 201430, and 201430 (corresponding to summer, fall, and spring terms in the 2013-14 academic year). The other criteria use the wildcard character (“*”) to select records based on additional criteria. Under the Subj_code field, include the criteria “PHYS” to select all sections with a Physics subject code. You could modify the query using the following criteria to obtain this result. Let’s say you are only interested in Physics lecture or lab sections that meet on Monday and/or Wednesday for calendar year 2013-14. This is where the criteria field comes in handy.
This might be useful for some purposes, but, generally, you probably want to select a particular group of courses to address a specific question. When running this query you would get something that looks like this:Īs it stands, the query pulls every course offered for the past several years (almost 9,000 records). Microsoft Word quotation marks do not always work in Access.)Īs an example, see the following Access query that pulls basic course data from a data warehouse created from Banner. ( Note: If you try to copy the criteria fields shown below directly to Access, you may get an extra set of quotation marks that will need to be deleted. This tip provides an overview of the criteria field in Access queries, including Flexible Criteria, Date Criteria, and Using Tables as Input. Many of us use Microsoft Access to extract data from Banner or other enterprise systems, or to store data that doesn’t fit in our enterprise system. Board of Directors and Nominations & Elections Committee.Improving and Transforming IR in Postsecondary Education.Postsecondary Data Partnership Tutorials.Statement of Aspirational Practice for IR.Duties and Functions of Institutional Research.Tab will move on to the next part of the site rather than go through menu items. Enter and space open menus and escape closes them as well. Up and Down arrows will open main tier menus and toggle through sub tier links. Left and right arrows move through main tier links and expand / close menus in sub tiers. The following navigation utilizes arrow, enter, escape, and space bar key commands.