Sunday, July 28, 2013

Use queries to do your work

Many people think of queries as just a way to ask the database a question and get a response.  The typical query is a select query.  This is one in which you are selecting a sub set of data from a table, or multiple tables, and either filtering rows and/or columns to create a new recordset.

Queries can be so much more than just select queries.  Here I will go over 3 types of queries that you can use to modify the data in your database.  The queries DO NOT return a set of data, but instead can add, edit, and/or delete data in your database.

 INSERT  (Access refers to this as an Append query)

An Insert Query is used to insert a new record, by listing values that match existing fields in a table.
This example would insert my data into the Customer table in the database:

INSERT INTO Customer (FirstName, LastName, Address, State, City, PostalCode) VALUES (‘Rob', 'Buonocore', ’130 Mapple Ave', 'NJ', ‘Dumont', 07628);  
  • The fields do not need to be in the same order as they are in the table.
  • You do not need to enter data into every field unless the field is set up as required on the table side.

The above example is a bit silly since you would rarely have the opportunity to hard code values into an insert query.  So to show a more practical application, you can use the same query to refer to fields on a form.  Then call the query from a VBA statement or Macro and it will insert the values that you entered into fields on a form, directly into your table.

            INSERT INTO Customer (FirstName, LastName, Address, State, City, PostalCode) VALUES
            (Forms!myForm.Fname, Forms!myForm.Lname, Forms!myForm.Address, Forms!myForm.State, Forms!myForm.City, Forms!myForm.Zip);

In this example, the user would enter data into unbound controls on a form and the query would pick up those values and insert them into your table.

You can also use an insert query to append many records at one time.  Usually this is appending all the records from one table or from a query.  I often use this type of query to append all the ID numbers of a group into another table to record that I performed some action on them (for example, sent them a letter).  I'll also use this type of query to perform archiving of records from one table into a master table.Rules:
  • Inserted data must have the same data type as the field Or be convertible to the same data type
  • An inserted value can not violate a check constraint
  • If a column is omitted the field is assigned:
    • Default value if one is set
    • NULL

An UPDATE query is used to edit existing data in the database.  It can be used to update one specific row in a table or all rows in a table.  This kind of query can use the WHERE clause in order to select the specific records to be updated.  Not entering any criteria will cause all rows to be updated.

                 UPDATE Customer SET FirstName = 'Robert',
                 WHERE FirstName = 'Rob';


  • You must specify the Table to update, the Columns to update, and their new values.
  • Optional search condition that specifies rows to update

Use a DELETE query to remove/delete:

  • A specific row in a table
  • All rows in a table

You can use the WHERE clause to select a specific record to delete or a sub set of records to delete.  If you do not enter a WHERE clause it will delete ALL the records in the table. 

FROM Customer
WHERE State ="NJ";

  • You must select the table to delete from.
  • You can not delete certain fields.  You must delete the entire row.

Each one of these queries has so many more possible ways that they can be used than is presented here.  The point is to recognize that these types of queries exist and to begin thinking of queries as more than just selecting records and columns.