Hands on: Slave to your database

I recently received an email from PCW reader Peter Jarvis, who sought some advice on an Access problem. He has a phone system with a built-in directory for the switchboard operators to use for incoming calls.

In the table of phone data, each extension number should have one row, where the entry in the Master/Slave column is ‘m’ for master.

An extension number may also have further rows, where ‘s’ (slave) is recorded in the Master/Slave column. In the final column, M/S is short for Master/Slave.

Records are frequently cut and pasted in, and errors also creep in, creating extensions with more than one master, or extensions which have all slave records but no master.

At the end of each month, the data is exported to Access and produces monthly phone bills, and that’s when the inaccurate data described becomes a headache.

It's possible, however, to have queries that show all records with the same extension number and identify those which have no master, and those with more than one master.

Here's the easy one first: finding those extensions with more than one master, using a favourite tool – the Group By query.

I would write a query called DuplicateMasters, as follows:
SELECT Extensions.Extn, Extensions.[M/S], Count(Extensions.ID) AS CountOfID
FROM Extensions
GROUP BY Extensions.Extn, Extensions.[M/S]
HAVING (((Extensions.[M/S])=”m”) AND ((Count(Extensions.ID))> 1));

The table and and the associated queries are in the database (DBCNOV06.MDB) online.

This query groups all the records with common extension numbers where there is an ‘m’ in the M/S field.

The last column in the query grid counts the number of IDs found for each extension with a master and returns the count if it is more than one.

This excludes all the extensions with just one master, which is the permissible – desirable, even – state for each extension.

Finding the extensions with one or more s but no m is fractionally more complex, but perfectly achievable.

In fact, there are several ways to solve this. The easiest is to write one query that finds all the extension numbers that have at least one slave, and then to write another that finds all the extension numbers that have at least one master.

Finally, a third query is employed to compare the two lists and identify those that are located by the first query but not by the second.

See the first query, ExtensionsWithS, with the SQL below:
SELECT Extensions.[M/S], Extensions.Extn
FROM Extensions
GROUP BY Extensions.[M/S], \Extensions.Extn
HAVING (((Extensions.[M/S])=”s”));

The second query, ExtensionsWithM, is identical except for replacing the s with an m.

To build the third query, ExtensionsWithSButNoM, start by adding the two queries above into the query grid.

Double-click Create query in Design view, look on the Query tab in the Show Table dialogue box and double-click each query to add it to the grid.

We want the third query to compare the extension numbers returned by the two previous queries and find all the records for extensions that do have slaves but don’t have masters.

To compare extensions from the two queries, we add a join between the Extn field in each query: click and drag to add a join.