19Access Queries

  1. What is the Query Wizard?

The Query Wizard automates the process of setting up the query’s structure. By selecting a record source, choose the sort and filter options, and the wizard takes care of the rest. The wizard can also help to create other types of queries: crosstab and action queries

  1. What is Design View?

Design view allows you to be in more control of the queries. After selecting a record source, drag the fields you want into a grid. You are also able to enter selection criteria for filtering data and the formulas for the performing calculations.

What are Queries?

Queries is a way to search for and compile data from one or more tables. When running a query it provides a thorough search throughout your database. When building a query in Access you are requesting specific search criteria to find the exact data you are looking for.

Create a Query with Two or More Tables

You can create a query that gathers data from multiple tables or queries. When gathering data from tables and queries, the data should have a relationship.

1

  1. Open tables/queries wanted in the Query Design view.
  2. Choose field names to retrieve data in the order you want to retrieve them
  3. Now choose the field names you want to sort, in the order of them to sort. Under fields choose whether to sort data by Ascending or Descending
  4. Enter selection criteria, not always needed.
  5. Make sure to unselect the Show button on column not wanting to be displayed
  1. Click Run. Access will now retrieve the information for the columns and display the rows in the order they were indicated.

Access iif Function

Access IIF function takes an expression and returns with whether it is true or if it is false.

The syntax that is used to determine whether the expression is true or false is:

Iif(expression, truepart, falsepart)

This function with go through both the truepart and the falsepart even though you will only get one answer. Knowing this, keep an eye out for possible errors that can occur. Sometimes it will return saying the expression is false when in fact it is true.

What are Macros?

A macro is a series of commands that an application program executes. Macros can help automate tasks that are within a program such as Access. Macros gives you the ability to program your software without requiring one to learn or understand the basics of having to write a program.

Create a Macro

  1. Click the create tab in the Ribbon, click Macro
  2. Use dropdown menu to select OpenForm
  3. Select the form name you want to open
  4. Select you next action in the dropdown menu.
  5. Click Save and name your Macro.

This is to be deleted!!

(This is the text for the Create Macro. Not sure how to make it exactly work without pictures. Need to add some information if pics can be added.

Ensuring that the CREATE tab is selected from the Ribbon, click Macro:

Use the dropdown menu to select your first action – choose OpenForm:

In the bottom pane, select the form name you wish to open – choose Customers Form (our only form):

Use the dropdown menu to select your second action – choose GoToRecord. In the Record field, choose the record you want to go to – choose New. What we’re doing here is ensuring that the form doesn’t open up a previous record – it goes straight to the end and has a blank record ready for you to enter a new record (this saves time and unnecessary clicks):

You’re now ready to save your macro. Click on the Save icon and enter a name for your macro. Make sure you name this macro AutoExec. By naming it Autoexec, we are instructing Access to run this macro everytime we open the database.)

Run a Macro

  1. Go to the Navigation Pane, double-click the macro name.
  2. On Database Tools tab, in Marco Group, select Run Macro, select the macro in the Macro Name, select OK.
  3. Macro open in Design view, select Run on the Design Tab, in Tools group. Open Macros in Design view by right-clicking in the Navigation Pane, Select Design View

Access Parameter Query

An Access parameter query, is a query that is simple and can be the most effective query you can create. Because these parameter queries are simple data can be updated easily to reflect a new search term.

What To Do and What Not To Do with Parameter Queries

When the parameter query is being designed make sure that the prompt is not the same as one of the field names. For example, if you use this for the parameter [LastName] to prompt a user to enter a name into the LastName Field, it will not work. This is because Access uses the square brackets for the field names to process the calculations in queries. A way around this is to add a question mark [LastName?].

Access Form

Access forms are used to make it easier to view or retrieve items that you want. Forms allow you or others users to add, edit, or display data that is stored in an Access database, the way the form is designed is an important piece. Well-designed forms are essential in providing efficiency and data entry accuracy, especially for multiple users.

Creating an Access Form

The form wizard is very useful for creating Access forms and can save time. Here is how to create a simple Access Form

1)  Go To Forms section in Access

2)  Double-click “Create form by using wizard”

18

3) Select tbl_Sales from the drop down box “Tables/Queries

27

4)  Depending on the fields you want to see, you can either click the single arrow to add selected fields or click the double arrow to add all fields.

33

5)  Select the Columnar layout, click Next

