Access Resources

Keyboard Shortcuts

F2: Switch between edit and navigation mode (edit mode places the cursor in a cell when tabbing around, navigation mode selects all the text in a cell)

Alt + V, S: Switch to datasheet view

Alt + V, D: Switch to design view

Helpful Links

Input Mask cheat sheet

Access vs. Excel

Excel:

  • Data with lots of “one-to-one” relationships
  • No need to ask complicated questions of the data
  • Math driven projects with lots of formulas and functions

Excel is great for sets of data where there are many “one-to-one” relationships and storage of that data is your main concern (i.e. you don’t care about asking complicated questions of your data). For example, if you’re creating an address spreadsheet to help organize invitations to an event, each person has one address and asking questions of the data isn’t necessary. Additionally, excel really shines when you need to do calculations with your data. Financial planning, complex math problems, and display of numerical data in charts and graphs are some of Excel’s strengths.

Access:

  • LOTS of data
  • One-to-many or many-to-many relationships
  • Non-linear analysis of data (i.e. not just sorting alphabetically)
  • Easy data entry
  • Easy report features

Access is used to create relational data structures where there are many different tables of information linked together through relationships. This relational structure allows the user to quickly ask questions of the data that would be impossible in Excel and also allows the user to create a data structure that is very flexible. Access is designed to handle lots of information, but is not very savvy at doing more than just simple math functions. Access is very good at creating data entry forms that allow non-Access users to interact with the database and also has a very sophisticated reporting tool that allows the user to display information very easily.

Terminology differences

  • In Excel, they’re called Rows In Access, they’re called Records
  • In Excel, they’re called columns In Access, they’re called Fields

Data Entry

To do basic data entry:

  • Tab moves from left to right
  • Shift-tab moves right to left
  • Enter moves left to right.
  • Space will check or uncheck a Yes/No field
  • Remember that the different views will let you modify different things
  • Design– modify the structure and properties of the different fields
  • Datasheet– view, add, or delete records.

*Saving – Databases are different than other programs in that they are constantly saving to disk. That means that it will save as soon as you leave the record. You also have very limited (one) undo that will undo the changes made to an entire record. Notice, when you close a table it asks if you want to save the Layout of the table, not the data in the table.

Data Types

  • Text – Stores up to 255 characters. This is the most common data type. Use this to store text, like names, letter/number combos like addresses, numbers that you’re not doing math on like SSNs and ZIP codes. Make sure to set the Field Size option to the smallest amount of characters you’ll need. This reduces file size and helps with automated things like Form Wizard.
  • Memo – Stores 65,536 characters. Use this type for text that doesn’t fit in a Text data type. When sorting this field, only the first 255 characters are used.
  • Number – Use this to store numbers you will do calculations on (like sums, +,-,*, /, etc.). The default field size is Long Integer, which does not allow decimals. For floating point numbers, use Double. To reduce file size, you can use Byte for numbers <255. Also use Number (with Long Integer) for foreign keys.
  • Date/Time – Stores dates (and times!). Computers store dates as the number of seconds from a certain point in time (1/1/1901, eg), therefore no date exists without a time (default 12:00 AM), and vice versa. Set the Format option to change how it displays by default. Custom formats can be defined as well (F1 from Format box, click on Date/Time Format link).
  • Currency – Use this for moneys. This is actually just a Number with Field Size set to Double, Decimal set to 2 and Format set to Currency, but it’s easier to just choose Currency.
  • AutoNumber – Use this to make primary keys. These fields will automatically populate with a unique number when a new record is created. You can choose serial numbers, i.e., 1, 2, 3 (Increment option) or Random under the New Values property.
  • Yes/No – Use these fields for Boolean values, i.e., values that are either true or false (like, “Received”, or “Alive”). You can also change this to True/False or On/Off with Format.
  • OLE Object – Use this to store actual files in the database. One of the best uses for this is to store images along with a record, for instance, a picture of each employee. (FYI, pictures must be in BMP format if you want to display it in the form) You could also store Excel files, Word files, sound or video clips, etc. (The last two would have to be opened by an external program.)
  • Hyperlink – Takes any text you enter and turns it into a hyperlink. Yeah!
  • Lookup Wizard – This turns the field into a Combo or List Box. You can do this manually as well under the Lookup tab under

