D
dabowery
i am in dire need of help. i have located a thread in archive section,
which i can no longer open for some reason. i cannot locate the thread
that is cited in this archived thread so i tried to work it out.
i have corrected the slq statement, but i can't get the vb syntax
straight. i'm a vb novice and can't get this code correct. this
archived thread is what i need, but it isn't working. any advise
regarding the solution below? thanks.
Combining Names for Family Mailing
From: John Williams
Date: 08-08-01 19:13
Subject:
----------------------------------------------------------------------
----------
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 that includes the first and last name data. I have
created a new table called AddrName with a family code field (primary
key) and a field called AddrName in which to store the combined names.
I would like to apply the following rules to update the AddrName table:
If there is only one record for the family code, do nothing.
If there are two records, and the last name is the same, combine the two
as in "John & Mary Smith"
If there are two records and the last name is not the same, combine the
two as "John Smith & Mary Jones"
If there are more than two records, the result should be "The
Smith Family".
The following is code I created in an attempt to do this (thanks are due
Allen Browne for his post of 2000/03/24 on a similar subject).
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 FstName, LstName, NameSfx, FamID, " _ & "COUNT(FamID)
as FID FROM 31057 GROUP
BY (FamID,FstName,LstName,NameSfx)" _ & "HAVING Count(FamID) > 1;"
Set rs = CurrentDb().OpenRecordset(strSQL)
Set rsComb = CurrentDb().OpenRecordset("addrname")
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!AddrName = strOut
rsComb.Update rsComb.Close
End With End Sub
My problem is that I get: Run time error 3122: You tried to execute a
query that does not include the specified expression "FstName" as part
of an aggregate function.
I thought that if I included FstName in the GROUP BY statement that it
would be included in the aggregate function, but apparently not. I would
appreciate any help on this. Thanks.
From: Allen Browne
Date: 08-09-01 03:12
Subject:
----------------------------------------------------------------------
----------
John, try creating a query by pasting your SQL string into SQL View of a
new query. Tidy up the syntax and try running it.
The Query grid can help identify what's wrong. VBA knowns nothing about
SQL syntax.
which i can no longer open for some reason. i cannot locate the thread
that is cited in this archived thread so i tried to work it out.
i have corrected the slq statement, but i can't get the vb syntax
straight. i'm a vb novice and can't get this code correct. this
archived thread is what i need, but it isn't working. any advise
regarding the solution below? thanks.
Combining Names for Family Mailing
From: John Williams
Date: 08-08-01 19:13
Subject:
----------------------------------------------------------------------
----------
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 that includes the first and last name data. I have
created a new table called AddrName with a family code field (primary
key) and a field called AddrName in which to store the combined names.
I would like to apply the following rules to update the AddrName table:
If there is only one record for the family code, do nothing.
If there are two records, and the last name is the same, combine the two
as in "John & Mary Smith"
If there are two records and the last name is not the same, combine the
two as "John Smith & Mary Jones"
If there are more than two records, the result should be "The
Smith Family".
The following is code I created in an attempt to do this (thanks are due
Allen Browne for his post of 2000/03/24 on a similar subject).
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 FstName, LstName, NameSfx, FamID, " _ & "COUNT(FamID)
as FID FROM 31057 GROUP
BY (FamID,FstName,LstName,NameSfx)" _ & "HAVING Count(FamID) > 1;"
Set rs = CurrentDb().OpenRecordset(strSQL)
Set rsComb = CurrentDb().OpenRecordset("addrname")
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!AddrName = strOut
rsComb.Update rsComb.Close
End With End Sub
My problem is that I get: Run time error 3122: You tried to execute a
query that does not include the specified expression "FstName" as part
of an aggregate function.
I thought that if I included FstName in the GROUP BY statement that it
would be included in the aggregate function, but apparently not. I would
appreciate any help on this. Thanks.
From: Allen Browne
Date: 08-09-01 03:12
Subject:
----------------------------------------------------------------------
----------
John, try creating a query by pasting your SQL string into SQL View of a
new query. Tidy up the syntax and try running it.
The Query grid can help identify what's wrong. VBA knowns nothing about
SQL syntax.