Totaling like records

[Cow Girl]

I have a huge list of cities and all of the corresponding shipment data for
every shipment during the year -- weight, costs, etc. I have created a query
to get the totals by city. But, my problem is there are some cities that have
been entered differently. For example, Guilderland Center and Guilderland
Ctr.

Is there a way I can the total for Guilderland -- regardless of how it's
listed?

 

[vbasean]

I have a huge list of cities and all of the corresponding shipment data for
every shipment during the year -- weight, costs, etc. I have created a query
to get the totals by city. But, my problem is there are some cities that have
been entered differently. For example, Guilderland Center and Guilderland
Ctr.

Is there a way I can the total for Guilderland -- regardless of how it's
listed?

ooo, yeah, I have similar problems

you can manually create criteria to match all the like names

WHERE City = 'Guilderland Center' OR city = 'Guilderland Ctr'

if you have administrator rights I'd do an update query and make them
all the same

then place some controls to limit the user's ability. What I do is
have the user select a city from a combo box (but the combo is not
limited to the list, that way new city names can be added) My list
source is a totals query based on the city field of that table

hope that helps

 

[Gina Whipp]

Cow Girl,

You can use Like "Guilderland*" but suppose they misspell that! I generally
use a CitiesStatePostalCode table which limits the user to the City names
from the table. True, you have to purchase a complete database of Cities,
States and Zips but I get accurate results when looking for specific Cities.

 

[vbasean]

I had very similar problems

what I did, in a nutshell

run a total's query that groups the city field

you'll have an eyes on view what cities are the major culprits

run some update querys to standardize the city names

create some stops/counter measures to control the input such as

using a combo box that is not limited to the list that uses the same
totals query above mentioned.

 

[KARL DEWEY]

Build a city table by using a totals query changed to Make_Table with two
more fields - Common: "" & Modify: "".
View the table and edit adding character in Update field like 'C' or 'M'
indicating copy or modify. Run an update query to update the Common field
with City field contents. Use select query with 'M' as criteria on Modify
field. Edit the Common field data so that Guilderland Center and Guilderland
Ctr read Guilderland or however the correct entry should be.
Then use a select query to view the data like this --
UPDATE [YourTable], City SET [YourTable].City = [Common]
WHERE ((([YourTable].City) Like "*" & [City].[City] & "*"));

You should use a ListBox on a form that has your city list table as source
so that you only pick from the list for data entry.

 

[Cow Girl]

I appreciate everyone's help. Unfortuantely, the data comes from outside, so
I have no control over it. It is what it is. And, the data is resent every
month with the new shipping information. So, it sounds like I'll have to
update the table every month before I do anything. Thanks again!!

 

[vbasean]

curious, does the data come with postal codes?

I appreciate everyone's help. Unfortuantely, the data comes from outside, so
I have no control over it. It is what it is. And, the data is resent every
month with the new shipping information. So, it sounds like I'll have to
update the table every month before I do anything. Thanks again!!

 

[strive4peace]

postal codes ... that is a good question

if so, you could create a table with the CORRECT city/cities for the
specified postal code ... sometimes the code may be right and the city
wrong -- other times, it may be the other way. Since you get this data
every month, it would be worthwhile to develop code to correct it.

Here is some code you can use to compare city names -- I have done this
on large tables with cities spelled differently and it works like a
champ to wade through the major problems

'~~~~~~~~~~~~~~~~~
'written by R_Cubed ... Rob Richards
'
Public Function fSoundex(Word As String) As String

Dim strCode As String
Dim strChar As String
Dim lngWordLength As Long
Dim strLastCode As String
Dim i As Long

'Grabs the first letter
strCode = UCase(Mid$(Word, 1, 1))
strLastCode = GetSoundCodeNumber(strCode)

'Stores the word length
lngWordLength = Len(Word)

'Continues the code, starting at the second letter
For i = 2 To lngWordLength
strChar = GetSoundCodeNumber(UCase(Mid$(Word, i, 1)))

' If adjacent numbers are the same,
' only count one of them
If Len(strChar) > 0 And strLastCode <> strChar Then
strCode = strCode & strChar
End If
strLastCode = strChar
Next

'Trim it down to a maximum of four characters...
fSoundex = Mid$(strCode, 1, 4)

'... but if it's less than four characters, pad
'it out with a bunch of zeros...
If Len(strCode) < 4 Then
fSoundex = fSoundex & String(4 - Len(strCode), "0")
End If

End Function

Private Function GetSoundCodeNumber(Character As String) As String

'Accepts a character and returns the
'appropriate number from the Soundex table

Select Case Character
Case "B", "F", "P", "V"
GetSoundCodeNumber = "1"
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
GetSoundCodeNumber = "2"
Case "D", "T"
GetSoundCodeNumber = "3"
Case "L"
GetSoundCodeNumber = "4"
Case "M", "N"
GetSoundCodeNumber = "5"
Case "R"
GetSoundCodeNumber = "6"
End Select

End Function
'~~~~~~~~~~~~~~~~~

I would recommend creating a table with:

- zipID, autonumber
- zip, text, 5 (postalCode, text 10 if internt'l addresses)
- city, text, 30
- state, text 2
- soundex, text 10

when you get data, assume the zip/postal code is correct. Add ZipID to
your table and update it where the records match.

Another table you may want to create is for Regions

- RegionID, autonumber
- Region, text

and add RegionID to the Zips table -- this will enable you to group
records for areas.

I have a zip code db of the united states with > 42,000 records. It
also has latitude and longitude, so relative distances can be
calculated. If you want it, email me and I will send it to you.

(e-mail address removed)

Warm Regards,
Crystal

*
have an awesome day
*