In PHP, I do the following (pseudo code for clarity)...
Query: SELECT distinct(web_contacts.zip) FROM web_contacts WHERE
web_contacts.zip <> ''
Query: SELECT DISTINCT(contacts.zip) FROM contacts WHERE contacts.zip <> '';
Take all those results put them into an array:
$zips[$zipCode] = 0;
The zipCode is used as the index to avoid duplicates between tables...
Then I retrieve the counts of these zips:
$zips[$zipCode] = Query: SELECT COUNT(web_contacts.zip) FROM
web_contacts WHERE web_contacts.zip = $zipCode
$zips[$zipCode] += Query: SELECT Count(contacts.zip) FROM contacts WHERE
contacts.zip = $zipCode
$zips=arsort($zips)
So this now gives me an array that has all the different zip codes from
2 tables as indicies, and how many total times each of these zips showed
up as the element's value ordered by most hits to least hits. From that,
you can print a "Top X Zips" report by walking the array and printing
each key and value pair until you've reached the X number you want to
report on.
Great, no problems there... However - It's never simple is it? - I now
need to do the same thing in a Microsoft Access database. I tried this:
[zip1]
SELECT distinct(web_contacts.zip) FROM web_contacts WHERE
web_contacts.zip <> '' UNION ALL SELECT DISTINCT(contacts.zip) FROM
contacts WHERE contacts.zip <> '';
[zip2]
SELECT DISTINCT (zip1.zip) AS zip FROM zip1;
[zip3]
SELECT zip2.zip as zipCode, (SELECT COUNT(web_contacts.zip) FROM
web_contacts WHERE web_contacts.zip = zip2.zip) + (SELECT
Count(contacts.zip) FROM contacts WHERE contacts.zip = zip2.zip) AS
zipCount FROM zip2
[zip4]
SELECT TOP 50 zip3.zipCode, zip3.zipCount FROM zip3 ORDER BY zip3.zipCount;
By the time I get to executing the zip3 query for testing, it takes a
few seconds to get the first part of the data, but takes nearly 4
minutes to retrieve the whole set of 6000 records...
When I run zip4, it chugs along for 12 minutes of nothing, then up pops
an error stating that the "Object invalid or no longer set."
I then changed zip3 to a make table query, and changed zip4 to select
from that table instead. This seems to work, but zip3 still takes about
4 minutes to complete. Is there a more efficient way to handle this?
There are about 8900 records in contacts.
There are about 12500 records in web_contacts.
zip1 returns 7400 results in about 2 seconds.
zip2 returns 6000 records in about 2 seconds.
The database will only grow, and it looks like the rate is on average
500 records/week...
Thanks in Advance!
--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com