Chapter 7 Access Tables / Queries

Chapter Outline

Database Basics

Designing a database

Creating a Table

Queries

Creating a Sort Query with Formulas

Creating a Query with equal type criteria

Creating a Query with < or > type criteria

Creating a Parameter Query

Creating a total Query

What’s Wrong With This Database

Review of Menus

Database Basics

Have you ever used a database before? I bet you have. Maybe not a computerized database but a manual database, I am sure you have used. The best example of a database that I can give you is that of a phone book! A phone book is a collection of peoples’ telephone numbers and address.

I want you to note that we will cover database in only 2 chapters but there is a TON more to learn. Let us start with some of the terminology:

Database - is a file that stores information in a structured and organized manner that allows you to retrieve that information in several different ways (i.e. forms, queries & reports). A database file holds tables, forms, queries, and reports all in one file as pictured in Figure 1.

Tables - hold the structure of your information and is comprised of records. An easy way to picture a data table is to think of a spreadsheet with columns and rows. The row would be the records and the columns would be the fields. Each data table relates to one entity. For example a data table about a phone book, the entity would be the people, since that is what is in the table.

Record - is information about one particular entity. A record in a phone book would include the person’s last name, first name, address, and phone number. Each person or entity has its own record. Everything in the record pertains to that entity.

Fields - make up the individual components of the record. In the phone book example last name is a field, first name is a field, address is a field and so is phone number. Each field has a type, i.e. Number or text. Each field also has a specific size, i.e. 10 letters.

Primary key - is a field that uniquely identifies a record. Can you think of a number that uniquely identifies you? Try your social security number. Some other examples of primary keys are customer number, product number, invoice number, and billing number. Often times the primary key is automatically assigned by the computer.

DataSheet - is a way to look at the information in your data table. Datasheet view is laid out like a spreadsheet with columns and rows. You see as many records and fields on your screen as will fit.

Queries - enable you to pull out the information from your data table that you want to look at. They enable you to narrow down the focus, and only see the records and fields that meet a certain criteria. Queries are based on a table or another query.

Database Basics

1. Keep it simple.

2. Never have a Calculated field in your Table.

3. You should have a Primary Key to uniquely identify each record in your Table.

4. Do not store a Field in another Table unless it is a Primary Key used to connect the two Tables.

5. Forms and Reports can be created using a table or query.

Figure 2

Forms - are just another way to look at the information in your data table. With a form you see only one record at a time and generally you try to fit all the fields on one screen. Forms are generally made to look like the paper form that you may be familiar with. Forms can be based on a table or a query.

Reports - are designed to print information in your data table. Reports can be based on a table or a query. The phone book is really a report that was printed from the information in the phone companies’ table.

Lets go over the Basics as shown in Figure 2. A database can become infinitely complicated and you need to try and keep it as simple as you can (tough in a complex world). Since a database can get complicated you need to make sure that your design is as simple and straightforward as you can get it.

If the information you want to have can be calculated then you should include a formula in a query and not type in the result (i.e. your gpa can be calculated so you would use a formula in a query and not save the actual value in a field in your table). The slowest thing a computer does is retrieve information from a disk (hard drive or floppy) and the fastest thing a computer does is to perform calculations so you let the computer do what it is best at.

As talked about above each record should a unique identifier, your social security number works nicely. However often times you can use a number that is automatically generated and that works fine. That way you never have to worry about typing it in as the computer handles it for you.

Basic number 4 really does not relate to what we cover in this class as we only create a one table (flat) database system. However it is important enough to include so I have. When you have more than one table in a database system you should never have the same field in more than one table UNLESS it is the primary key used to identify a record. In this case you would call the key the foreign key (naming example: FK_CustID) and it is used to connect two tables together. This does go beyond the scope of what I want to teach you at this time.

Lastly with basic number five and just want to point out the fact that not only can you base forms and reports on queries and that this is very common.

