Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    nyc
    Posts
    6

    Unhappy Unanswered: query & code help: multiple households, subqueries & nested Fn

    Thank you for your consideration. Using A2K in XP. VbNovice.

    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). Now with households there are three scenarios: 1) two adults same last name; 2) two adults different last names; 3) adult household members not known.

    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 should 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] ex. John and Jane Doe

    ** 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”. I believe that a partial solution for Sc1 is to use a 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.


    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]
    ex. Jane Doe
    John Smith

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

    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? Any advice on quering 4-5 queries? Advise on getting two names in one field? Do you advise simply giving up, create 5 different Print reports (Inv, Co & 3 households)?

    Many thanks for any consideration!

  2. #2
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: query & code help: multiple households, subqueries & nested Fn

    Originally posted by dabowery
    Thank you for your consideration. Using A2K in XP. VbNovice.

    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). Now with households there are three scenarios: 1) two adults same last name; 2) two adults different last names; 3) adult household members not known.

    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 should 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] ex. John and Jane Doe

    ** 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”. I believe that a partial solution for Sc1 is to use a 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.


    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]
    ex. Jane Doe
    John Smith

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

    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? Any advice on quering 4-5 queries? Advise on getting two names in one field? Do you advise simply giving up, create 5 different Print reports (Inv, Co & 3 households)?

    Many thanks for any consideration!


    Hi

    My suggestion would be (if you have no VBA experience) is to create queries for each scenarion, then to create a union query to get the final query for your label.

    Would be easier if: 1: you could send me a copy of your tables and I can attempt to create the queries for you or 2: I attempt to create a db for you.

    1 would be easier.

    Cheers
    Mona
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  3. #3
    Join Date
    Jul 2003
    Location
    nyc
    Posts
    6
    Hi Mona,

    Super! Thanks for inquiring. I have attached a bareboned db with only the tables concerned. If the full db is prefered please advise. I have excluded all modules including any concat functions I've piece mealed together. Please advise if these should be included.

    My aim is 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.

    My comfort level with vb is increasing, so if you prefer I get closer to speed and have suggested KnowledgeBase or ForumIssues to recommend, please feel free to direct me further. Any assistance is invaluable. Thank you again.
    Attached Files Attached Files
    Last edited by dabowery; 07-22-03 at 21:24.

  4. #4
    Join Date
    Jul 2003
    Location
    nyc
    Posts
    6

    Unhappy Re: query & code help: multiple households, subqueries & nested Fn

    Originally posted by mona
    Hi

    My suggestion would be (if you have no VBA experience) is to create queries for each scenarion, then to create a union query to get the final query for your label.

    Would be easier if: 1: you could send me a copy of your tables and I can attempt to create the queries for you or 2: I attempt to create a db for you.

    1 would be easier.

    Cheers
    Mona
    Hi Mona,

    In searching for a solution to code or query for household mailing scenarios I've found an archive [http://dbforums.com/archives/t122783.html] with a partial solution and no further reference. I am not able to get the syntax correct in the code below and need help getting the code to work. I believe I have the SQL straight. Are you able to help with this?

    To follow is an edited/updated version of the post:

    I need to create mailing labels, one per household, from a table in which each person has a different record but a "family code" field provides the right data to group by.

    I have a table [tblMailTest] that includes the first and last name data. I have created a new table called tblAddrName with a family code field (PK) and a field called AddrName in which to store the combined names.

    I would like to apply the following rules to update tblAddrName:
    1. If there is only one record for the family code, do nothing.
    2. If there are two records, and the last name is the same, combine the two as in "John & Mary Smith"
    3. If there are two records and the last name is not the same, combine the two as "John Smith & Mary Jones"
    4. If there are more than two records, the result should be "The Smith Family".

    Private Sub Command0_Click()
    Dim strSQL As String
    Dim rs As Recordset
    Dim rsComb As Recordset
    Dim strOut As String
    Dim tmpfirst(2) As String
    Dim tmplast(4) As String
    strSQL = "SELECT tblMailTest.FamID, tblMailTest.FstName, tblMailTest.LstName, Count(tblMailTest.FamID) AS FID From tblMailTest GROUP BY tblMailTest.FamID, tblMailTest.FstName, tblMailTest.LstName HAVING Count(tblMailTest.FamID) > 1;"

    Set rs = CurrentDb().OpenRecordset(strSQL)
    Set rsComb = CurrentDb().OpenRecordset("tbladdrname")

    With rs

    If FID = 2 Then

    tmplast(1) = LstName tmpfirst(1) = FstName
    .MoveNext
    tmplast(2) = LstName tmpfirst(2) = FstName

    If tmplast(1) = tmplast(2) Then
    strOut = (tmpfirst(1) & " and " & tmpfirst(2) & tmplast(1))
    Else: strOut = (tmpfirst(1) & tmplast(1) & " & " & tmpfirst(2) & tmplast(2))

    End If
    End If

    If FID > 2 Then

    strOut = "The" & LstName & "Family"

    End If
    rsComb.AddNew rsComb!FamilyID = FamID rsComb!tbladdrname = strOut rsComb.Update rsComb.Close
    End Sub

Posting Permissions

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