Have you ever wanted to create an Access index report for a list of names? This article describes how to create a report from a data table containing fields including Firstname and Surname, with the report sorted by surname and indexed under the appropriate letter of the alphabet. The steps to do this are firstly to create a query which lists all the table fields and the first letter of each surname. Then secondly to create a report based on the query, and then thirdly to organise the report sorting and grouping, with the data grouped under the appropriate letter of the alphabet.

To start, you need to have an Access database containing a table with fields including Firstname, Surname, and others as appropriate. Then in query design view create a query based on this table and add all the table fields to the query. If you run the query you will see the same view as the table. Back in query design view, we next create a custom query in the next available field heading. We'll call this field Letter so we type Letter followed by a colon ":" without the quotes. We then use the LEFT function to pull off the first letter of the surname data. So we type all this into the new field heading LETTER: LEFT(Surname,1) then click one cell down. Provided you already have the Surname field in the query design, Access will recognize it and place square brackets round it. So the amended expression looks like Letter: LEFT([Surname],1) and when you run the query you'll see all the records just like the table view but also with the new Letter field showing all the first letters of each surname. Then save the query.

Next we create a new report based on this query. The easiest way is to use the Report Wizard and then choose the query you just saved. Next you add all the query fields, including the new Letter field. Then work through the report wizard but don't choose any grouping or sorting at this stage. Select a tabular report which shows one record per line, select a colour scheme as appropriate, give the report a name and then create the report. The report should now list all staff records with one record per line. Next we want to add grouping to the report.

To do this select Report Design View and choose the grouping option. (Toolbar option in Access 97-2003, expandable option under report in Access 2007/2010). In the Grouping panel choose to group by Letter, ascending, and in the Group Header option choose Yes. Then click OK to finish. Save the report and view it again. You should now see all the records listed in Letter order, with a gap above each change of letter - this is the Letter group header. Now switch back to Report Design View to organize this header.

Can you see the Letter field and label in the details section? We want to cut it out of Details and paste it into the Letter Header section. To do this select the Letter field box. Both the field box and label should select. Then choose cut. Next click into the Letter Header section and choose Paste. Both items should now appear in the header. We only want to see the Letter data, so delete the Letter label. You may want to then align the Letter field in the header with the leftmost field in the detail section, and then apply formatting to the field, such as blue colour, larger font size and bold. Save the report and then view the report again. Your data should now show as an index under each letter of the alphabet, in surname order.

Want to ensure the first letter is always a capital? You can modify your query which selects the Surname first letter to convert it to uppercase just in case it is entered in lowercase. So close the report. You might like to ensure that some of the data in the table does have some surnames all lowercase. Then open the query in design view. We'll use the Access function Strconv(field,1) to convert the Letter expression to uppercase. So amend the expression for Letter to the following Letter: STRCONV(LEFT([Surname],1),1) and then test the query again. This should now list all first names in uppercase. Save and close the query. The report is already based on the query, so just run view the Report again, and now you should see the staff indexed by Surname, with all the letters uppercase.

Interested in learning more about Access? Attending a training course is a proven way to really boost your skills in a very effective way.