ISP 121 Access Queries and Forms Access Queries

Simple Queries To create an Access query, don’t use the query wizard. Instead, create

Queries You can look for something after a certain date IF the data was

Queries Logical OR - You can look for records in the state of Indiana

Queries Logical AND - You can also use an AND in one field. For

Queries That Calculate When performing a query, you can aggregate the data You can

Example Say you have a database for a vet that treats dogs. Each dog

Example What if you want to find the average height and weight for all

Example What if you want to find the minimum and maximum weight for all

Displaying Data – The Form One way to start a form is to use

Forms Continued Now go back into Design View to edit the form Resize windows

Toolbox Basic Controls Label controls – headings, labels, captions, instructions Text box controls –

Toolbox Basic Controls List box – a pull down menu which is always down

Sample Form Let’s try another example Create a form using Name text 50, Married

Sample Form Click on Field List button. If you can’t click on this, click

Properties Pretty much everything in Access has a list of properties associated with it

Creating a Form Using Multiple Tables Create form for main table On toolbox, click

Forms Continued Note – if you change the data on the form, you are

Slides: 20

Download presentation

ISP 121 Access Queries and Forms

Access Queries

Simple Queries To create an Access query, don’t use the query wizard. Instead, create query in Design view Let’s see how Access does it Copy the Pets database to your desktop Then open the Pets database

Queries You can look for something after a certain date IF the data was stored as date/time and you say >1/1/2004 Dates should be entered with # before and after the date, and can be in many different formats, ie #1/1/2004#, #January 1, 2004#, #1 -Jan-2004#

Queries Logical OR - You can look for records in the state of Indiana or Illinois by saying “IL” OR “IN” You can also say: In (“IL”, “IN”, “OH”) Logical AND - you can make multiple entries in the query boxes. For example, in the State field enter “IL” and then in the Size field enter <3

Queries Logical AND - You can also use an AND in one field. For example, in the Size field you can enter >=3 AND <=9 A slightly easier way of doing this is using the BETWEEN operator: Between 3 and 9 Possible operators include =, <>, <, >, <=, >=

Queries That Calculate When performing a query, you can aggregate the data You can perform a Count, Sum, Avg, Max, Min, St. Dev, Var(iance), First, and Last Count, First, and Last can be performed on types counter, number, currency, date/time, yes/no, text, memo, and OLE object The others on counter, number, currency, date/time, and yes/no

Example Say you have a database for a vet that treats dogs. Each dog treated has an entry including ID, weight, and height If you want to find the average weight and height of all pets: (you may have to click on View / Totals) Field: Pet ID Weight Height Total: Count Avg Show: X X X

Example What if you want to find the average height and weight for all dogs? Field: Weight Height Type of Animal Total: Avg Group By Show: X Criteria: X X “Dog”

Example What if you want to find the minimum and maximum weight for all dogs? Field: Weight Type of Animal Total: Min Max Group By Show: X Criteria: X X “Dog”

Access Forms

Displaying Data – The Form One way to start a form is to use the Form Wizard Let’s create a form for our Real Estate database, for the Listings table Download the Real Estate database to the desktop and unzip the file

Forms Continued Now go back into Design View to edit the form Resize windows Move fields around Many more properties / controls available

Toolbox Basic Controls Label controls – headings, labels, captions, instructions Text box controls – data is displayed or entered here Toggle buttons, option / radio buttons, check boxes Option group – contains multiple toggle buttons

Toolbox Basic Controls List box – a pull down menu which is always down Combo box – a pull down menu which you must pull down, and also lets you add an option that is not on the list When working with forms, notice Table button (or Field List), Toolbox button, and Wizard button in Toolbox

Sample Form Let’s try another example Create a form using Name text 50, Married Yes/No, Employed Yes/No, Home Owner Yes/No, City text 50, and State text 2 Make Name a text box, Married a toggle button, Employed a check box, Home Owner an option button, City a combo box, and State a list box

Sample Form Click on Field List button. If you can’t click on this, click on Properties and set Record Source to appropriate table Click on Toolbox and make sure Wizard button is pressed Click on ab|, then click and drag on Name field Click on Toggle button, then click and drag on Married field Now put a caption or a picture in the toggle button using Properties

Properties Pretty much everything in Access has a list of properties associated with it To display properties, you can click on View Properties from the menu bar Or you can right click on an item Let’s look at some properties

Creating a Form Using Multiple Tables Create form for main table On toolbox, click on subform button (make sure the Wizard button is pressed) Answer the wizard’s questions

Forms Continued Note – if you change the data on the form, you are changing it in the database! You can use the form for entering new data You can create a form for a query and if you enter new data on the query form, it changes the data in the table! Let’s stop here and try our activity