Hands on: Solving a storage and usabililty problem

Call me (reprise)
In the November column I discussed a problem with telephone numbers. Peter Jarvis had a table that contained extension numbers and wanted a query that shows all records with the same extension number and identified those with no master and those with more than one master. I provided one solution and then I received an alternative and excellently elegant solution from Mike Holmes.

Mike’s solution is a query that is based on the original Extensions table. His query includes the extension number field and a calculated field based on the M/S field (which records ‘m’ if the number is a master and ‘s’ if it is a slave). The expression inspects the content of the M/S field and, if it finds ‘m’, writes 1 into the calculated field and if it finds ‘s’, writes a zero.

The Access SQL is:

SELECT Extensions.Extn
IIf([M/S]=”m”,1,0) AS ExtnCalc
FROM Extensions;

As a quick check that it’s working, you can add the M/S field to the query temporarily. This makes it easy to see the results are being calculated correctly.

The query has translated the ‘m’ and ‘s’ characters into the numbers ‘1’ and ‘0’ respectively so we can now treat them mathematically with a Group By.

Make the query a Group By, set the Extn field to Group By and the calculated field to Sum; this adds the values in the calculated field for each individual extension number. In the answer table, a zero in the calculated field indicates one (or more) slave extension without a master and any number higher than one indicates a multiplicity of masters.

It’s a neat solution indeed (see the query called QryExtnCalc1 in dbcmar07.mdb..

The SQL reads as

SELECT Extensions.Extn, Sum(IIf([M/S]=”m”,1,0)) AS ExtnCalc
FROM Extensions
GROUP BY Extensions.Extn;

I liked it so much, I added a minor refinement. We can retranslate the numbers into user-friendly words using another calculated field. This is the query called QryExtnCalc2. The expression reads:

Words: (IIf([ExtnCalc]=0,”No master”,IIf([ExtnCalc]=1,”OK”,”Multiple masters”)))

and this is the SQL:

SELECT QryExtnCalc1.Extn, QryExtnCalc1.ExtnCalc,
(IIf([ExtnCalc]=0,”No master”,IIf([ExtnCalc]=1,”OK”,”Multiple masters”))) AS Words
FROM QryExtnCalc1;

Nested IIFs and their bracketry look nasty but breaking them down helps make their actions clear. Here we’re using nested IIFs to deal with the three alternatives; zero, one and more than one. IIF can react to two states only, truth and falsehood, so a single IIF cannot cope with three alternatives, therefore we must use two. The first:

(IIf([ExtnCalc]=0,”No master”,

will respond “No master” if the argument is true (that is, it writes “No Master” if ExtnCalc does equal zero). The second IIF is then evaluated:

IIf([ExtnCalc]=1,”OK”,”Multiple masters”)))

and if the argument is true (ExtnCalc equals 1) it writes “OK” and if it’s false (doesn’t equal 1) it writes “Multiple masters”.

Mike’s solution solves the problem that Peter set – how to identify all extension numbers with no master and those with more than one master.

The right solution?
As well as Mike’s solution I had several other emails, not about how to formulate the query, but pointing out that the fundamental problem lies in the data structure. If Peter stored the master extensions in one table, and the slaves in another, then extension can be a unique key in the master table and a foreign key in the slave table.

That structure ensures that he can never have a slave without a master. One rather puzzled reader wrote “Mark, you clearly know how to do this because you covered this very topic earlier in the year in the database column!”

Yes, I do know how to do it and, had I been creating the database from scratch, I would have used this kind of structure. But I think there is a balance to be struck sometimes in trying to help people with database problems.

Peter’s database seems to work fine; his only real problem was identifying slaves without masters and multiple masters. Getting him to rewrite the entire structure seemed like overkill, so I solved his immediate problem. In other cases, poor structuring leads to a multitude of problems and these readers are quite correct, a restructure is the only viable solution.