Let’s talk about data tables first since they hold the foundation or structure of your database system. Everything in your database is based on the information contained in your data table(s). In the case of a phone book the structure is pretty much the same for all phone books. Can you guess what it is? Your phone book will be organized pretty much the same as everyone else’s with the names sorted alphabetically by last name, first name. The address is always below the name and the phone number is to the right. The structure of a database would be how many characters are allowed for each field and is the information, text or number data type. A phone number’s structure is always the same, three numbers then four numbers.

The easiest way to begin to understand a database is to look at how other people build theirs. One way to do this is to look at a warranty card or information card that you fill out. Let’s take a look at a sample Wilderness Vermont Information Card Figure 3. You can see what the Wilderness Vermont database looks like by just looking at the information that is being asked for. This warranty card makes up one record in the Wilderness Vermont database. Each piece of information would be a field. Let’s identify the different fields that we can see. There may be fields in the database that we do not see but we can get a good picture of the database by looking at this information card. The information card would be the INPUT for the database. To get a complete picture of the database we would need to see the OUTPUT as well. We can see not only the fields but the size of the fields as well. We will have to guess whether the field is text or a number. Figure 4 is the list of fields I can see from the information card.

Field	Type	Size
fldLastName	Text	15
fldFirstName	Text	10
fldAddress	Text	20
fldCity	Text	20
fldState	Text	2
fldZipcode	Text	10
fldPhoneNumber	Text	14
fldHeight_ft	Number	Integer
fldHeight_inches	Numbers	Integer
fldWeight	Numbers	Integer
fldInsurance	Y or N (yes/no)	1
fldBackpack	Y or N	1
fldCanoe	Y or N	1
fldKayak	Y or N	1
fldSnowshoes	Y or N	1
fldMailOrder	Y or N	1
	Figure 4

I used a field type of text and numbers for most of the fields. Phone number and zip code are considered text only because of the way we are going to format them. Since we are not using them for any calculations they can be considered text. However the last 6 fields I used a new field type and that was Yes or No, which is often used for 'question like fields'. This is all we can tell about their Database file by looking at the Information Card. Notice that I preceded each field name with fld. This is a naming convention, meaning you do not have to do it but that it is a good idea. You should notice that I did not use any blank spaces but capitalized each word and in some cases the _ character to separate them. This is also a convention that you do not have to do but in reality will make it a lot easier for more advance topics so you might as well learn them now.

Text Box: Memo Very large text field varies
Integer -32,768 to -32,768 2 bytes
Long Integer -2,147,483,648 to 2,147,483,647 4 bytes
Single -3.402823E38 to 3.402823E38 4 bytes
Double -1.79769313486232E308 to 1.79769313486232E308 8 bytes
Yes/no (Boolean) YES/NO 2 bytes
Date/Time Valid date or time 8 bytes
Figure 5
Figure 5 A text data type you need to figure how many letters you want to allow for that field and in the Wilderness Vermont example all you have to do is count the little boxes. A number data type you need to figure out what type of number you want to use. An integer data type is for whole numbers only that fall into the range of -32,768 to 32,767. So a number 33,000 would not fit into an integer data type field and would cause an error. In the Wilderness Vermont the numbers will all be small so I made them all integers. You can see in figure 5 some of the other data types and the values that can be placed in them. You always try to use the smallest (number of bytes) data type that you can.

To really study someone’s database system you would need not only the INPUT but also the OUTPUT that comes from the database. Combining the two you can figure out which database fields that the company has in its database. Since a computer accepts input and processes it in some way to produce output by looking at these two you can construct the database. If you input a field it should be used in some way either as a processing step or directly as output. If the input is not used in any way it may not be worth saving in a database file.

When thinking of database files think of a large amount of data and more than one table (for simplicity we are only going to use one table and only a few records). The record above for Wilderness Vermont will take up 98 bytes (assuming one letter takes up one byte) of space on a diskette. So we can fit about 15,000 Wilderness Vermont Information records on a floppy diskette. If we have a character field of size 6 that we do not use that would waste 90,000 bytes of space! So when designing a database you always want to be sure to save all the information you will need but try to avoid saving information that you are not going to use.

