I am working with a commercially supplied database of zipcode numbers and
related data. In the documentation, the following pseudo-coded SQL queries are
included. I am mystified by two things here:
1) [Zip Code Distance Extractor] <-- I have no clue as to what this is
referring to
2) the use of the "!" operator makes no sense to me in this context
help?
Thanks in advance...
--- included text ---
Programming Notes
Zip Code Distance Calculator
Tables:
1. CPA Firms Table (CPAFIRMS)
Fields:
1) First Name
2) Middle Name
3) Last Name
4) Company
5) Address 1
6) Address 2
7) City
8) State
9) Zip Code – 10 digit
10) Delivery Point
11) County
12) Code Number
13) Zip Code – 5 digit
2. Zip Code – Latitude – Longitude Table (ZIP-LAT-LONG)
Fields:
1) Zip Code – 5 digit
2) Latitude
3) Longitude
4) City
5) State
6) County
Queries:
1. Find Center Zip Code
Finds matching Latitude/Longitude from ZIP-LAT-LONG table.
SQL CODE:
SELECT [ZIP-LAT-LONG].[ZIP CODE], [ZIP-LAT-LONG].LATITUDE,
[ZIP-LAT-LONG].LONGITUDE
FROM [ZIP-LAT-LONG]
WHERE ((([ZIP-LAT-LONG].[ZIP CODE])=[Forms]![Zip Code Distance Extractor]![Zip
Code]));
2. Find Distance
Calculates Distance from "Center Zip Code Query" against Zip Codes in the
ZIP-LAT-LONG table.
SQL CODE:
SELECT [ZIP-LAT-LONG].[ZIP CODE], [ZIP-LAT-LONG].LATITUDE,
[ZIP-LAT-LONG].LONGITUDE, ([ZIP-LAT-LONG]!
[LATITUDE]-[FIND CENTER ZIP CODE]![LATITUDE])*69.1 AS
[Distance Lat], (69.1*([ZIP-LAT-LONG]!
[LONGITUDE]-[FIND CENTER ZIP CODE]![LONGITUDE])*(Cos([FIND
CENTER ZIP CODE]![LATITUDE]/57.3))) AS [Distance Long],
((([Distance Lat]^2)+([Distance Long]^2))^0.5) AS Distance
FROM [ZIP-LAT-LONG], [FIND CENTER ZIP CODE];
3. Find Distance by Radius
Limits the results of the "Find Distance Query" by the radius number.
SQL CODE:
SELECT [Find Distance].Distance, [CPAFIRM'S].COMPANY,
[CPAFIRM'S].FIRST, [CPAFIRM'S].MIDDLE, [CPAFIRM'S].LAST,
[CPAFIRM'S].ADDRESS1, [CPAFIRM'S].ADDRESS2,
[CPAFIRM'S].CITY, [CPAFIRM'S].STATE, [CPAFIRM'S].ZIPCODE
FROM ([Find Distance] INNER JOIN [ZIP-LAT-LONG] ON [Find
Distance].[ZIP CODE] = [ZIP-LAT-LONG].[ZIP CODE]) INNER
JOIN [CPAFIRM'S] ON [ZIP-LAT-LONG].[ZIP CODE] =
[CPAFIRM'S].ZIP5
WHERE ((([Find Distance].Distance)<=[Forms]![Zip Code
Distance Extractor]![radius]))
ORDER BY [Find Distance].Distance;
--- end included text ---
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
