>What I am trying to do is create a list of ids for a pick list (subset of all ids). User picks the user and that is used to retrieve data from a releated table. Then I want to display decodes of fields in original table of all ids.
>
>What I tried to do was save the physical record number of table of ids which has the values to be decoded by doing a sql select from two ables, where table b is used to restrict the records goton from table a i.e.
>
> select fields_in_a , recno() from a,b
> where a.field = b.field and b.amt > 0
> into dbf c
>
>my problem is that recno() returns record number of record in table b not record nunber of table a, recno('a') returns same value for all records, last record number in b.
>
>There must be a simple way to do this. Any help appreciated.

The record number can only be reliably returned on a single table SELECT statement, and you're obviously going to encounter some problems if anyone does anything to change the physical order of records in the first table.

IAC, if you insist on hurting yourself, you can rewrite the SELECT to use a subquery and have a single table select by:

SELECT *, RECNO() as MaybeRecNo ;
  FROM A ;
  INTO TABLE C ;
 WHERE <i>SomeField</i> IN (SELECT <i>SomeField</i> FROM B WHERE AMT > 0)