Access  queries

Queries are used to filter out information from a table or a number of tables.

They have many uses - basis for forms or reports, filtering data, calculating fields etc.

To begin a new query go to the queries tab, select new, then design view.

Add the tables that you want included in the query. When you have all that you want click close.

To add more tables later select the add table button

There are various types of query. We are initially going to look at select queries

Select:

Extracts data from one or more tables and displays the data in tabular form.

What we are going to do is to build a query to make the previous task work better and allow us to select our items directly from the product table.

To do this we need to set up our table structure.

So open your suborder table in design view and delete the Product and unit cost fields.

Add a field called ProductID  type Number

Go to the query tab, New, select the product table and add it, now select the sub order table and add it. Click close.

Linking tables in query.

Tables are linked by dragging fields from one table to another. Of course what you drag has to have the same value. So you could drag ProductID from one table to ProductID in another. The fields do not have to be the same name but they must represent the same thing. For example you could link Surname from one table with Secondname in another.

If you have set your relationships up these links are automatic. There should be a link between the two tables, if not drag the Product ID from subsales to the Product ID in products in order to link the tables

Now add the following fields from subsales to the query:

OrderID, SalesID, ProductID, Quantity

And add these fields from Products

Unit cost,description:

To use calculated fields in a query:

For example you have a price and a quantity and you want the total value from the query. You would do the  following:

Quantity and UnitCost are the fields from the table. SaleVal is the calculated field.  Note the syntax of the expression. it is SaleVal followed by a colon, then the formula we want based on the fields from the table. You could of course use any other name than SaleVal. If you create a form from this query the sale value will appear as a field in the form.

Save the query -- call it qsubsales.

Now go to forms, new and create a form based on this query.

In design view add a combo box that links back to the product table. Select the description and link the combo box to the product ID field on the form.

Set the form properties  so it is a datasheet and then in your order form delete the currently embedded form and use this one instead.

Now when you use your order form you can select products from the drop down menu, the unit cost and description are added automatically. Once you enter a quantity the cost is already calculated.

Do a similar thing with the sales form and base this on a query linking sales and customers, to display all of the customer details on the form when you select the customer from the combo box.

Using queries to filter data

To make the following work you will need to go back to your customer table, add an age field and then enter some ages.

Create a query that only has the customer table in it.

Add fields from the customer/client table to the field row in the query by dragging them from the table to this row.

The criteria section is used to specify which records you want from the table to be displayed.

You can either specify this explicitly by writing the string you are looking for in the criteria box. For example if you want everyone with the surname smith then put smith in the criteria box under the surname field.

In an age field if you were looking for everyone older then a certain age then you could put something in the criteria field like ;
                                                                >45

When you put different criteria on the same line, but under different fields, these make AND conditions. That is all of the criteria must be met to display your records.

So this would find all smiths older than 45

These criteria would find all smiths  or people older than 45.

Queries: Getting queries to do more work.

Prompting the user.

To prompt the user for data to search for in a query we enclose a comment or questions in square brackets. Such as this.

This will prompt the user for a surname and then look for records with that surname.

For a more general search that does not require an exact match use the following in Criteria:

                                                Like  [Enter name] & “*”

This will look for any records that start with the name entered.

If you are looking for records between certain dates then use the following format:

                                Between  [Start date]  and [End Date]

This will prompt the user for both of these and then find the records that lie between them.

Extending to link to a form

The basic principle is to use a query to display data for  a list, say a list of people who have paid, and then have a way of only showing this between certain dates.

To make this work add about 12 sales with different dates on them.

Steps:

1. Create the query. Queries, new, design. Add your sales and customer table. Add the date field from sales, plus invnum and paid, add surname and address fields from customer table
2. Save the query.
3. Create a blank form.
4. Add a list box and use your query from above as the source for the list.
5. Save the form, call it listform.
6. Add two text boxes, call one startdate and the other enddate. (properties, other tab, name)
7. Add a button to the form ( without the wizard) go into the buttons properties, events, On Click. Click on the down arrow, select Event Procedure, click on the button with …
8. Enter the following code                 me!list0.requery
9. close the code window and save your form.
10. Now, back to your query. Design and in the sales date criteria enter the following.

Between [Forms]![listform]![startdate] And [Forms]![listform]![enddate]

Save the query, back  to your list form.  Enter different dates in the two boxes in short date form  eg  10/02/01

Click the button and list only show  sales between those dates.

What is occurring here, is that the list shows the contents of the query, and the query is modified by the date criteria on the form. In order to show the updated data in the list we requery it. That is the command we attached to the button.