Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    tblTrinity includes the fields UniqueID, LastName, FirstName, HouseNbr, Street, City

    How do I make a function that groups all names living at the same HouseNbr, Street, and City...so that only 1 mailing goes to that address, but all names show?

    Say that all live at 1 Pinnacle Drive...rather than 3 envelopes being mailed there, I would like the envelope to show
    Fred, Samantha and Julia Bailey
    1 Pinnacle Drive
    Guelph, ON N1k 5L5

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The attachment to Post 301393 contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module.

    Create a query that groups by City, Street and HouseNbr.
    Create a calculated column that uses the Concat function to concatenate the first names.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786827' date='30-Jul-2009 15:37']The attachment to Post 301393 contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module.

    Create a query that groups by City, Street and HouseNbr.
    Create a calculated column that uses the Concat function to concatenate the first names.[/quote]
    Thanks, Hans.

    I assume the following to be correct for the calculated column
    [codebox]Expr1: Concatenate("tblTrinity","FirstName","LastName","H ouseNbr = " & [HouseNbr] And "Street = " & [Street])[/codebox]
    but that gives me "Too many parameters. Expected 1"

    When I change the column to the following...i.e. remove "LastName"
    [codebox]Expr1: Concatenate("tblTrinity","FirstName","HouseNbr = " & [HouseNbr] And "Street = " & [Street])[/codebox]
    the return in the query is every first name in the db on each row in the query result.

    Obviously I am doing something incorrectly.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Expr1: Concatenate("tblTrinity","FirstName","HouseNbr = " & [HouseNbr] & " And Street = " & Chr(34) & [Street] & Chr(34))

    This assumes that Street is a text field and HouseNbr a number field. If HouseNbr is a text field too:

    Expr1: Concatenate("tblTrinity","FirstName","HouseNbr = " & Chr(34) & [HouseNbr] & Chr(34) & " And Street = " & Chr(34) & [Street] & Chr(34))

    You will probably add the city to the condition too.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786868' date='30-Jul-2009 18:57']Try this:

    Expr1: Concatenate("tblTrinity","FirstName","HouseNbr = " & [HouseNbr] & " And Street = " & Chr(34) & [Street] & Chr(34))

    This assumes that Street is a text field and HouseNbr a number field. If HouseNbr is a text field too:

    Expr1: Concatenate("tblTrinity","FirstName","HouseNbr = " & Chr(34) & [HouseNbr] & Chr(34) & " And Street = " & Chr(34) & [Street] & Chr(34))

    You will probably add the city to the condition too.[/quote]
    Correct that the HouseNbr is a text field, because it could be something such as 9A. But it turns out this is more complicated than I was posting!

    The table, tblTrinity, includes FirstName, FirstName2, LastName, LastName2.
    So James Ford and Susan James could be living at 1 Pinnacle Cr. ... but also Susan James' son Brian could also reside with them.

    So I have constructed a query to pull together FirstName, FirstName2, LastName, LastName2 ... plus the full address, as the street will be absent in the case of a rural route.

    SELECT [FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName]),[LastName] & " & " & [FirstName2] & " " & [LastName2]) AS CombinedNames, tblTrinity.Person1Removed, tblTrinity.Person2Removed, tblTrinity.BothRemoved, tblTrinity.HouseNbr, tblTrinity.Street, tblTrinity.City, tblTrinity.Province, tblTrinity.Code, [HouseNbr] & " " & Nz([Street]) AS FullAddress, tblTrinity.LastName, tblTrinity.FirstName
    FROM tblTrinity
    GROUP BY [FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName]),[LastName] & " & " & [FirstName2] & " " & [LastName2]), tblTrinity.Person1Removed, tblTrinity.Person2Removed, tblTrinity.BothRemoved, tblTrinity.HouseNbr, tblTrinity.Street, tblTrinity.City, tblTrinity.Province, tblTrinity.Code, [HouseNbr] & " " & Nz([Street]), tblTrinity.LastName, tblTrinity.FirstName
    HAVING (((tblTrinity.Person1Removed)=False) AND ((tblTrinity.Person2Removed)=False) AND ((tblTrinity.BothRemoved)=False))
    ORDER BY tblTrinity.LastName, tblTrinity.FirstName;

    I then constructed a second query to attempt to concatenate the residents of the addresses, so there is only one mailing to each address.

    SELECT qryCombinedNames.CombinedNames, qryCombinedNames.FullAddress, qryCombinedNames.City, qryCombinedNames.Province, qryCombinedNames.Code, (Concatenate("qryCombinedNames","CombinedNames","F ullAddress = " & Chr(34) & [FullAddress] & Chr(34))) AS AllNames, qryCombinedNames.LastName, qryCombinedNames.FirstName
    FROM qryCombinedNames
    GROUP BY qryCombinedNames.CombinedNames, qryCombinedNames.FullAddress, qryCombinedNames.City, qryCombinedNames.Province, qryCombinedNames.Code, qryCombinedNames.LastName, qryCombinedNames.FirstName
    ORDER BY qryCombinedNames.LastName, qryCombinedNames.FirstName;

    This returns 366 records, which is about 40 records too many, as there are duplicates. An example is Jeff, Sarah and Sue Chadder all live at the same address, but the return in the recordset produced by the query gives all three of those, concatenated properly, but appearing 3 separate times.

    Tom

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What happens if you make a third query to select the distinct records from the 2nd query?
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='786937' date='31-Jul-2009 05:15']What happens if you make a third query to select the distinct records from the 2nd query?[/quote]
    Yep, John...that does it.

    Thanks.

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •