Debbie Elam
delam at zyterra.com
Thu Sep 13 11:55:44 CDT 2007
Maybe you could set a default contact person, and query only for the default contact when you want one mailing per address to go out. The only issue I see is that each address would require one default contact. Debbie -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Thursday, September 13, 2007 11:46 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] First in a group So if you have parent/children or parent/grandparents living at the same address it is pot luck as to who gets the mailing? The child or grandparent or inlaw for that matter might get a letter intended for the parent? That could be interesting! :-) Jim Hale -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, September 13, 2007 11:11 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] First in a group Thanks all who replied. I added an autonumber ID to the table. 1) Extracted all matching records to a temp table. 2) Added an autonumber ID so I could uniquely point to any given record. 3) Created qFirstID where I Grouped By the address hash and pulled the First(ID) That gave me the correct subset of records where there is only one ID for each address. 4) Joined that ID from qFirstID with tblOrder on the ID in qOrderFirstID 5) Selected all the name / address fields and viewed them to ensure no dupes. NO DUPES. 6) Used that to export the data Again thanks for the suggestions. I really have to write up a document of how I do this stuff!!! John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, September 13, 2007 11:07 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] First in a group John, Group on the address and use First([person]) to get the nanme. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, September 13, 2007 7:06 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] First in a group I have a set of address records where there are several people at the address. I need to send only one piece of mail to that address, the person doesn't matter. John Colby, Some Street, SomeCity, SomeState, SomeZip Mary Colby, Some Street, SomeCity, SomeState, SomeZip John Doe, Some Other Street, Some Other City, Some Other State, Some Other Zip Mary Doe, Some Other Street, Some Other City, Some Other State, Some Other Zip I don't care whether I send to Mary or John, I only care that only a single piece of mail goes to each address. Select distinct of course acts on the entire row and so returns all 4 names. I do have an address hash that generates a hash string for just the address part. So it seems like something like a group by address hash and then a top1 is the right direction, but of course it has to be the TOP 1 in each GROUP. I could (and might have to to get this out) write a little VBA routine to do this but I would rather use a SQL solution. So how is this done in SQL. John W. Colby Colby Consulting www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com