Designing a database

Field	Type	Size
pkCustomerID	AutoNumber	Long
fldLastName	Text	15
fldFirstName	Text	10
fldAddress	Text	20
fldCity	Text	20
fldState	Text	2
fldZipcode	Text	10
fldPhoneNumber	Text	14
fldBirthday	Number	Date/Time
fldSpouse	Text	15
fldChildren	Text	50
fldComments	Memo
fldCurrent	Y or N (yes/no)	1
fldHolidayList	Y or N	1
fldFastList	Y or N	1
fldKayak	Y or N	1
fldSnowshoer	Y or N	1
fldHiker	Y or N	1
	Figure 6

The first step in designing a database begins on paper! It seems as if a lot of this computer stuff we must do by hand first. This is done as a way to organize your thoughts. You need to identify all the fields that you will need in your table. This can be done by looking at the forms and reports that your database system will need (just like we did above or by designing your reports and forms). In other words, ask yourself:

1. What information do I want to get out of this database?

2. What information do I need to put in this database to get the information that I want?

For example, if I want a list of all the people who own a kayak I will need a field that first tells me if they have a kayak (fldKayak) and I will need their names as well.

I want to create a simple phone book Data table for myself using the fields listed in Figure 6. Let’s start Microsoft Access. You should know all the parts of the window by now. I will however talk about the tool bar buttons as we need to. Some of them you will already know. Once you have Access opened, click on Blank Access Database or choose the New File toolbar button or File New Database... whichever you prefer, and lets start with a blank database.

When creating a database you have to save it first! Call this file PhoneBook. Since I always have you save before you begin, this should come as no surprise. Actually you will find when working with a database that you have to save first and save often. Also note that this is THE ONLY TIME YOU CAN SPECIFY THE DRIVE, FOLDER AND FILENAME. There is NO save as option in the database.

Your screen should look like Figure 7. Access will have many windows. All of them can be open at the same time. This is the main Database window. For now let's maximize all the windows to make it less confusing.

This is the Database window inside the Access window. On the left shows you all the items that you can include in your database file (tables, queries, forms, reports and a bunch of stuff we are not going to cover).

Creating a Table

A data table is the structure that is going to hold all of your information. Figure 6 shows the fields that I wanted in my table. Let’s go ahead and create a data table. It is easiest to create your table in Design View so that is what we are going to do by double clicking that text. This will give you a blank table design grid (similar to Figure 9) that has columns for the field name, data type and description. In the lower right corner there are directions for the place that your cursor is on.

Go ahead and type in the first Field Name for fldID and press the tab key to move to Data Type. The data type by default will be text but you can click the drop down arrow to check out the different types that you can use. Take a moment and press F1 to read the help about the different data types. Choose Autonumber for the data type. This way an ID number is automatically entered by the computer for us. Then tab to description which is an optional column but we will put in Computer will enter in Automatically. The description is just that, a space for a small description about the field to remind you of what you were thinking when you created the data table (or for someone else to work on the data table so they can try and figure out what you were thinking). It will also display as the status bar text to provide ‘on screen’ help for someone using the database system.

You should notice the lower left corner now has its own little grid. If you do not, use your mouse to click on the fldID row. The grid will change depending on the data type that you have chosen. In this case the information is already filled out with the defaults. Let’s add a Caption ID. The caption will be used later on. When you put in a caption it will display ID instead of fldID so it is a personal preference look. So when you type the caption you don’t use the naming conventions (fld prefix and we do use blank spaces). We do need to make the fldID the primary key. You do that by clicking on the Set Primary Key toolbar button. This will place the key next to the field name like shown in Figure 9.

