Todd,

You can join a table more than once, i do this for an overloaded lookup table. For example:

create sql view windowsview as;
SELECT windows.iID, cWindowLetter,;
  nQuantity, nWidth, nHeight,;
  nCostPerSqFt, nPrice, lNameplate,;
  lProtectiveCovering, lLightbox, lFrames,;
  lFinalMeasurement, ;
  lCircular,;
  CodesStyle.cValue as cWindowStyle, ;
  CodesTop.cValue as cWindowTop ;
  FROM  windows ;
  INNER JOIN codes as CodesStyle;
     ON  windows.iStyle = CodesStyle.iID;
  INNER JOIN codes as CodesTop;
     ON  windows.iTopShape = CodesTop.iID ;
 WHERE windows.iProject = ?Projects.iID ;
 order by cWindowLetter

But each row in that table has a unique iID. Your data doesn't have that luxury. This may need some tweaking but it's a start for you:

select ... 
   tbl1.field_value as padmis,;
   tbl2.field_value as adpriv ;
   left outer join table_add_detail as tbl1 ;
      on .. = tbl1.unique_id and tbl1.field_name = "padmis" ;
   left outer join table_add_detail as tbl2 ;
      on .. = tbl1.unique_id and tbl1.field_name = "adpriv" ;
 '''

>I've never used the join operator before, but I can't seem to get my tables joined correctly, so I think its time:
>
>Three tables (appoint_detail, address, table_add_detail (joined twice?))
>
>Appoint_detail has key, facility_code, unique_id
>Address has lookup_name, userdef_1
>table_add_detail has unique_id, field_name, field_value
>(BTW--I didn't define this database!)
>

>Appoint_detail looks like this:
>key facilitycode unique_id
>32  XYZ GROUP    1
>32  ABC GROUP    2
>23  DEF GROUP    3
>23  ABC GROUP    4
>

>Unique ID is unique to the line (integer key), key is the file number the user uses.
>
>

>address looks like this:
>lookup_name    userdef_1
>XYZ GROUP      45
>ABC GROUP      99
>DEF GROUP      88
>

>Table_add_detail looks like:
>

>Unique fieldname  fieldvalue
>1      padmis     70
>1      adpriv     Y
>2      padmis     80
>3      adpriv     Y
>

>Ok, I want out of that:
>
>

>key    facility_code   unique_id    userdef_1   padmis   adpriv
>32     XYZ GROUP       1            45          70       Y
>32     ABC GROUP       2            99          80       (blank)
>

>
>I've been fussing with the sql syntax all afternoon. If someone has a idea, I'd be grateful to hear it! If you want to see my best efforts, ask!
>
>Thanks!