6) Select any style and click Next

7) Change the Title to what you choose.

41

Click Finish

Now open you form and check it out!

            51

Four Different Form Formats

  • Single Table Form: Just as it sounds, it is a single form that is corresponding to a single database table. This form is functional, but basic and can be used to complete different tasks.
  • Single Table Form with Lookup Field: Similar to the Single Table Form, but the lookup field is what allows us to display data from another table or database, or a project that has summarized values of a data range.
  • Master/Detail Form: Master to Subform relationship i.e. one master form directs many subforms (Not sure how to reword)
  • Master/Detail Form with Lookup Field: This is similar to the master/subform relationship, however it contains the lookup fields in either the master or the subforms.

Access Reports

Data that is stored in Access in great for maintaining a database, but when if you want to share the data or being able to view the data away from a computer, it is not possible. Access Reports allow you to design a ready-to-print document with information you would like to see.

Create a Report using Report Tool

The Report tool is the quickest way to create a report. This is because it produces the report instantly without prompting for information. The report will display all the fields from the table or query. The Report tool will not always create the final product, but it will provide a quick look to the essential data. If you wish you can save the report and modify it in Layout or Design view to better service your needs.

  1. Click the table or query of the report you want produced
  2. On the Create Tab, under Reports group, select Report.

Access will build the report and display in Layout view.

You can choose to save the report so the next time you open the report Access will display the most recent data.

Create a Report using Report Wizard

The Report Wizard allows you to be more selective about the fields that will appear in your report. Specify how the data is to be grouped and sorted and being able to use fields from more than one table or query, provided the specified relationships has been decided beforehand.

  1. Click Create tab, Report group, Click Report Wizard
  2. Follow directions on the Report Wizard, click Finish

 

Subform

What is a subform? A subform is a lot like it sounds, a form inside another form. Other names can be parent/child form or master/detail form. Subforms are best used to show data from tables or queries that are considered to have one-to-many relationship.

Make a Subform

First open a database window. Click on Forms in the Objects task pane, click the New button located on the toolbar and a New Form dialog box will open. Double click the Form Wizard. From the list, select Tables/Queries. Select the fields that you want to add to the form by double clicking them from the Available fields list. Do not click Next just yet. First you will need to select a second option from the Tables/Queries lists, double click the fields and add accordingly to that selection. Make sure to set up the relationships between the two tables or queries, now click Next.

Access VBA

VBA is also known as Visual Basic for Applications. What VBA is, is a language that is used to create powerful applications in the Windows operating systems. VBA can be used to program anything that you like. VBA can be most helpful in Windows programs: Excel and Access. Using Access VBA codes allows you to be able to perform more complex operations instead of using Access Macros.

Enable Macros in Microsoft Access 2007

Step 1 – Select the Office button located on the top left.

Step 2 – Select “Access Options”, located at the bottom.

Step 3 – Access Options window is open. Select the “Trust Center”

Step 4 – Select “Macro Settings”

Step 5 – Choose “Enable Macros (not recommended; potentially dangerous code can run)”

Step 6 – Click “OK”.

Enable Macros Microsoft Access 2010

Step 1 – Click on File and select “Access Options”.

Step 2 – One the left side of the window, select “Trust Center”

Step 3 – With the Access Options window open, Select “Trust Center Settings” button.

Step 4 – In the Trust Center window, click on Macro Settings on the left side.

Step 5 – Select the option, “Enable all macros (not recommended; potentially dangerous code can run)

Step 6– Click “OK”

Step 7 – Select “OK” again

Note: A dialog box may appear and inform you to close the program and reopen for the action to take place.

Step 8 – Select “OK” and close Access

Microsoft Access Compact and Repair

Databases tend to grow with use and can become corrupted. Using the Compact and Repair Database command assists in preventing and correcting the problem that can affect a database.

Here is how to use Compact and Repair

Step 1 – Make sure that you are the only one that is in the database. Only one user can be in the database for this tool to run.

Step 2 – Select the Microsoft Office button

Step 3 – From the Office menu, select “Manage”

Step 4 – Select “Compact and Repair”

Step 5 – The “Database to Compact From” dialog box appears. Select the database that you wish to compact and repair.

Step 6 – Select the Compact button

Step 7 – Rename the compacted database in the “Compact Database Into” dialog box.

Step 8 – Select the Save button

