Client Query

In this tutorial we will create a simple Client query, where we will ask Access to

list all our clients with contact details,
sort by LastName then FirstName and
join up the LastName and firstname separated by a comma..

In the toolbar, select Create > Query Wizard > Simple Query Wizard

Use the dropbox (Tables/Queries) to select Table:tblClient and move all available fields to the Selected Fields ([>>])

Change the Query name to qryClient, the [Finish] to view the query.

The following query will be displayed (actually at the moment it is just the same as viewing the Client table (tblClient)

Before we change the query it will pay to save it to the DBMS so close it down and

SQL

First lets look at how MS-Access saves the Query. AS mentioned in the Query introduction Access uses SQL (pronounced sequel) to store its queries.

So Click on View > SQL View to see the SQL behind the query.

You will see the folllowing

SELECT tblClient.[ClientID], tblClient.[ClientBusName], tblClient.[ClientAddress], tblClient.[ClientTownCity], tblClient.[ClientMobile], tblClient.[ClientContLastName], tblClient.[ClientContFirstName] FROM tblClient;

If you read it carefully it says SELECT a list of attributes FROM the table tblClient;

Notice that it identifies each of the attributes by putting adding the table name, this is important later when a query combines multiple tables.

Now that you have seen how Access saves a query lets use it's designer to make some changes. You can actually make changes in the SQL view but it's much easier in the Design View.

So Click on View > Design View to see the User Interface that Access has for you to visually modify your query. (Look closely and you should be able to recognise the parts that make up the SQL command..

Adding conditions

The real power of a query is that we can select what we want, for example, change the sort order or join up the Contacts Last name to the Contacts First Name .

In our tutorial we will change the sort order so that the query sorts on the Contact Last Name, plus we will add a new query attribute that will join up the Last name and the first name and put a comma in between.

Changing the Sort order

All you need to do is in the Sort row under ClientContLasName use the drop down and select "Ascending" (A-Z) . if you want to sort from Z-A use descending..

However, what if you have two client contacts with the same last name and different first names, it would be useful to sort on Last Name first then First name.

( If you want it the other way round you can drag the columns around and it will sort based on the order of the columns.)

The example below show ascending for LastName then FirstName

Click on DataSheet view and you will find your query has been sorted.

Check out the SQL View and you'll see an ORDER command has been added.

Concatenating LastName and FirstName

So back to Design view and we'll join up the lastname to the firstname and separate with a comma (This is known as Concatenating fields)

To do this,

In the last colum add the expression

ClientName: [ClientContLastName]+", "+[ClientContFirstName]

ClientName: will become the Column heading and the expression [ClientContLastName]+[ClientContFirstName] tells Access to join the LastName and the FirstName.

Note that some programming languages use the "&" to concetanate strings.

Question What heppens if you miss out the ", "?

You end up with the LastName + FirstName joined without a separator, e.g., CeeLyon, FantElly.

The small tick boxes indicate to the query if the attributes are to be displayed , so lets turn off ClientContactLasName and ClientContactFirstName and turn on ClientName. Note that the fields do not show but the sort remains on them.

This will hide the Contacts Last Name and FirstName and add the new ClientName Column.

The SQL for this is

SELECT tblClient.[ClientID], tblClient.[ClientBusName], tblClient.[ClientAddress], tblClient.[ClientTownCity], tblClient.[ClientMobile], [ClientContLastName]+", "+[ClientContFirstName] AS ClientName
FROM tblClient
ORDER BY tblClient.[ClientContLastName], tblClient.[ClientContFirstName];

When you close the Query Window, you should have the qryClient added to your Access Objects.

Google Sites

Report abuse