Building a Table in Datasheet View

  • Click on the column, choose that datatype, and then give the column or Field a name. Normally field names should not have spaces, so you can use camelBack or underscores (camel_back).
  • Each record has an automatic number in the leftmost column, known as the primary key, or a unique identifier for each record. You can use Access’ autonumber, or you can change the datatype to number and enter in the number yourself.
  • Examples: student ID number, social security number, license plate, bank account number, product ID, etc.
  • Note usually these ought to be autonumber or a number so that they can match up between tables.
  • You can also go to the Field Tab and insert automatic fields, like Address, Phone Number, etc.
  • You can change any field data type with the options in the Add and Delete group.

Building a Table in Design View

  • Type the names of the column headings in the first column (the top one is the primary key). Choose the data types in the second column and add the description in the third column.
  • Change the field properties in the Properties panel below. Use the General table for the most common ones and use the Lookup Tab to create a lookup query for one of your fields (you can also use the Lookup Wizard in the datatype column).
    • It’s usually a good idea to make lookups before you make relationships, because otherwise it will require you to delete the relationship, and then make the lookup.

Field Properties

  • Field Properties – It really helps to set these properties early. If they’re set, automatic features like Form and Report Wizard will use them as defaults when creating forms and save you lots of time.
  • Field Size
  • For Text fields, sets the maximum number of characters the field can hold. Keep this to a minimum to reduce file size.
  • For Number fields, sets the number of bytes used to store a number.
  • For numbers <256 use Byte, for other integers (whole numbers) use Long Integer, for decimals use Double.
  • Field Names – Good idea to make them one word (use _ two separate words, etc.). This will save you time later because with two-word field you have to manually type square brackets ([]) and you don’t with one-worders. One-worders are also more compatible with other database systems. Also a good idea to have a consistent naming scheme, so you aren’t guessing all the time. (See Caption below)
  • Format – Use to set the default display of this field. Used mostly for Date/Time and Number/Currency fields, but can also be applied to Text, Memo, Hyperlink and Yes/No fields.
  • Caption – Use this to set a display name for the field. This is the name that will be shown in the field selector and labels on forms and reports. This allows you to have one-word field names, but two-word display names.
  • Default Value – This value will appear in this field for any new record. Does not put this value in blank records that already exist.
  • Validation Rule/Text – Use these to check the data against criteria. If it doesn’t validate, the Validation Text is displayed. (See section on Forms for more info.)
  • Required – The use cannot leave a new record until something is entered in this field.

Two ways two name objects in Access:

  1. Standard programming conventions
  • Includes prefixes on object names
  • All words start lowercase, no spaces or punctuation
  • For example: tbl_students; qry_birthday; frm_addStudent; rpt_classRole
  • Useful if you are going to be getting into advanced databases heavily programmed with Visual Basic
  • If not using VB, it is still good and appropriate to use because it is so common
  1. Access 2007 and 2010
  • Standard English, punctuation allowed
  • Do not identify object type in name
  • For example: Students, Birthdays!, Add Student, Class Role
  • Easier to use, understand, and looks less advanced – not as intimidating to inexperienced users
  • Can potentially cause problems in Access 2000 or earlier databases – if Visual Basic is used.

Both kinds of naming conventions are appropriate for almost all of our students. If the topic arises in class, explain that different instructors, and even database designers, have their preferences; either is acceptable. For this class, the example files use the newer naming conventions just because it is easier. Sometimes, when using the more conventional naming conventions, students miss the point anyway and name things like this: Table students, or AddStudent Form. This is an incorrect way to name objects, but not necessary to stress in this foundations class. Other database software such as Oracle, mySQL, etc. have stricter naming conventions.

