Ok, I have been trying all day now and have searched long enough. This should be simple but I just don't get it.

I have six Foxpro 2.6 tables that I need to retrieve data from to create a cursor for a report. The tables are Visual, Liquid, Dga, Pcb, Furan and Nameplat. All the tables have commom fields named "custid" and "upsino". The first five tables all have a date field named either "puldat" or "datinp". You could say the Nameplat is the parent table if a relationship were setup.

The criteria for retrieving data will be a custid, a date range between which "puldat" or "datinp" will have to fit, and either all upsinos for a the selected custid or a selective range of upsinos.

I need to combine various fields from each of the tables for each record and include any records that fit in the date range and/or upsino criteria, even if they don't have a related record in the other tables. (i.e., only a Visual record exists for a certain UPSINO but no data for that one exists in any of the other child tables - Liquid, Dga, Pcb, or Furan.)

I have started out by just trying to combine just two of the tables, Visual and Liquid first. I get the proper records selected from the Liquid, however records from Visual that do not have a matching record in Liquid are excluded. I have tried using INNER, LEFT, and RIGHT joins in my SELECT statements joining on custid, upsino and DTOS(puldat). In my results I either get the exlclusions mentioned above or I get way to many records in the result set because each record is matched with every record in the other table.

How do I properly SELECT and combine data from more than two tables while including all records from each of the tables that meet the criteria? Below are some of my attempts:

First I combine nameplat and visual data:

SELECT nameplat.custid, nameplat.upsino, datinp, sub_name, ;
co_equipno, serial_no, liquid, equip_type, leaks, recser ;
FROM nameplat ;
INNER JOIN visual ON (nameplat.custid + nameplat.upsino = ;
visual.custid + visual.upsino) ;
WHERE nameplat.custid = gcCustid AND datinp >= ldStartDate AND ;
datinp <= ldEndDate ;
INTO CURSOR curNamevis ORDER BY nameplat.upsino, datinp

Then I combine that with liquid data:

SELECT curNamevis.upsino, liquid.upsino, datinp, liquid.puldat, sub_name, co_equipno, serial_no, liquid, ;
equip_type, leaks, recser, class, rec_servic ;
FROM curNamevis ;
LEFT JOIN liquid ON ALLTRIM(curNamevis.custid + curNamevis.upsino + DTOS(curNamevis.datinp)) = ;
ALLTRIM(liquid.custid + liquid.upsino + DTOS(liquid.puldat)) ;
WHERE liquid.puldat >= ldStartDate AND liquid.puldat <= ldEndDate ;
AND ALLTRIM(curNamevis.upsino + DTOS(curNamevis.datinp)) = ALLTRIM(liquid.upsino + DTOS(liquid.puldat)) ;
INTO CURSOR curLiq ORDER BY liquid.upsino, liquid.puldat

Thank you so much for your help.