MS Access groups data

In this chapter, we'll show you how to calculate on a set of records in Access. b20> es. so-called aggregate queries.

Aggregate queries

Aggregate queries (also known as total or summary queries) are sum, quality, or group details. b20> ds. ere is a simple table that lists some methods for totaling a set of records.

Let's open the database, go to the query design, and include the following table -

Close this dialog box and you'll see the following two fields, as shown in the query grid in the following screenshot.

This is a simple query, we only show two fields - book title and quantity - and when we run it, we see each individual order in the database.

Now let's run this query and you'll see the following results.

That's why the title repeats. E ach book here has a separate order with a different number of orders. it. hen the sum of the quantities listed next to it.

Now let's go to Design View, where you'll see a Sigma symbol in the Design tab. This is your total button.

Click to open another row of the sigma symbol below the field in the table row, and from here specify how to group this query.

We will now group by book title and total our quantity fields.

If we click Group by region and further click on the drop-down menu, all options will be listed. In this case, we'll select Sum and run the query.

You can now view each individual book, along with all the individual orders that appear next to the title.

In-line in access

We learned the process of normalization, storing information in separate fields. b20> nt. ou can also add other characters, such as commas or periods, as you might want.

• To connect in Access, there are two different operators, and you can use the sign and the plus sign.

• The symbol combines two strings into one, and the plus sign combines the two strings and propagates the NULL value, for example, if one value is NULL, the entire expression will be evaluated as null.

Example

Let's take a simple example of how to create a new query using a query design. This will be a very simple query to extract information from the customer's table.

Now, let's add the tblCustomers table to close the display table dialog box. We'll try some connections to some simple named fields.

Now, let's add the first and last name fields and run the query.

As you can see, first and last names are divided into two different fields.

We need to show this information together.

Returns to the design view and creates a new field named Full Name in the third field. Now add the full name and type the expression that connects the two fields together.

Let's run the query and you'll see a new calculation field.

It can connect information from these two fields together, but it doesn't exactly appear the way we want it to. b20> ds.

Now let's go back to the design view and add another part to this expression.

Add spaces in quotation marks and another . . . This allows Access to obtain information from the name field; A dd it with a space, and then add the information in the last name field at the end. ts.