Primary Keys

A table’s primary key is a unique identifier for each record in a table. It MUST be unique to each record in order for it to work properly. For example, a person’s first name would not be a good primary key because there is a possibility that there is another person in the same table with that same first name.

To set a Primary key, select the design view of a table, click on a field name, and then click on the “primary key” button in the ribbon.

Foreign Key

A foreign key is a primary key from another table. In one to many relationships, the primary key from the “one” end of the relationship travels to the table on the many end and becomes a foreign key.

Relationships Between Tables

Access allows us to create relationships between tables and thus tell Access what things are related to others. These relationships MUST be of the same data type, the one exception being matching AutoNumber with Number.

  • To set relationships, click on the database tools tab on the ribbon and then click on relationships. There are several types of relationships:
    • One to One–this type of relationship usually means that that data should go in one table (i.e. for every person there is one address or phone number you want to keep track of). Using an Excel spreadsheet or an Access Table is to use one to one relationships.
    • One to Many relationships–the most common type of relationship, created when you connect a primary key from one table to the foreign key of another table.
    • Many to Many relationships–require a junction table, or a table in between two tables in which their primary keys can be matched as foreign keys.
  • You usually should check “Enforce Referential Integrity.” The following rules apply when you use this.
    • You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you cannot have an order that is assigned to a customer that does not exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
    • You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
    • You cannot change a primary key value in the primary table, if that record has related records. For example, you cannot change an employee’s ID in the Employees table if there are orders assigned to that employee in the Orders table.
  • If you click to select the Cascade Update Related Fields check box when you define a relationship, any time that you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records.
  • If you select the Cascade Delete Related Records check box when you define a relationship, any time that you delete records in the primary table, Microsoft Access automatically deletes related records in the related table.
  • Join Type
    • Option 1 defines an inner join. An inner join is a join where records from two tables are combined in a query’s results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.
    • Option 2 defines a left outer join. A left outer join is a join in which all the records from the left side of the operation are added to the query’s results, even if there are no matching values in the joined field from the table on the right.
    • Option 3 defines a right outer join. A right outer join is a join in which all the records from the right side of the operation are added to the query’s results, even if there are no matching values in the joined field from the table on the left.
  • See <http://support.microsoft.com/kb/304466> for more about this.
  • See Lynda video-Building Structure.

Input Masks

Input masks are used in order limit the kind of data that users can enter both in type and length. Additionally, Access will give you the option of storing the data with or without the formatting options. For example, if a user selects phone number, they can either choose to store the data as “(801)654-3210″ or “8016543210″ but the input mask would display both as “(801)654-3210.”

Go to design view and choose the from preset input mask options, or enter these characters manually.

  • 0: indicates numeric data, entry required
  • 9: indicates numeric data, entry optional
  • L: indicates alphabetic data, entry required
  • ?: indicates alphabetic data, entry optional
  • >: forces uppercase
  • <: forces lowercase

Validation Rules

  • A validation rule limits or controls what users can enter in a table field or a control (such as a text box) on a form.
  • You can create either a field validation rule or a record validation rule.
    • Field validation rules
      • Use a field validation rule to check the value that you enter in a field when you leave the field.
      • For example you can enter a rule such as >100 And <1000 in the Validation Rule property that will force the user to enter values between 100 and 1,000.
      • Or suppose you have a Date field, and you enter >=#01/01/2007# in the Validation Rule property of that field. Your rule now requires users to enter dates on or after January 1, 2007. If you enter a date earlier than 2007 and then try to place the focus on another field, Access prevents you from leaving the current field until you fix the problem.
    • Record (or table) validation rules
      • Use a record validation rule to control when you can save a record (a row in a table). Unlike field validation rules, record validation rules refer to other fields in the same table. You create record validation rules when you need to check the values in one field against the values in another.
      • For example you could make a rule such as [EndDate]>=[StartDate] forces users to enter an ending date that occurs on or after a starting date.
      • Or suppose your business requires you to ship products within 30 days and, if you don’t ship within that time, you must refund part of the purchase price to your customer. You can define a record validation rule such as [RequiredDate]<=[OrderDate]+30 to ensure that someone doesn’t enter a ship date (the value in the RequiredDate field) too far into the future.
    • You can also add validation text to help your users understand what they must to satisfy your validation rule.
    • Entering text such as “Enter values between 100 and 1,000” or “Enter an ending date on or after the start date” in the Validation Text property tells users when they have made a mistake and how to fix the error.
    • See http://office.microsoft.com/en-us/access-help/create-a-validation-rule-to-validate-data-in-a-field-HA010096312.aspx  for more details.