Optional Step – After checking to make sure the compacted database is running properly, you can delete the older version and rename the newer version the same as the old database.

Microsoft Access List or Combo Box

A combo box control is what combines features of a text box and a list box. A combo box is used when one wants to have the option of either typing in a value or selecting a value from a predefined list.

How to Create a List box or Combo box using the Wizard in Access.

Create a list box or a combo box by using a wizard

  1. Right-click the form in the Navigation Pane, and then clickDesign View.

NOTE   This procedure assumes that the form is bound to a table or query. Some of the steps will not apply if the form is unbound. To determine if the form is bound to a table or query, press F4 to display the property sheet. On the Data tab of the property sheet, the Record Source property box displays the table or query that the form is bound to.

  1. On the Design tab, in the Controls group, ensure that Use Control Wizards 110   is selected.
  2. Click either the List Box28  tool or the Combo Box  34
  3. On the form, click where you want to place the list box or combo box.
    • Depending on your choice, the List Box Wizard or the Combo Box Wizard starts.
  4. When the wizard asks how you want to get the values for the control, do one of the following:
    • If you want to display the current data from a record source, clickI want the list box/combo box to look up the values in a table or query.
    • If you want to display a fixed list of values that will seldom change, clickI will type in the values that I want.
    • If you want the control to perform a find operation, rather than serve as a data entry tool, clickFind a record on my form based on the value I selected in my list box/combo box. This creates an unbound control with an embedded macro that performs a find operation based on the value the user enters.
  5. Follow the instructions for specifying how the values will appear.
  6. If you chose one of the first two options on the first page of the wizard, the wizard asks what you want Access to do when you select a value. Do one of the following:
    • To create an unbound control, clickRemember the value for later use. This means that Access will hold the selected value until the user changes it or closes the form, but it will not write the value to a table.
    • To create a bound control, clickStore that value in this field, and then select the field you want to bind the control to.
  7. ClickNext and type a label for the control. This label will be displayed next to the control.
  8. ClickFinish.

Microsoft Access ODBC connection

Microsoft Access ODBC Connection, aka, Open Database Connectivity is a protocol that is used to connect a Microsoft Access database to a SQL (Structured Query Language) database servers, such as Microsoft SQL Server or Oracle.

Microsoft Access Relationships

Microsoft Access Relationships is referring to the how data in one table is related to data in other tables. Tables can be related to another table(s) in three different ways:

One-to-One: One record from one table matches another record from a different table.

One-to-Many: One record from one table matches many records from another table. An example: An order tracking database that has the Customer table and an Orders table. The customer is able to place many numbers of orders and follows that for any customer is the customer table. There can be many orders that are represented in the Orders table. So the relationship between the two tables is one-to-many relationships.

Many-to-Many: Many records from one table matches many records from another table.

One-to-one relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This kind of relationship is not common, because most information that is related in this manner would be in one table. You might use a one-to-one relationship to take the following actions:

  • Divide a table with many columns.
  • Isolate part of a table for security reasons.
  • Store data that is short-lived and could be easily deleted by deleting the table.
  • Store information that applies only to a subset of the main table.

In Access, the primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol.

Handling Many-To-Many Relationships in Microsoft Access:

Definition: Many-to-many (M:M) – is used to relate many records in the table A with many records in the table B. A record (‘parent’) in Table A can have many matching records (‘children’) in Table B, and a record (‘child’) in Table B can have many matching records (‘parents’) in Table A.

It is the hardest relationship to understand and it is not correct. By breaking it into two one-to-many relationships and creating a new (junction/link) table to stand between the two existing tables will enable correct and appropriate relationship setting.

A many-to-many relationship is really two one-to-many relationships with a junction/link table.

What is an ACCDE file?

An accde file is a database that has been created by Microsoft Access, a relational database program. Accde file allows for all Visual Basic for Applications code to be saved in a compiled format that is not viewable or able to edit. This allows for a custom database code to be protected.

What is Microsoft Access?

Microsoft Access is used to analyze large amounts of information and has the ability to manage related data more effectively than Microsoft Excel or any other spreadsheet applications.

Access is used by small to large businesses, to people wanting to manage their own finances at home.

Microsoft Access is a part of the Microsoft Office Suite, however it does not always come with all the versions of Microsoft Office. Keep this in mind if you are purchasing Microsoft Office Suite if you want it to include Access.