|
 |
access thread: Fwd: Re: Concantenate Same Field in Query
Message #1 by pmcmillin@m... on Mon, 26 Mar 2001 13:04:02 -0600
|
|
--=====================_8132413==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
Just thinking. In the following statement, how does it distinguish between
each record's FName.
If strTempCompare = "" Then
'If strTempCompare is empty then set the strings to equal the
'appropriate fields in the rstTemp recordset
strFName = rstTemp!FIRST
strLName = rstTemp!LAST
strAddress = rstTemp!Address
strCity = rstTemp!CITY
strZip = rstTemp!ZIP
strTempCompare = rstTemp!LAST & rstTemp!Address
ElseIf strTempCompare = rstTemp!LAST & rstTemp!Address Then
'If strTempCoompare is equal to the current lname & address
'in the rstTemp recordset, add the First Name of the
'current record to the strFName string
strFName = strFName & " & " & rstTemp!FIRST
rstTemp!First has already been assigned in the first If statement. Doesn't
it retain it's value? I realize that in the second ElseIf we are saying
strFname = strFname concantenated with the rstTemp!First. But we already
said in the first If that rstTemp!First was already assigned.
I need it to look at another recordset and pick up that first name if the
LName and Address are the same. Like move to next record where LName and
Address = each other.
Maybe I am not setting it up properly? Really it should repeat the FName
twice, but it doesn't.
I wonder why that is?
>Date: Mon, 26 Mar 2001 12:34:19 -0600
>To: "Access" <access@p...>
>From: Pam McMillin <pmcmillin@m...>
>Subject: Re: [access] Re: Concantenate Same Field in Query
>
>John,
>
>I followed your instructions (which were brilliant), but I am still
>getting a separate record for each voter. Even if they have the same
>address. It isn't duplicating all the fields that I was getting. But I
>now have two fields for one couple. Each field has only one FName.
>
>Here is the code I copied from you:
>
>Public Sub Mailings()
> Dim rstTemp As Recordset
> Dim rstMail As Recordset
> Dim strTempCompare As String
> Dim strFName As String
> Dim strLName As String
> Dim strAddress As String
> Dim strCity As String
> Dim strZip As String
>
> 'Turn program warnings off
> DoCmd.SetWarnings False
>
> 'Delete any records in the temp table tblMailings
> DoCmd.RunSQL ("DELETE * FROM tblMailings")
>
> Set rstTemp = CurrentDb.OpenRecordset("qSel_Mailings")
> Set rstMail = CurrentDb.OpenRecordset("tblMailings")
>
> strTempCompare = ""
> strFName = ""
> strLName = ""
> strAddress = ""
> strCity = ""
> strZip = ""
>
> Do While Not rstTemp.EOF
> If strTempCompare = "" Then
> 'If strTempCompare is empty then set the strings to equal the
> 'appropriate fields in the rstTemp recordset
> strFName = rstTemp!FIRST
> strLName = rstTemp!LAST
> strAddress = rstTemp!Address
> strCity = rstTemp!CITY
> strZip = rstTemp!ZIP
> strTempCompare = rstTemp!LAST & rstTemp!Address
> ElseIf strTempCompare = rstTemp!LAST & rstTemp!Address Then
> 'If strTempCoompare is equal to the current lname & address
> 'in the rstTemp recordset, add the First Name of the
> 'current record to the strFName string
> strFName = strFName & " & " & rstTemp!FIRST
> ElseIf strTempCompare <> rstTemp!LAST & rstTemp!Address Then
> 'If strTempCompare IS NOT equal tothe current LAST & Address
> 'in the rstTemp recordset, add the appropriate strings to the
> 'rstMail table
> rstMail.AddNew
> rstMail!Name = strFName & " " & strLName
> rstMail!Address = strAddress
> rstMail!CITY = strCity
> rstMail!ZIP = strZip
> rstMail.Update
> 'After adding the new record, re-populate the appropriate
> strings
> 'with the current record in the rstTemp recordset
> strFName = rstTemp!FIRST
> strLName = rstTemp!LAST
> strAddress = rstTemp!Address
> strCity = rstTemp!CITY
> strZip = IIf(IsNull(rstTemp!ZIP), "", rstTemp!ZIP)
> End If
> rstTemp.MoveNext
> Loop
>
> 'Turn program warnings back on
> DoCmd.SetWarnings True
>
> rstTemp.Close
> rstMail.Close
>
> Set rstTemp = Nothing
> Set rstMail = Nothing
>
>End Sub
>
>
>Here is a sample of the data I am getting.
>
>NameAddressCityZip
>JOHN AARON4102 WOODCREST DRBRYAN77802
>JAMES ABBOTT3510 SPRING LNBRYAN77802
>LOUISE ABBOTT3510 SPRING LNBRYAN77802
>CATHERINE ABSHIRE3624 RABBIT LNBRYAN77808
>DON ADAM718 N ROSEMARY DRBRYAN77802
>DONNA ADAM718 N ROSEMARY DRBRYAN77802
>At 10:03 AM 3/23/2001 -0800, you wrote:
>>Pam,
>>
>>I would recommend this:
>>
>>1. Create a temporary table (tbl_Mailings maybe?) and in the table add the
>>appropriate fields you need to create your labels.
>>2. Create a GROUPED query and include, in this order LASTNAME, HOUSENUMBER,
>>STREET, CITY, STATE, ZIP, FIRSTNAME.
>>3. This query will automatically sort the records in the order Lastname,
>>housenumber, street, city, state, zip, and firstname.
>>4. Create a procedure that looks for the Lastname, housenumber,street, city,
>>state, zip and compares these values with
>>
>>The samples I have below are based on the Northwind database so there will
>>be some differences than what you are looking for, but the principles are
>>the same.
>>
>>My table tblMailings have the following fields in it:
>>Name - Text
>>Address - Text
>>City - Text
>>Zip - Text
>>
>>My query qSel_Mailings looks like this: (I created a temp table - tblTemp
>>so that I could separate the First and Last Names from the Customers table
>>in the Northwind database.
>>SQL statement -
>>SELECT tblTemp.Lname, tblTemp.Address, tblTemp.City, tblTemp.PostalCode,
>>tblTemp.Fname
>>FROM tblTemp
>>GROUP BY tblTemp.Lname, tblTemp.Address, tblTemp.City, tblTemp.PostalCode,
>>tblTemp.Fname;
>>
>>The Procedure that does all the work looks like this:
>>
>>Public Sub Mailings()
>>
>> Dim rstTemp As Recordset
>> Dim rstMail As Recordset
>> Dim strTempCompare As String
>> Dim strFName As String
>> Dim strLName As String
>> Dim strAddress As String
>> Dim strCity As String
>> Dim strZip As String
>>
>> ' Turn program warnings off
>> DoCmd.SetWarnings False
>>
>> ' Delete any records in the temp table tblMailings
>> DoCmd.RunSQL ("DELETE * FROM tblMailings")
>>
>> Set rstTemp = CurrentDb.OpenRecordset("qSel_Mailings")
>> Set rstMail = CurrentDb.OpenRecordset("tblMailings")
>>
>> strTempCompare = ""
>> strFName = ""
>> strLName = ""
>> strAddress = ""
>> strCity = ""
>> strZip = ""
>>
>> Do While Not rstTemp.EOF
>> If strTempCompare = "" Then
>> ' If strTempCompare is empty,
>> ' then set the strings to equal the
>> ' appropriate fields in the rstTemp recordset
>> strFName = rstTemp!Fname
>> strLName = rstTemp!Lname
>> strAddress = rstTemp!Address
>> strCity = rstTemp!City
>> strZip = rstTemp!PostalCode
>> strTempCompare = rstTemp!Lname & rstTemp!Address
>> ElseIf strTempCompare = rstTemp!Lname & rstTemp!Address Then
>> ' If strTempCompare is equal to the current Lname & Address
>> ' in the rstTemp recordset, add the First Name of the
>> ' current record to the strFName string
>> strFName = strFName & " & " & rstTemp!Fname
>> ElseIf strTempCompare <> rstTemp!Lname & rstTemp!Address Then
>> ' if strTempCompare IS NOT equal to the current Lname & Address
>> ' in the rstTemp recordset, add the appropriate strings to the
>> ' rstMail table
>> rstMail.AddNew
>> rstMail!Name = strFName & " " & strLName
>> rstMail!Address = strAddress
>> rstMail!City = strCity
>> rstMail!PostalCode = strZip
>> rstMail.Update
>> ' After adding the new record, re-populate the appropriate
>> ' strings with the current record in the rstTemp recordset
>> strFName = rstTemp!Fname
>> strLName = rstTemp!Lname
>> strAddress = rstTemp!Address
>> strCity = rstTemp!City
>> strZip = IIf(IsNull(rstTemp!PostalCode), "", rstTemp!PostalCode)
>> strTempCompare = rstTemp!Lname & rstTemp!Address
>> End If
>> rstTemp.MoveNext
>> Loop
>>
>> ' Turn program warnings back on
>> DoCmd.SetWarnings True
>>
>> rstTemp.Close
>> rstMail.Close
>>
>> Set rstTemp = Nothing
>> Set rstMail = Nothing
>>
>>End Sub
>>
>>If you would like me to send you the samples just email me and it shall be
>>done.
>>
>>Good luck,
>>
>>John Ruff - The Eternal Optimist :)
>> -----Original Message-----
>>From: Pam McMillin [mailto:pmcmillin@m...]
>>Sent: Friday, March 23, 2001 7:50 AM
>>To: Access
>>Subject: [access] Re: Concantenate Same Field in Query
>>
>>Also when I do this I am getting every combination of the two names. I am
>>getting results where Don Adams and Donna Adams are:
>>
>>Don Don Adams
>>Don Donna Adams
>>Donna Don Adams
>>Donna Donna Adams
>>
>>How do you only pull out one of these?
>>
>>At 03:32 PM 3/23/2001 +0000, you wrote:
>> >I have a database that contains voter information. The fields are split
>> >out by LASTNAME, FIRSTNAME, MIDDLEINITIAL, HOUSENUMBER, STREET, CITY,
>> >STATE, ZIP.
>> >
>> >I am needing to create labels that will look for duplicates in the
>> >combination of LASTNAME, HOUSENUMBER AND STREET. And then concantenate the
>> >FIRSTNAME fields in the two records.
>> >
>> >Is this possible?
>> >
>> >I had thought about using the IIF statement, but I am not sure where to
>> >start.
>>
>>Pam McMillin
>>Systems Technician
>>
>>Schlumberger - IPM Asset Management
>>500 W. Texas, Suite 500
>>P.O. Box 2726
>>Midland, TX 79702-2726
>>Office 915/571-4600
>>Cell 915/556-0749
>>Fax 915/571-4788
>>pmcmillin@m...
>>
>
>Pam McMillin
>Systems Technician
>
>Schlumberger - IPM Asset Management
>500 W. Texas, Suite 500
>P.O. Box 2726
>Midland, TX 79702-2726
>Office 915/571-4600
>Cell 915/556-0749
>Fax 915/571-4788
>pmcmillin@m...
Pam McMillin
Systems Technician
Schlumberger - IPM Asset Management
500 W. Texas, Suite 500
P.O. Box 2726
Midland, TX 79702-2726
Office 915/571-4600
Cell 915/556-0749
Fax 915/571-4788
pmcmillin@m...
|
|
|
View Cart
