Hi Mark,
>Don't you need to see if there are multiple records with the same address first? Something like:
>select cVot_Stree, count(*) as Kount from AK ;
> group by cVot_Stree having Kount > 1 ;
> order by 1 ;
> into cursor crsDoran noFilter
>Then you couold scan through the cursor and issue the second
>query to get the names for each unique street address.
>
>lcStreet = crsDoran.cVot_Stree
>select First,LastN from AK where cVot_Stree == lcStreet
This table for instance has names and addresses of all people who live in AK.
This table has like 163650 records. There are duplicates addresses since several people can live under the same roof.
If I am using the following query:
SELECT ;
COUNT(*) AS MyCount ,;
AK.cVot_First ,;
AK.cVot_LastN ,;
AK.cVot_Stree ;
FROM ;
AK ;
WHERE AK.cVot_First=="JOE" OR AK.cVot_First=="VERONICA" ;
HAVING MyCount>1 ;
GROUP BY AK.cVot_Stree ;
INTO CURSOR Doron ;
ORDER BY AK.cVot_Stree NOFILTER
I am getting the following results:
--------------------------------------
2 "JOE" "GONZALEZ" "12820 OOMIAK CIR"
2 "JOE" "WILLIAMS JR" "3351 E 68TH AVE"
2 "VERONICA" "ISLER" "4922 CASTLE CT"
2 "JOE" "KOON" "GENERAL DELIVERY"
Using the following query :
SELECT DISTINCT ;
AK.cVot_First ,;
AK.cVot_LastN ,;
AK.cVot_Stree ;
FROM ;
AK ;
JOIN Doron ;
ON AK.cVot_Stree==Doron.cVot_Stree ;
WHERE AK.cVot_First=="JOE" OR AK.cVot_First=="VERONICA" ;
INTO TABLE Ica ;
ORDER BY AK.cVot_Stree
I am getting the following results:
----------------------------------------
"JOE" "GONZALES" "12820 OOMIAK CIR"
"JOE" "GONZALEZ" "12820 OOMIAK CIR"
"JOE" "WILLIAMS" "3351 E 68TH AVE"
"JOE" "WILLIAMS JR" "3351 E 68TH AVE"
"VERONICA" "DARRELL" "4922 CASTLE CT"
"VERONICA" "ISLER" "4922 CASTLE CT"
"JOE" "KOON" "GENERAL DELIVERY"
"VERONICA" "KIERECK" "GENERAL DELIVERY"
As you see only the last two records are the actual match. The first
2 pairs are duplicate, the third One are two VERONICAs . If I get two more VERONICAs under the last address which is GENERAL DELIVERY this is still
ok, or if it could be several more JOEs with several more VERONICAs under
the same address. Since it could be an apartment house.
To make long story short the first 6 records are not needed. I wonder
if there is a direct query to provide the right results.
Appreciate your input.
Doron