Undupe Panel (Worksheet)

Let's continue the example begin in the Explain topic. There we saw how the question "Show the customers that buy both seafood and chai" created a list, not just of these chai-drinking seafood consumers, but of their chai and seafood orders as well. If we were interested only in which products these customers purchase -- and not whether they placed such orders once or many times -- we might prefer to drop the "duplicate" records out of this display.

These records aren't duplicates in the full sense, because each has some differing information, for example the OrderID number. But now we're using "duplicate" in a very flexible sense. We want to specify the Customer and the Product as the meaningful parts of the record, and remove all but one example of each pair. (Of course, in other examples there might be three, four or more fields of interest.)

Let's compare how this might be approached using the two tools we have at our disposal, the Access ELF Worksheet and the Microsoft Access "Find Duplicates Query Wizard." Using Access ELF, we click on Undupe, exposing the Undupe controls in the Worksheet footer. To specify the fields which, taken together, must show only once in the result set, we click on the column headers of the fields. In this case, we'll click once for CompanyName and once for ProductName.

Now when we click the Remove Duplicates button, instead of the 184 records we had at first, we'll see a smaller set of 133. In fact, what's left are the records showing the first time a customer ordered a particular item. You can inspect the SQL to see how this happens. The key section of the SQL is the SELECT clause, which now reads:

SELECT First(Customers.CustomerID) AS FirstCustomerID, First([Order Details].OrderID) AS FirstOrderID, First(Orders.ShipName) AS FirstShipName, First(Categories.CategoryName) AS FirstCategoryName, Products.ProductName, Customers.CompanyName

At the end of the SQL statement is another change:

GROUP BY Products.ProductName, Customers.CompanyName;

Aside from ProductName and CompanyName, all the columns have been bracketed by this First( ) operator. This has the effect of keeping only the first data value for the column corresponding to each ProductName + CompanyName pair. (Note that by "first" we mean the first in the original listing, not by date -- in this case they happen to be the same.)

One of the nice features of the answers returned by this method is that the "unimportant" information is not completely gone. For instance, the CategoryName field hasn't disappeared, so it's still easy to make out the difference between products in the seafood group and the lone product (chai) from the beverage group.

Microsoft's Find Duplicates Wizard is a great tool, but its focus is quite different. It narrows in on the records that have duplicates in the fields we specify. In other words, it hides the orders which were one-of-a-kind, such as Berglund's purchase of Röd Kaviar. In fact, customers that haven't bought an item on more than one occasion won't appear in the listing at all.

Of course, it's possible to modify the output of both the Duplicates Wizard and our own Undupe tool. For instance, it's not too difficult to change the Duplicates Wizard query into a close cousin of our own. Here's how it's done. With the answer to "Show the customers that buy both seafood and chai." showing in the Access ELF Worksheet, start the Duplicates Wizard by clicking Insert / Query from Access's menubar. Click on "Find Duplicates Query Wizard" and click OK. Switch to the Queries listing, and double click the entry shown as Query: elfQ4. (This is ELF's answer to the "both seafood and chai" question.) Click Next, then select the ProductName and CompanyName fields into the Duplicate-value fields list. Click Finish.

You should see a display of the products which have been ordered more than once by a single customer. The number of times they've ordered this item is also shown. To see how this listing matches up with our "unduped" Worksheet, flip the Wizard's query into Design mode. You'll see two ">1" conditions. Delete them and rerun the query; you'll see the same 133 records as earlier.

In one way this result is useful, because it gives us a precise count of exactly how many times each customer ordered each product. But it does require careful, non-wizardly use of the Query Designer. To get the additional fields shown on the Worksheet would require additional steps.

By the way, we could get the same results much more easily from the Worksheet itself. Here's how: with the answer to "Show the customers that buy both seafood and chai." showing in the Access ELF Worksheet, click the Edit button. Click View / Totals from the Access menubar. In the Totals cell under [Order ID], change "Group By" to "Count". That's it -- run the query.

We're not trying to prove that the Duplicates Wizard isn't a useful feature -- just that you now have another, and sometimes even more handy, device in your SQL toolbox.