Create Aggregate Function

  • Thread starter Thread starter Marshall Smith
  • Start date Start date
M

Marshall Smith

OK, I have a problem that would have a very easy, elegant solution IF I had
one missing piece.

I have a Contacts table and a ContactCategories table. Each Contact can
have multiple Categories. Now, I want to create a form that will display
each Contact. In addition to some other filter criteria, I would like it
for the user to be able to show only Contacts belonging to a given Category.

If I simply include the ContactCategories in the form recordsource, it gives
me a copy of each Contact for each ContactCategory. This is not desirable.

If I do not include it, and put the ContactCategories in a separate pop-up
form, then filtering the records by Category is a tricky, laborious process.
I can do it, but I don't want to.

Now, the elegant solution is to create an aggregate function that would
simply concatenate all of the Category names in the recordsource query,
grouped by Contact. One record per contact, but it can be filtered by using
LIKE on the concatenated string. However, there is no concatenation
aggregate function that I can find.

Does anyone know how I could go about creating a new aggregate function?
Or, a different solution for this problem?
 
Hi,

You use a WHERE clause. The WHERE clause occurs BEFORE the aggregate (a
HAVING clause occurs after).

SELECT ContactID, LAST(ContactName), LAST(Telephone)
FROM ContactCategories
WHERE Category IN( ...list of constant... )
GROUP BY ContactID



Here, the WHERE clause eliminates the category you are not interested to
see, and once that filter is done, you can use the standard GROUP BY, or
whatever.

Using a LIKE operator on a constant prefixes with the wildcard is
relatively slow, since that cannot make use of an index, so a table scan is
the only solution left, probably, which mean that much of the data will be
pump from hard disk (and transfer through the network cable) to consume not
negligible time ... to found it is to be rejected (by the criteria) !

If you really want a list aggregate, Steve Dassin once posted a solution
based on crosstab (sure, that makes it applicable for JET only, and those
using Steve's add on for MS SQL Server).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top