More Field Properties (Don’t feel you have to cover all of them)

  • Lookup Tab – Use this to manually turn the field into a Combo or List Box. These settings can also be set using the Lookup Wizard “Data type”. (See the section on Forms for more info)
  • Allow Zero Length – For Text, Memo, and Hyperlink fields, allows a zero-length string to be stored instead of Null. In short, turn this to Yes when you want the field to be required (i.e., they have to type something) but you want to allow them to type just a space to show “nothing”.
  • Indexed – Creates an internal mechanism on that field that speeds sorting, grouping, and querying. Also useful for enforcing uniqueness to the field (“No Duplicates”). It’s a good idea to index common search fields, like IDs, or maybe Last Name.
  • Smart Tags – These are the annoying little boxes that pop up and ask you if you want to look that person up in your address book, or check your Outlook calendar for that date. To add a smart tag to the field, click the ellipses.
  • Decimal Places – For Number and Currency fields, it set the number of decimal places (obviously). However, setting this property does nothing unless the Format is set to something other than blank or General Number. Note that for any Integer number, all decimal values will be rounded away, because integers are by definition whole numbers (use Double instead)

Importing into Access

Spreadsheets

  • Before importing spreadsheets, it will be to your advantage to format the spreadsheet to make the import easier. Starting in the top-left, the first row should have column headers that are the same as the Fields in Access, then each row after that should have a record of information. No skipped rows, no extra titles, no junk off to the side, etc.

To import to a fresh table, choose the External Data ribbon and select Excel.

  • Browse to the proper file
  • Select Appropriate destination for the data
  • New sheet in existing database
  • Append to sheet in current database
  • Link to the data source by creating a linked table (this will create a dynamically updated table)
  • Choose which sheet has the desired data. Should be obvious.
  • Check the box to turn the first row into field names.
  • For each field (click on them) decide on a field name, whether to Index or not and whether you want it at all (skip).
  • Highly recommended to “Let Access add primary key”, but you can choose your own or have none if you really want.
  • Name the table and hit Finish.
  • Once the table is imported, open it up in design view and make the necessary adjustments. Some things you could do:< >Check the field sizes of text fields. Rename the ID field to differentiate from other soon-to-be-made IDEnter Captions for each field.

To import to an existing table:

  • Be sure that the Excel Column Headings are exactly the same as the Field names the students made in Access. Even the slightest incongruence will cause the import to fail.
  • It’s a good idea to remove the primary key field in the spreadsheet because they will not import if the values from your spreadsheet already exist in your database.
  • Choose the file you want to import from
  • Choose which table to append (or add) the data to
  • Choose the spreadsheet you want to import and click Next and Finish.

What is a form?

A form is an intermediate step between users of the database and the database itself. It is used primarily as a user-friendly data entry and record lookup interface. In essence, it’s a fancy-looking way of displaying and entering data.

Forms and form items have either a “Record Source” or a “Control Source” (respectively) that determine how they interact with tables.

  • Control Source: Is different for every field in a form. The control source is the information that the field is pulling out of the table or query. To specify a field you must first put =[fieldname]. You can add calculations to a field’s control source.
  1. Select the specific field that you want to change in the form.
  2. Press F4 to open the form’s Property Sheet. Then click the all tab to display the all of the form’s properties.
  3. Type in the new control source.
  • Record Source: Is the table or query that the Form is based off of. < >Select the entire form by selecting the box in the upper left hand corner. Press F4 to open the form’s Property Sheet. Then click the Data tab to display the form’s data properties. Click the Record Source text box and then use the drop-down list to select the table or query you want as the form’s new Record Source.

