Hi!

Oops, sorry, I thought ccode is town code, but now I see it is county code, right? So, probably you have the same ccode for different towns.



>Vlad,
>
>Thanks for your input, but now, this is not the case, and actually I tried == with the same result. In both tables ccode is 2 Character field like '01' '02', etc. For the whole state I use 'ZZ' as ccode.
>IOW, Input table contains:
>

>ccode  town UseGrp cYear Period NumSales MedSale  VolSale
>01     BROO 1FA    1999   YR       15    200000   29102901300
>

>Towns table contains all towns in MA, RI, CT, NH, e.g.
>

>ccode  town  fullaname  county
>01     BROO  Brookline  Norfolk
>

>
>(I don't provide actual data, so this is just an example)
>
>I select records from the Input table and I just want to add county. IOW, If I select 255 records, I want to have the same 255 records in curWork, just with few additional fields. Group By solved the problem.
>
>>Hi, Nadya!
>>
>>Just my $0.02
>>
>>It looks for me that problem solved incorrectly, because (I want not to offend anybody) I see obvious omission in query.
>>
>>As you know (probably), when you compare 2 strings in VFP, in certain cases comparison is not exact character to character comparison. In such case 'ABC' = 'ABCD' returns .T., '' = 'ABS' returns .T. too. In your second query join condition is pure non-exact comparison of strings. This causes me to think that you will got incorrect result when you will try to group results set instead of change comparison to exact (by using '==' operator). In other words, if you have codes like 'X' in maine table and codes like 'XY', 'XYZ' ... in towns table, for one record in main table you will have many records from towns table. In addition, if you have empty code field in main table, you will have all records from towns table for that record! Maybe that is a cause of your problem? ;-)
>>
>>HTH.
>>
>>>Hi everybody,
>>>
>>>It's always simpler to answer somebody's else questions... :)
>>>
>>>Ok, there is a problem:
>>>I have a table with this structure
>>>

>>>ccode C2 town C4 UseGrp C3 cYear c4 Period C2 other numeric values
>>>

>>>
>>>I do:
>>>

>>>select ccode, town, UseGrp, cYear, ;
>>>		VolSales, MedSale, NumSales ;
>>>		from InputTable ;
>>>		where town='ZZZZ' and ;
>>>    	Period='YR' and between(cYear,lcStartYear,lcEndYear) ;
>>>		into cursor curTemp  && This cursor would contain all necessary records in the ascending order (Since the stats table was prepopulated)
>>>?_Tally	

>>>_Tally returns 255 records
>>>
>>>Now I want to join to Towns table to add county C 15 for this table.
>>>I do :
>>>

>>> select distinct curTemp.ccode, nvl(State,lcState) as State, ;
>>>		nvl(upper(County),lcCounty) as County, ;
>>>		nvl(fullname,lcCity) as City, ;
>>>		curTemp.town, UseGrp, cYear, ;
>>>		VolSales, MedSale, NumSales ;
>>>	 	from curTemp left join Towns ;
>>>	 	on curTemp.ccode=Towns.ccode ;
>>>	 	order by 1,5,6,7 ;
>>>	 	into cursor curWork	

>>>
>>>Now my result returns 5755 records.
>>>
>>>I just want the same 255 records with one additional field included.
>>>Note, BTW, that the InputTable contains ZZ as ccode for state level. Towns table doesn't contain ZZ, so what's why I tried left join.
>>>
>>>Could you please help me with this SIMPLE problem? I spent already about 1h on it :(