Patient Table Sort (1 Viewer)

[MikeSr]

I have a patient table with The following Fields:
Last_Name
First_Name
DOB
Phone
Address
City
State
Zip

We are want to send out mailers to our patients. As an example, we have four family members at the same address and phone. We only want to send one mailer to the household. We want to address this as the "Last_Name" & Family instead of the first name.
When the phone number and address are individuals then we will use the First_Name and Last_Name fields in the address.
I am stuck trying to generate the correct sort algorithm. Any help would be appreciated,

Kind Regards,
Michael

 

[DCrake]

Firstly you need to create a seperate query that groups by Surname by zipcode (hopefully you will not have 2 different families with the same surname with the same zip code.

You need to incluce a count that tells you how many it finds.

Then for the adddressee

Addressee:iif(Count(1)>1,[LastName] & " & family","[Firstname] & " " & [Lastname])

Then you can use this as the basis for your address labels.

 

[Kryst51]

Just an idea, I don't know if it will work, but try doing a query that groups by last name and address, do another query that counts first names at the address, then include that count in your query that groups by last name and address, (Then again maybe you can do it all with one query).

Then you could do something like, iif(count = 1,[firstname]+" "[LastName],[LastName]+" "+"Family]


Edit: DCracke beat me

 

[MikeSr]

I was able to get the sort to show the correct count by Last name and phone number. There are to many surnames that are the same in the same zip, so Phone worked just fine.
I am stuck with how to implement your algorithm. Can you provide more detail as to how I go about using the query that generated the count and the table that contains the address data?

Kind Regards,

 

[MikeSr]

I am stuck with my query. As I mentioned I was able to get the count. How do I use the count to add the new data? Do I create a new field in my "Customer" table and add the String data and or the first name to that field?
I have tried to implement the "Addressee" as a field name in the query, but that didn't provide any results. I substituted the field names accordingly based on the first example in this tread.

Kind Regards,
Michael

 

[PNGBill]

The long way is to generate the selection, create a temptable and group on Lastname, TelNum and where count >1 move these entries to another temptable.
Update the new temptable so you only have the first entry for each Lastname, TelNum and then update the mailing details so it reads LastName & Family.
Then append result back to original temp table and you have a complete list to run your mailmerge/labels. You can add a line in the macro to delete the temptables when done.