Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Aggregate Total Acreage and Group By for Mail Merge

    I was helped on an earlier question to complete my mail merge with the following code:
    Code:
    select	YourTable.*
    from	YourTable
    	inner join --DistinctNames
    		(select	Max(PrimaryKey) as PrimaryKey
    		from	YourTable
    		group by FirstName,
    			LastName) DistinctNames
    		on YourTable.PrimaryKey = DistinctNames.PrimaryKey
    Basically this code queries my mailing list and ensures that i do not send mutiple letters to one person at the same address who might be in the batabase more than once. However, the reason they are in there more than once is that they might own additional properties. Anyway, I have a column that includes their acreage for each property in each record and I would like to add those up for each person during my query. Thought anyone? Thanks!
    BillS

  2. #2
    Join Date
    Dec 2003
    Posts
    39
    select YourTable.*, DistinctNames.total_acreage
    from YourTable
    inner join --DistinctNames
    (select Max(PrimaryKey) as PrimaryKey, sum(acreage) total_acreage
    from YourTable
    group by FirstName,
    LastName) DistinctNames
    on YourTable.PrimaryKey = DistinctNames.PrimaryKey
    Link Link

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks, the query seems to run withour error, however, the "total acreage" field is simply populated with one of their acreage values, not their total. It looks like the sumation is occurring after the new table is created, which would provide an incorrect result. Any other thoughts?
    BillS

  4. #4
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Nevermind, it worked flawlessly. Thanks a lot!
    BillS

Posting Permissions

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