Welcome > Form Designer module > Add actions to a form or control > Add actions > Create a DB query > DB Query to view records in a table

DB Query to view records in a table

  1. On the DB Query Action page of a form, or on the Extended Properties page of a Data Access activity, click Configure.
    The Database Query page appears.
    Note By default, Tables is selected and all tables within the selected database are available on the Table list.
  2. On the Table list, select the table to view records for. All columns within the selected table appear.
    Note By default, the View option is selected.
  3. On the All Columns list, select the columns to retrieve to view the records. For example, for a Customer table, you can view the records for the ContactName, Address, and Phone columns.
  4. To view the records for the selected columns, do one of the following:

    • Click Return Multiple Rows to retrieve multiple rows, .

    • Click Return Single Row to return a single row, .

    • Click Use Aggregate and do the following:

      • To retrieve the maximum and minimum values of a column, select the Max and Min check boxes.

      • To get the count of rows within a table, select the Count Rows check box.

  5. To specify the fields and sort order in your result:
    1. Select the columns.
    2. Select the sort order against each column.
  6. To reorder the columns, select the columns, and then use the Up/Down arrows to move the selected columns to the required order.
  7. Configure your query based on specific conditions. For example, you can search for records with similar values. Conditions are helpful and enhance your search result when you do not know the exact value of an item.

    Example 1

    To retrieve the records of ContactName, Address and Phone for the cities of London and Berlin, specify the condition as:

    1. Column: City(String), Operator: Equal To, Criteria: London, Logic: AND.

    2. Click Add Add.

    3. Column: City(String), Operator: Equal To, Criteria: Berlin.

    4. Click Add Add.

    Example 2

    To retrieve records of customers whose surname starts with 'M', specify the condition as: Column: LastName, Operator: Begins with, Criteria: "M".

    Example 3

    To retrieve the records of FirstName, LastName, Date of Birth and Address for first names starting with 'A' such as Andrew, Avril, and Anthony; first names containing the letters 'or' such as Trevor, Florence, and Victoria; or first names ending in 'a' such as Martina, Katrina, and Sabrina, specify the following condition:

    1. Column: ContactName(String), Operator: begins with, Criteria: A, Logic: OR.

    2. Click Add Add.

    3. Operator: contains, Criteria: or, Logic: OR.

    4. Click Add Add.

    5. Operator: ends with, Criteria: A.

    6. Click Add Add.

    Note To update a condition, select the column, make changes, and click Update Update . To delete a condition, select the column and click the Delete Delete.
  8. To specify the fields and sort order in your result:
    1. Select the columns.
    2. Select the sort order against each column.
  9. To reorder the columns, select the columns, and then use the Up/Down arrows to move the selected columns to the required order.
  10. Click OK.
  11. To test the database query, click Test.

    The Preview Query window appears with the records for the selected columns.

  12. Click Close.