Bob,
Try this:
SELECT A.Ads, ;
A.Ad1, ;
PADR(ALLTRIM(C.Sn) + ", " + ALLTRIM(C.Fn), 25) AS SurName, ;
C.Mno ;
FROM TcData!Address A, ;
Client C ;
WHERE A.Can = 0 ;
AND A.Mno = C.Mno ;
AND A.Ads IN ;
(SELECT MAX(A2.Ads) ;
FROM TcData!Address A2 ;
WHERE A2.Mno = A.Mno) ;
ORDER BY 3 ;
INTO CURSOR Boe
What's going on here:
1) Don't need to do an INNER join here... VFP supports equijoins nicely, and they're much easier to read (IMO)
2) I'm using a correlated subquery to find the MAX(Address.Ads) for each Address.Mno. The main query is tied into the subquery with the WHERE clause in the subquery. TcData!Address is used in both the main query and the subquery. To keep VFP (or any SQL DBMS) from getting confused, the instances of TcData!Address get unique aliases ("A" in the main query, "A2" in the correlated subquery).
3) When creating a calculated field in Fox (i.e. SurName) its best to define the field width. VFP uses the width of the results of the first calculation to set the width of the derived field. If the first record's calc size is smaller than any subsequent records, all subsequent records will be truncated:
Client.Sn Client.Fn SurName
"AB" "Joe" "AB, Joe"
"ABCD" "Franklin" "ABCD, Fr"
"ABCDEFG" "Zachary" "ABCDEFG,"
I'm using the PADR() function to define SurName's field width to 25.
You should do the same thing if you're doing a math calculation, by adding an appropriate number of zeros:
SELECT ;
pnLength,
pnWidth, ;
00000 + (pnLength * pnWidth) AS Area ;
FROM MyTable
Regards,
Thom C.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only