[AccessD] Mailing Labels

Martin,

I have a function that will "de-normalize" data like this in SQL Server.

Essentially, you will pass in the ID, 1234, and return all of the names in a
comma separated list.  Your query would look something like this:

SELECT
    NameID, GetNameList(NameID), Address
FROM
    YourTableName

You would need to build the function GetNameList.

Here is an example from SQL Server:

CREATE FUNCTION dbo.GetLanguage(@NameID int)
RETURNS varchar(2000)
AS
BEGIN
   DECLARE @Language varchar(2000)
   SET @Language = ''
   SELECT @Language =
      CASE @Language
         WHEN '' THEN ISNULL(LanguageName +'(' + LevelFullFesc + ')','')
         ELSE @Language + ', ' +
              ISNULL(LanguageName +'(' + LevelFullFesc + ')','')
      END
   FROM v_NameLanguage
   WHERE NameID = @NameID
   RETURN @Language
END

This function gets a list of languages a person can speak with the knowledge
level and returns it as a comma separated list.  That should be enough for you
to build the function you need.

Robert L. Stewart
The Dyson Group International
Software for the Non-profit Enterprise
Expanding your Sphere of Knowledge


Quoting accessd-request at databaseadvisors.com:
> Date: Wed, 21 Jun 2006 15:10:43 +0100
> From: "Martin Reid" <mwp.reid at qub.ac.uk>
> Subject: [AccessD] Mailing Labels
> To: "Access Developers discussion and problem solving"
> 	<accessd at databaseadvisors.com>
> Message-ID:
> 	<D0BF9C78D1C45C418541C852C8944FE2A550FD at qub-xchange-01.ads.qub.ac.uk>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Given the following data
>
> 1234,Name,Address
> 1234,Name,Address
> 1234,Name,Address
> 1234,Name,Address
> 2345, Name Address
> 2345,Name Address
>
> I need to get one mailing label produced
>
> 1234, Name1, Name2,Name3,Name4
> Address
>
> 2345,Name1,Name2
> Address
>
> Etc
>
> So I can have multiple people at the same address. I need the name line to
> include all the names but only produce 1 label per address.
>
> Martin