Let’s work on fldLastName field. fldLastName will be a text field so by default it will have a field size of 50. Change the field size to 15 and put in a caption for this field Last name. You should notice the lower right corner text keeps changing, it keeps providing you with a little help. There is a lot on this screen that we have not talked about and that is because we are just going to skim the surface of databases. You can however get more information anytime by pressing F1 for help.

Go ahead and do the same thing for fldFirstName, fldAddress, fldCity and fdlState as they are all text. The caption will be the field name without the prefix and with spaces if needed. Make up descriptions for each one or refer to Figure 15.

For fldState I want to add a format. When you type in the two letter abbreviation it really should be capitalized. We can force this to happen by putting in a > sign in the format row (< would cause to be lowercase). No matter what gets typed into this field it will be automatically changed to all caps.

When you get to fldZipCode even though it is a number, it is not used for calculations, therefore let’s choose text as the data type. Set the size to 10 to take advantage of the extended zip code (even though the Wilderness Vermont form has not been updated yet). Click on the Input Mask and you should notice the ellipses instead of a drop down arrow this time. Go ahead and click on the ellipses to get the dialog box shown in Figure 10.

Naming Conventions

tbl - Tables

qry - Queries

frm - Forms

rpt - Reports

lbl - Labels

txt - Text Boxes

pkTableName – primary key

Figure 11

Ha, When you click on the ellipses the computer will not let you continue unless we save first! We have saved our database but not our table, so we must do so now. Name this table tblFriends. We are going to use the three letter prefix naming convention as shown to the right in Figure 11. When you save a table, form or anything else, it will all be saved within your database file. In this case PhoneBook.mdb is a file that will have our data table in it. You will never actually save your database again, however you will always have to save your data table and forms as you make changes to them.

After saving you can use the input mask wizard to specify the format for this data. I just chose next accepting it the way it was. When you get to the option buttons be sure to SAVE WITH SYMBOLS. After you are complete you will notice a bunch of numbers, semi colons, etc. placed in the input mask property.

There are several different types of input masks that you can choose. An input mask is simply a format for the data that will go in this field. In the case of a zip code, we will type in the number and the hyphen will show up automatically.

Your screen should look something like Figure 13.

Go ahead and do phone number with an input mask as well. Remember to choose SAVE WITH SYMBOLS. For the Birthday field you need to change the Type to Date/Time. I would recommend a format of short date (m/d/yy). The fields to hold the spouse’s name and the children’s name are both text fields. The comment field on the other hand I used a data type of memo. A memo data type allows to hold a really lot if text data in a field (a text box allows 255 characters). Use the memo data type sparingly as allowing you to use to save a lot of text in one filed also takes up a lot of space.

Now you will start the yes/no question fields, starting with fldCurrent. These fields are all yes/no data type and will be used in the next chapter to perform queries. You should also set the default value for these questions. For fldCurrent I am going to set the default value to yes. You need to think about the most common choice would be yes or no and set the default to be the most common choice. A default value will automatically be placed in this field until we change it. You may have noticed that the default value for our number fields was 0. A Yes value will put a check in a check box when we make our form.

If you click on the Lookup tab for fldCurrent you will see Figure 14. Up to this point if you have looked at this tab the display control has always been a text box. Since a yes/no data type can only have 2 possible answers a check box is used for this data type. Blank means no, a check means yes.

Finish off creating your data table with the rest of the yes/no question fields. When you are finished, your completed design grid should look like Figure 15.

Our data table is all ready to start typing in information i.e. enter in records, but first I want to introduce a couple of toolbar buttons in Figure 16 that enable you to switch back and forth from Design View to Datasheet View (Figure 17). Save your table (or computer will just ask you to save it) and switch to Datasheet view by clicking the toolbar button.

Notice that instead of the field name it is showing you the caption. Also notice the status bar is showing you the description you typed in.

