code or query for household mailing scenarios?

[dabowery]

Thank you for viewing. I believe this is an VbIssue, but if it's better posted in queries, please advise. Using A2K in XP. VbNovice. This post has been edited to provide more detail.

I need to print mailing labels for Individuals, Companies and Households. For Households I have big troubles. I'm trying to get a record set to print labels in a specific format according to Household information.

The tblHousehold is parent of the tblHouseMem. Each household member has a Type (Family Contact, Spouse, Partner, Family Member, Juvenile). Addresses come from tblAdr and tblAdrMem. The scenarios for the Print Label differ according to HouseholdMember Type. Scenarios follow and assumes address info follows immediately thereafter:

1. Household has a Family Contact and a Spouse or Partner with the same last name.
Print Line1 [Forename Family Contact] “and” [Forename Spouse or Partner] [LastName]

** Additionally, for Sc1, I don’t know a solution to concatenate the two Forename fields within the same record set and then get their names to print on line 1 e.g., if the AdrMemID and FamilyID for any Contact are equal and the SurNames are equal then get each ForeName in the Label’s ForeName field separated by “and”.

2. Household has a Family Contact and a Spouse or Partner with different last names.
Print Line1 [Forename Family Contact] [Surname FamilyContact]
Print Line2 [Forename Spouse or Partner] [Surname Spouse or Partner]

3. Household does not have an Adult associated with it.
Print Line1 “The ”[Surname Juvenile or FamilyMember]“ Household”

** Additionally, in this overall query, is there any advise on getting both the Households with Adults and Households without Adults to be on the same list WITHOUT including Juveniles of households with adults?


I don’t know where to begin: vbcode or query. Is it best to simply create one query for each scenario and then query these in order to get something to base a PrintLabel Report on? If so, any advice on quering 4-5 queries? Advise on getting two names in one field?

Many thanks for any consideration!

Comment added July 19th:
I believe that a partial solution for Sc1 is to use a Fn fConcatChild to get both FirstNames in one field and then some type of Fn to replace the ";" character in this string to "and". But I still unable to get this done with any confidence.

 

[neileg]

What you want to do, concatenating data from different records, is not easy. You could look at MS support article 141624 but this relates to reports, not queries.

I presume that fConcatChild is a custom function. I would have thought that it could be modified to include 'and' rather than ';'

 

[dabowery]

thnks

thank you for replying. re:string replacement, yes, i can replace the ";" with text, yet when doing so the code returns the text regardless of meeting the code's requirements or not. so, i'll continue with this as best i can including covering articles you reference.

thanks again for your consideration!

 

[dabowery]

I am in dire need of any guidance or help.

I need to get a print Report for mailing labels of Individuals, Companies and Households: Individuals (everyone not associated with a household or company); Companies (addressed to the Co Contact); Households (individuals associated with a household). For households there are three scenarios: 1) two adults same last name; 2) two adults different last names; 3) adult household members not known. Details are above.

I do not know how to go about creating the queries and the functions needed to feed the report. I have been trying to piece meal together code and queries to address some of the groupings and formatting, but I have not been able to get anything together that is cohesive or comprehensive. Your insight is truly needed. Thanks you in advance for your assistance.

 

[neileg]

The reason why you are struggling with this is that it is genuinely difficult to do.

From your description of your tables, you have properly normalised the data, so that tblHouseMem holds the house occupants in individual records linked to the house ID in tblHousehold. You also have one of these entries flagged as the contact.

Obviously, it is relatively easy to extract data with the address and the unique contact name. (I suggest that where you have on a juvenile contact that you add an extra record for 'The Parents or Guardians of'). This should enable you to send a mailshot addressed to the single contact.

However, to concatenate data from different records in the same table is difficult, which is why I suggested the MS article that I did.

You should look at this thread
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=49693&highlight=141624

Quite frankly, I don't know how to do what you are asking for!

 

[dabowery]

Thank you for your insight. I believe I will be doing just queries and may have to shelf the idea until a later date. There's one day left for me to mine an approach. Again, thank you for taking your time to help.