Auto Forms

  • You can make an autoform from a Table or a Query, depending on the data you want to enter.
  • Click the table or query, and under the Create tab choose Forms
  • Access will generate an automatic forms with all the fields in the given query or form.
  • All field properties (like lookup columns, Currency, etc.) will appear on the form
  • You can navigation through a form with the arrows in the bottom left and use the search box to search the fields.

Data entry with forms

  • The arrows at the bottom of the screen allow the user to navigate between records and create new ones
  • Any time the user navigates between records, Access automatically saves any changes in data

The Form Wizard

  • Create> Forms group> more forms> Form wizard
  • This allows you to insert fields from many different tables or queries
  • If you do use multiple tables’ fields, you have the option of inserting Subforms

 Subforms

Subforms are basically forms or tables that are embedded in another form. They are usually used when you want to modify both ends of a one-to-many relationship. There are three basic ways to add a subform to an access database:

  1. Drag an existing form onto the current form.
  • This usually works when you have created autoforms from tables and then drag  another autoform or just a table into Design or Layout view.
  • If the form is based on a query, it might be a good idea to use the subform wizard so that you can be sure you match the keys correctly.
  1. Use the Subform/subreport wizard.
  • In Design view, click the Insert Subform button on the Design Tab and the Subform Wizard will appear. Choose whether you want your new subform to come from the list of forms, or whether to create one from a Table or Query on the next page of the wizard.
  • If Table/Query is chosen, choose the fields you wish to include.
  • Access offers to show the data in your subform according to the Primary key in the main form. Usually it is correct, but you can also choose Define my own and match the keys yourself.
  • Give the form a name and insert it.
  1. Through the Form Wizard
  • As stated above, when you use the wizard to create a form and insert fields from multiple tables, Access gives you the option to insert one as a subform.

Blank forms