In datasheet view you can type in your information for your records. You can see how this looks similar to a spreadsheet. Simply hit the tab key to go to Last name and type in your last name. Tab to first name and type in your first name. When you are at the last field hit the tab key and you will go to the next record. When you move to the next record Access will automatically save the record to your file. In Access you save your database file once, when you first create it. After that you save all the tables, queries, forms and reports as you make them. When you enter information into your data table the information gets saved on a record-to-record basis. You do not have to do anything to save a record except move to the next record. As you are typing in the record you should notice the pencil shown in Figure 18 on the left of the row. Once the pencil is gone the record is saved. This means the record has not been saved. Notice that ID was automatically filled in. Notice the status bar where it says record 1 of 10. This means we are currently on record 1. The * at the bottom (Figure 17) is where you would type in a new record.

If you are working along with the book, make up ten records with at least five people who have said yes to Kayak and no to Hiking. Figure 17 shows you the people I made up and some of their information. Notice that the address column is larger than the rest. It was to small but I made it large just like you would in a spreadsheet by clicking and dragging it larger. You can also freeze columns by highlighting the columns and choosing Format, Freeze, Columns. This will keep those columns from scrolling off the screen. I recommend that you highlight First and Last name and then choose Format, Freeze Columns.

Queries

Remember I said that when you think of a database to think of a large number of records, for example 100,000. Let’s say from that 100,000, we want to know how many come from the state of Vermont. This question we have about our database is called a Query. Queries allow you to ‘pull out’ records from your database that meet a certain criteria. In this case my ‘query criteria’ was that the field called state was equal to Vermont.

Besides allowing us to pull out certain records we can also use a query to sort our database differently. Our data table is always sorted by the primary key. You can type your records in any order and they will be rearranged (sorted) by the primary key when you next open your table. With a Query we can sort the data table in any order that we like.

A query is the place where we put all of our calculated fields (formulas) in. There are also total queries and average queries that will provide a total or average for a particular field. In fact there are several more types of queries but they go beyond the scope of this course.

Often times we are not interested in all of the fields in our database. Besides pulling out the records that we want, a query will also allow us to pull out the fields that we are interested in. You can include all the records and all the fields if you want. When creating a query to sort your database you will most likely want to include all the information. Let’s start by creating a simple sort query and we will build from there.

Creating a Sort Query with Formulas

Normally what I do is create a query called qryTable that holds all of the fields in my table plus all of the calculated fields as well. Generally I also sort this query as well. Our primary key for the sample database is the fldID number, so our database is sorted by the ID number. I would prefer to look at the database sorted alphabetically by last name and then first name. To get started click the Query tab on the database window. I find it easiest to Create a query in Design View so click that and you will a get a dialog box like Figure 19.

The first step is to choose the table that you want to perform the query on. We only have one table in our database shown in Figure 19, so this is easy. Keep in mind that a query can also be based on another query as well. Your table should be highlighted already so simply click on the Add button to add the table to your query. Close the dialog box since we do not need to add any more tables.

Your screen should look similar to Figure 20. At this point it is easiest to maximize your window if it is not already. The Select Query window has two parts. The top part shows you the table(s) that you have chosen for this query. The bottom portion is your query by example (QBE) grid and this is where you will specify the ‘meat’ (or vegetable) of your query. You simply need to add the fields that you want in your query.

To add a field in your query click on the field row in the QBE grid so you can see the drop down list box. Click the list box so that you see all of your fields like in Figure 20. You can simply choose the field that you want. You will notice just below the field row there is a sort row. Click the drop down arrow for this row you will have a choice of Ascending (abc), descending (zyx) or not sorted. Choose Ascending, as we want our database sorted by last name.

You need to add First Name to your grid and you also want to sort your database by First name as well. This time instead of using the drop down list box double click on First Name on the table (top portion) of the screen. This will place First Name in the next column on the QBE grid. Sort First Name ascending in the same way as you did for Last Name. Your screen should look similar to Figure 21. Go ahead and push the run query button to perform the query. This query will simply sort the database by Last Name and then First Name. Once you press the Run button you will be in a Datasheet view as in Figure 22 (less the first column). However you only have two fields, Last Name and First Name. To get the rest of your database to show we need to add those fields to the QBE grid as well. Choose the design view button to get back to it.

