|
 |
access thread: Concantenate Same Field in Query
Message #1 by "Pam McMillin" <pmcmillin@m...> on Fri, 23 Mar 2001 15:32:20
|
|
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.
Message #2 by pmcmillin@m... on Fri, 23 Mar 2001 09:50:00 -0600
|
|
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...
Message #3 by pmcmillin@m... on Fri, 23 Mar 2001 11:30:53 -0600
|
|
--=====================_12309269==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
Here is a sample of what I am trying to do and the results I am getting. I
have created three queries to work with the data. One that gives me the
DISTINCT LASTNAME, NUMBER, STREET and then two others that give me the same
information only I use alias' for the FIRSTNAME.
Couple: IIf([qryDistinctLastNameAddress]![LAST]=[qrySpouseFirst]![LAST]
And [qryDistinctLastNameAddress]![NUMBER]=[qrySpouseFirst]![NUMBER]
And [qryDistinctLastNameAddress]![STREET]=[qrySpouseFirst]![STREET]
And [qrySpouseFirst]![SPOUSEFIRST]<>[qrySpouseSecond]![SPOUSESECOND],
[SPOUSEFIRST] & " " & [SPOUSESECOND],[SPOUSEFIRST])
Last NameCoupleHouse NumberStreet AddressCityStateZip Code
ABBOTTJAMES3510SPRING LNBRYANTX77802
ABBOTTJAMES LOUISE3510SPRING LNBRYANTX77802
ABBOTTLOUISE JAMES3510SPRING LNBRYANTX77802
ABBOTTLOUISE3510SPRING LNBRYANTX77802
At 11:35 AM 3/23/2001 -0500, you wrote:
>Hi. I don't know if you are using an Access backend or not. This works in
>access 97. Please see attached word file.
>This will filter out duplicate records by whichever field you choose. Also,
>you can use the SQL to create a recordset
>of rows which are unique by record, using SELECT DISTINCTROW . (In access,
>look in the properties for the query, and set
>unique records to YES or unique values to YES (can't set both to yes).
>
>An excerpt from Access help:
>Only one table use:
>You can use the UniqueValues property when you want to omit records that
>contain duplicate data in the fields displayed in Datasheet view. For
>example, if a query's output includes more than one field, the combination
>of values from all fields must be unique for a given record to be included
>in the results.
>
>Note The UniqueValues property applies only to append and make-table
>action queries and select queries.
>
>The UniqueValues property uses the following settings.
>Setting Description
>Yes Displays only the records in which the values of all fields
>displayed in Datasheet view are unique.
>No (Default) Displays all records.
>You can set the UniqueValues property in the query's property sheet or in
>SQL view of the Query window.
>
>Note You can set this property when you create a new query by using an
>SQL statement. The DISTINCT predicate corresponds to the UniqueValues
>property setting. The DISTINCTROW predicate corresponds to the
>UniqueRecords property setting.
>
>When you set the UniqueValues property to Yes, the results of the query
>aren't updatable and won't reflect subsequent changes made by other users.
>The UniqueValues and UniqueRecords properties are related in that only one
>of them can be set to Yes at a time. When you set the UniqueValues property
>to Yes, for example, Microsoft Access automatically sets the UniqueRecords
>property to No. You can, however, set both of them to No. When both
>properties are set to No, all records are returned.
>
>more than one table use:
>You can use the UniqueRecords property to specify whether to return only
>unique records based on all fields in the underlying data source, not just
>those fields present in the query itself.
>
>Note The UniqueRecords property applies only to append and make-table
>action queries and select queries.
>
>The UniqueRecords property uses the following settings.
>Setting Description
>Yes Doesn't return duplicate records.
>No (Default) Returns duplicate records.
>You can set the UniqueRecords property in the query's property sheet or in
>SQL view of the Query window.
>
>Note You set this property when you create a new query by using an SQL
>statement. The DISTINCTROW predicate corresponds to the UniqueRecords
>property setting. The DISTINCT predicate corresponds to the UniqueValues
>property setting.
>
>You can use the UniqueRecords property when you want to omit data based on
>entire duplicate records, not just duplicate fields. Microsoft Access
>considers a record to be unique as long as the value in one field in the
>record differs from the value in the same field in another record.
>The UniqueRecords property has an effect only when you use more than one
>table in the query and select fields from the tables used in the query. The
>UniqueRecords property is ignored if the query includes only one table.
>
>The UniqueRecords and UniqueValues properties are related in that only one
>of them can be set to Yes at a time. When you set UniqueRecords to Yes, for
>example, Microsoft Access automatically sets UniqueValues to No. You can,
>however, set both of them to No. When both properties are set to No, all
>records are returned.
>
>Hope this helps a bit. Carol
>Carol(See attached file: Query To Filter Unique data.doc)
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...
Message #4 by "John Ruff" <papparuff@c...> on Fri, 23 Mar 2001 10:03:40 -0800
|
|
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...
Message #5 by "Victor K Heyman" <victor@h...> on Sat, 24 Mar 2001 05:20:26
|
|
> 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.
>
John Ruff's submission is exactly what I need for a similar project.
However the lines setxxx=currentdb.openrecordset("xxxx") produce type
mismatch errors. having dim'ed temp and mail as recordset's, how can this
be? I'm running access2000 with both doa and ado references. I'd love to
get over this hurdle.
Thanks,
Vic
Message #6 by "John Ruff" <papparuff@c...> on Sat, 24 Mar 2001 06:34:52 -0800
|
|
To use the code with Access 2000 you must do two things.
1. Make sure you have a reference to the DAO Object Library. At the top
menu's, select Tools > References and then select Microsoft DAO x.xx Object
Library.
2. Change the Dim statements to the following:
3.
Dim rstTemp As DAO.Recordset
Dim rstMail As DAO.Recordset
This will resolve your problem.
John Ruff - The Eternal Optimist :)
-----Original Message-----
From: Victor K Heyman [mailto:victor@h...]
Sent: Saturday, March 24, 2001 5:20 AM
To: Access
Subject: [access] Re: Concantenate Same Field in Query
> 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.
>
John Ruff's submission is exactly what I need for a similar project.
However the lines setxxx=currentdb.openrecordset("xxxx") produce type
mismatch errors. having dim'ed temp and mail as recordset's, how can this
be? I'm running access2000 with both doa and ado references. I'd love to
get over this hurdle.
Thanks,
Vic
Message #7 by pmcmillin@m... on Mon, 26 Mar 2001 12:34:19 -0600
|
|
--=====================_6349780==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
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...
|
|
|
View Cart
