SELECT ;
Programs.cprogramcode, ;
Programs.nclientno, ;
Programs.creferralcode, ;
Programs.ndischargetype, ;
Programs.cstaff1, ;
Programs.nprogramid, ;
Programs.ddischarge, ;
Programs.dadmission,;
Programs.lreferedout, ;
Programs.lmedicalcare, ;
Programs.cstaff2, ;
Programcode.cdescription,;
Programcode.ddateofentry, ;
Clients.cfirstname, ;
Clients.cmiddlename,;
Clients.clastname, ;
Referralcode.cdescription, ;
Staff.cfirstname, ;
Staff.clastname, ;
Staff_a.cfirstname, ;
Staff_a.clastname,;
Dischargetype.cdischargetype ;
FROM ;
parenting!programs, ;
parenting!programcode, ;
parenting!clients, ;
parenting!referralcode, ;
parenting!staff, ;
parenting!staff Staff_a, ;
parenting!dischargetype ;
WHERE ;
Programs.cprogramcode = Programcode.cprogramcode AND ;
Programs.nclientno = Clients.nclientno AND ;
Programs.creferralcode = Referralcode.creferralcode AND ;
Programs.cstaff1 = Staff.cstaffcode AND ;
Programs.ndischargetype = Dischargetype.ndischargetypeid AND ;
Programs.cstaff2 = Staff_a.cstaffcode ;
OR (Programs.cstaff2 = "")
>The difference between this one and the one I E-mail about originally is that there is no check to see if (Programs.cstaff2 LIKE "%").
>
>When I run the SQL, I get the same record repeated over and over again in the browse window. I was hoping to get back just one record regardless of weather
>or not there is a value in Programs.cstaff2.
I only rearranged the SQL so I could follow and make sure all tables were present and joined. I see that they all are. Now the problem is the first 6 lines of the WHERE clause [everything before the OR] are the table joins. The OR part defeats your JOINS. In effectect, you are saying give me all the records that meet the 6 lines joining my tables
AND give me all records where cStaff2 is empty.
Not really knowing what you want exactly, I would drop the OR portion and add a GROUP BY PROGRAMS.cProgramCode or some other relevant field that makes each returned record unique.
Mark McCasland
Midlothian, TX USA