The easiest way to get all the rest of the fields to the QBE grid is double click the blue bar of the table window on the top part of the query. This will highlight all of the fields. Then click and drag them to the grid. Since we have already added last and first name they will show up twice. If you place your cursor on top of the field name in the grid and move it up until it changes shape to a downward pointing arrow then click it will highlight that column. You can highlight both First and last name at the same time with a little click and drag motion. Pressing the delete key will remove the field from the grid (the extra ones). Then you can just double click the table bar to put all the fields on the grid. Don’t forget to resort by last and first name.

Go ahead and run (execute) this query by clicking on the Run Query button. When you run the query now it will show you the contents of your table but sorted by last name. I want to add the calculated field to the grid. I like to put the calculated fields first (just makes it easier to see really does not matter) so in design view click the column for fldID. Choose Insert Column and this will give you a blank column.

To put in a calculated field you type in the name of the calculated field with a colon (instead of an = sign) and then the formula in the field name row of the QBE. I want a formula to show me the age of the person. Since it is a calculated field I will use a naming convention of cfld. The formula is shown below and uses the Date Difference function:

cfldAge: DateDiff("ww",[fldBirthday],Now())/52

You can put most any formula or function in a query. Your design view should look like Figure 22. I did enlarge the column so that you can see the whole formula. Enlarging the column is similar to the way you would in a spreadsheet.

We have not saved this query and we should. Since this query holds all the fields including the calculated fields I name this query qryTable. Choose File, Save and call this query ‘qryTable’ (there is no save as, Save as / Export is something different). If you run the query it will look similar to mine as shown in Figure 23.

Notice that the age has been formatted to only one decimal place in my sample. This is because I set the property for that field to be only one decimal place. Think of everything in the data base as being an object and that all objects have properties. In the past we would highlight a paragraph or spreadsheet cell and then choose format to make a change to it. In the database you still highlight it first (done by just clicking on it). We don’t choose format but we do choose View, Properties. As you look at the properties you can see the properties that this object has. Not all objects have the exact same properties though some of them are the same. I changed the two properties, format and decimal places on cfldAge.

That is all there is to it. Of course you want to rearrange the data sheet view so that last name and first name are to the left. You should also freeze those two columns as well, which is done the same as you did for the table. I went ahead and did that and you should as well. In fact just always do that for any datasheet view. When you close the Query you will see your query name in the database window.

Creating a Query with equal type criteria

What we want to do is ask the question “Who has a kayak?”. Of course I only have ten records so it would be really easy to just look at the table, but if I had thousands of records it would be a little more difficult. With a ‘selection’ query we can pull out only the records that we are interested in looking at. Since we may want the calculated field we can base this query on qryTable. In fact most of the time I base all my queries on qryTable instead of the table itself. The information still comes form the table.

Create a new query in design view and add the qryTable instead of the table this time. You will need to click on the Query tab. Include the fields’ last name, first name and kayak on the grid. Your grid should look similar to Figure 24.

Notice the Criteria Row on the QBE Grid in Figure 24. This is the place that we specify our query criteria. All you do is go to the criteria row on the field that you are interested in and type in an example of what you want to find. In this example, we want to show all the records where the Kayak field has a yes (or the check box was checked). On the Criteria row for Kayak we simply type in ‘yes’ for people who have a kayak. Go ahead and run this query. Figure 25 shows I only have two people in my database that meets this query criteria. You will notice that all the other records are hidden from view. All the records are still in our database table we are just looking at the selected records that we are interested in. Notice that the records are sorted by last name since we based this query on qryTable.

I may want to add phone number to this query so I could call these folks up or I may want to add the address so that I could write to them. You would just go back to design view and add the fields that you want to include. I am going to save this query as qryKayak.

Creating a Query with < or > type criteria