Blank forms are just that: blank. You must then specify what fields you want to include.

  • Click on Create > Forms Group > Blank form
  • A New, blank form will appear, along with the “Add existing fields” button.
  • You can select any field from the “Field list” that appears on the right of the screen.
  • Once you’ve selected an initial field, its table becomes the form’s Record source
  • Tables that are related to your record source will be displayed as “Fields available in related tables” < >Note that these are related tables If relationships have not been set up, then no other tables will appear
  • The rest of the tables will appear at the bottom of the Field List pane as “Fields Available in other Tables”
  • If you want to include indirectly related tables (i.e. there’s an intermediate table that connects the two, Add a field from the connecting the table that contains that field will be moved to “Fields available in this view” All tables connected to THAT table will be available in “Fields available in related tables”
  • You can now delete any fields that you don’t need on the actual form itself, but the indirectly related table will still be available for use.

Formatting options

  • By Default, fields are grouped and move together
    • For example, fields that are vertically stacked will keep the left and right sides of the text box or label even
    • Fields that are horizontally aligned will keep the same height.
    • You can remove the default layout by selecting a field, going to  Design view, Arrange, contextual tab, Table group, and choosing the “Remove Layout” button.
    • After you remove controlled layouts, the brown boxes in the upper left hand corner of the element will allow you to move it independently of any associated other elements.
    • You can put the controlled layouts back on by selecting multiple elements and clicking on stacked or tabular.
  • You can resize fields by selecting them and grabbing the sides or corners to adjust.
  • You can merge or split cells within the layout to add more fields to your form.
  • You can also add margins and padding to a field.

Filters

  • You can add filters to a form just like you can add filters to a table. Simply select the field and click on Filters on the ribbon, or right click on the field itself. From there you can choose to sort alphabetically or numerically, or simply filter by/out that individual record.

Buttons

  • You can add a button in Layout or Design View, but the wizard only seems to come up from Design view.
  • In the wizard you can choose from the commands offered, or you can choose to run a Macro that you made.
  • You can also not use the wizard and just open the Macro Builder from the Properties Sheet under the Event Tab.

Combo Boxes

  • There are three types of combo boxes that you can create.
    • You can add a combo box that will get the combo box to get the values from another table or query. This can actually store the chosen value in the field you choose in the database.
    • You can type in the values yourself.
    • You can also add a combo box that will update a form based on the value selected in the box. This can be very useful when you want to lookup a particular record in a form, but don’t want to have to search for it.
      • For example, let’s say you have a form to show the instances in which a book has been checked out. You want to find the data for a specific book but don’t want to search through your records to find it. You can create a combo box that will choose book and update the form according to the book chosen.

Navigation Forms

  • Simply drag forms or reports into a navigation form and the tab desired.
  • You can have different side tabs for each top tab
  • To rewrite the macro:
    • Go into the macro builder. In the first line where the if statement is, there should be an expression builder icon click on it.
    • Make a new line. In the builder below look for the navigation form , and the Navigation subform inside of that. Double-click on the navigation subform. Then in the expression above right after [NavigationSubform], type “.form” double-click on “form” when it pops up. Then type an “!” then copy and paste the last part of the original macro at the end.
    • Here’s a video go to Time: 8:00 min
  • If your forms have a macro assigned to them but you don’t want to rewrite the macro for the navigation form, create a button that will open the form in a dialog window.

Queries

Queries are questions that you ask your database. Queries provide a structured method for users to pull out specific information from the database. For example, if a user wanted to know how many students are in a particular class, they would design a query that would filter all records that didn’t match the criteria of a particular class.

Queries in Access are much more powerful that the filter and sort options found in Excel. Queries function as separate objects in Access and so they can be referenced easily by reports and other objects and used as comparison tools in order to add to, delete from, and update existing tables.

Design grid options

  • To start things off, you need to “show” Access what tables you want to draw data from.
  • In the Query tools> Design contextual tab in the query setup group, click on the “show Table” button
  • Double click on what tables you wish to show in referencing your query.
  • Tables will be related according to how the tables are related in your database, but you can also create temporary relationships, edit or delete the relationships specifically for a query (these changes will not affect the relationships between the tables in your database).
  • Double click on the fields you wish to include in your query
  • The following are many different kinds of things you can do in the Design Grid:

Show/hide

  • You can elect to not show certain columns in your final query data sheet
  • This can be nice if you want to sort by a column or want to look up something based on a column, but don’t want to show the column in the final markup

Sort

  • You can sort ascending or descending
  • “A” is treated like 1 and “Z” like 26, so ascending will put things in alphabetical order

Criteria

  • Criteria are the parameters that are used to find specific data in your database. In essence, you are having the following conversation with your query:
  • Query: “Hi. What do you want me to find?”
  • You: “I’d like you to find every record where (criteria) is true.”
  • Query: “Okay.”
  • <several milliseconds later>
  • Query: “Well, I found (insert a number)of records where (criteria)is true.”
  • Criteria also allows us to set certain conditions with various types of operators. Criteria in the design grid are read from left to right. As you read the criteria on a same line, put an “AND” statement between each one in order to get a good idea of what’s going on. If you add additional criteria on subsequent lines, read them with an “OR” statement between them.
  • Operators:
    • = — equal to
      • — greater than
    • < — less than
    • “between” lets you choose something between two extremes (numerical or alphabetical)
    • “and” must meet more than 1 criterion
    • “or” can meet either criterion
    • Wildcards
      • (any # of any characters)
      • ? (any single character)
      • # (any single digit)
      • Because wildcard characters , such as *, are treated as literals, you cannot use them with the Between…And operator. so you can’t say things like Between “I” and “R*”
  • If you want to reference a field within your criteria, put the name of that field in square brackets “[example_field]”
  • Criteria can also take advantage of “pseudo fields” or fields that do not exist in the tables that you have chosen to show within your criteria. If Access encounters a “field” that it can’t find in the shown tables, it will prompt the user for a value which it can then use in pulling out matching records. For example, if you were to run the following query, Access would not have any field named “Name?” and so it would ask the user for a value it could use as that criteria.
  • Random Note–if you want your query to return unique values, known as Distinct in SQL, choose Unique Values–Yes in the Field Properties for the query. Make sure that it is Query properties and not field properties.

Totals

Totals rows are not displayed by default in the query design grid

  • To enable the totals row, click on the totals button in the show/hide group of the design contextual tab
  • The data type of a given field will determine what type of totals will work best

Expressions

  • Expressions are used to do simple math or to combine fields. They are created by typing in the name of the expression (i.e., the eventual name of the field in the datasheet view) followed by a colon. Some expression can also be entering in as criteria.
  • Most of the time you can use the Builder to make the expression. You can find many functions to build you expressions. They can be found in Functions, Built-In Functions, and are sorted by category. There are many functions and Access gives a brief explanation for each and a link to Help. Here are some common ones:
    • Concatenation: FullName: [First_Name] & ” ” & [Last_Name]
    • Date()–returns today’s date.
    • Now()–returns today’s date and time
    • DateDiff(«interval, such as year(yyyy) month(m), day(d)l», «date1–first day you want to use in the calc», «date2–second date you want to use in the calc», «optional: firstdayofweek», «Optional: firstweekofyear»)
    • Month()–returns the month number of the field
    • MonthName()–returns the month name for a given number 1-12
    • DSum(«field», «table/query», «optional criteria»)
    • A regular IF statement–IIf( argument, result if true, result if false)
    • DateAdd( interval of time (d, yyyy, m, etc.), number of intervals to add or subtract, Date to add or subtract from (could be a field name or Date(), etc.))

Delete Queries

  • A delete query will delete records from your database depending on the criteria you select. Simply change it from a regular query to a delete query in design view.

Update Queries

  • An update query will update the records in your database based on the criteria you select. Simply change it from a regular query to an update query.

Crosstab queries

  • In a crosstab query, you can add fields to the rows and columns of the query to compare the data and perform calculations like Sum, Count, Average, etc. You can add up to three fields for the rows and one for the column heading. Then you can choose the calculation.

Auto Reports

  • Click on a table or query in the navigation pane to the left (it will be highlighted in orange when you do this)
  • Click on Create Tab > reports group > Report button
  • Voila!

Report Wizard

  • Click on Create tab > reports group > report wizard
  • Select the fields that you want to appear in the report
  • Select any grouping levels and add them to the report with the arrows
  • You can find more options by clicking on Grouping options…
  • Add any sorts that you want
  • Select the layout of your report
  • Choose a title for the report.

Blank Reports

  • Click on Create Tab > Reports group > Blank Report button
  • Add any Fields that you need to the report

Group and Sort options

NOTE — It’s easiest to do grouping and sorting in Layout View.

  • Click on Design contextual tab > Grouping & Totals group > Group & Sort button
  • This will open up a pane at the bottom of your screen with two buttons: add a group and Add a sort
  • Click on either of these and select a category to either group or sort based on.

Additionally: (see the picture below for corresponding numbers)

  1. You can change the units that the report sorts or groups based on.
  2. To Change the hierarchy of the grouping and sorting, click and drag the four dots on the left side of the group and sort rules
  3. To delete a rule, click on the “X” in the upper right corner

General Formatting Options

  • Formatting for reports is basically the same as the formatting for forms.
  • There is one significant difference, however, and that is the different headers and footers that Reports have.
    • Report Header appears only at the top of the entire report
    • Page Header appears at the top of every page
    • There can be other “header” sections that represent groups that you have created in the report. They repeat based on how many different groups you have.
    • Detail is the part of your report that repeats based on how many records you have displayed in the report
    • Page footer appears at the bottom of every page
    • Report Footer appears only once at the bottom of the report