Okay, let’s create a new query based on qryTable. Include Last Name, First Name, and Age. Sort this query descending by Age. This will put the oldest person at the top of the list. Go ahead and run the query to see what it looks like and then get back to design view. Save this query as qryAge.

What I want now is a list of all the people are younger than 30. To do this all we need to do is type in <30 in the criteria row for the cfldAge column. Try this and run the query to see if it works. You should not see any records where the person older than 30. Figure 26 shows what the QBE should look like.

You can narrow this down further by including the AND operator. Return to design view and type in >25 and <30 in the criteria row. This will give us a list of people who are between the ages 25 and 30. Are you starting to get the idea? If your query does not work, try typing it in again from the beginning.

Creating a Parameter Query

You can also query your text fields as well. Create a new query based on the qryTable and include all the fields this time (remember it is easiest to highlight them and click and drag them to the grid all at once). Now say for example you want a list of all the people whose last name begins with M. All you need to do is to type in M* in the last name Criteria Row. The * is a wildcard symbol that replaces all the characters. When you type M* it will change to ‘Like “M*”’ in the Criteria row when you come back to design view so do not be surprised. Go ahead and give this a try. This will give you a list of all the people with a last name that begins with M. Now it would be silly to have a query like this for every letter of the alphabet!

What you can do is create what is called a parameter query which in this example will prompt you to enter the person’s last name. So delete the M* in the criteria row for last name and replace it with [Enter the Person’s Last Name] like shown in Figure 27.

Run this query. You will be given an input box that prompts you to ‘Enter the Person’s Last Name’ which is the text between the square brackets [ ].

That is pretty cool isn’t it? But, suppose you forget how to spell each person’s last name? It would be even better if you could put M* like we did earlier. Go to design view and add Like before and & “*” to the end of the criteria row as shown in Figure 28. What this does is to take whatever letter you type and it adds (concatenates) the * to the end of it. Now run the query and only type in a letter or two. Don’t forget to save it! I saved mine as qryLastName. What do you think will happen if you do not enter in any thing in the input box? Well the criteria would then be like * so it will show you all your records.

Creating a Total Query

Sometimes you want a query to get totals from your database. For example how many people come from each City. Create a normal select query including only two fields, fldCity and fldLastName and sort this query by fldCity. Run the query and do a quick visual count of how many people are from the first City. Back in design view choose View, Totals, this will add a new row to your QBE Grid. You will notice in Figure 29 that I have grouped the query by fldCity and for fldLastName I am going to Count how many are in each group.

You will notice that there are several other functions that you can use (some are also available in EXCEL). In this case I need to count how many people there are. The count functions does just that. Since I am grouping the records fldCity I will get a count of how many people live in each city. Run the query and see what you get!

What’s Wrong With This Database

Looking at the data table below (yes I know that I have 2 figure 29’s). What’s wrong with the datasheet view?

What stands out in my eyes is the fact that I cannot read the entire address. The columns need to be adjusted so that they are wide enough to read the information. You can adjust the column width the same as you did in spreadsheets by clicking and dragging with the mouse (or double clicking for the best fit). You should adjust the widths so that the information is readable. You should also freeze the first and last name fields in this example so that you could scroll through the rest of the fields and still see whom the record information is about. To freeze the fields you highlight both fields and choose Format, Freeze columns. You can then choose Save to save your layout changes.

Take a look at the following query grid and let me know if you can see anything wrong with it:

Since there is no query criterion we can assume that the purpose of this query is to pull out the four fields shown and to sort the data. Well this query is sorted on every field! Yes you can do this but it does not make sense. The first sort is by Last Name. The second sort is by First Name, so if 2 people have the same last name they will also be sorted by First Name. That makes sense to me. The next sort really does not make any sense. If two people have the same last and first name they will be sorted by State as well. For what purpose I do not know. To take that one step further if two people have the same last name, first name and are from the same state they will be sorted by there Weight! The last two sorts are meaningless sorts. You should leave the sort row blank for state and weight.