I am connecting to a database via ODBC from CR Dev 8.5
I have two tables FCAND and FACTIVITIES joined by FCAND.ACODE equaling FACTIVITIES.ACTACODE. This is one to many where there may be many FACTIVITIES or none at all for each FCAND record. They each have a date of FACTIVITIES.ACTDATE. What I want is all FCAND records that either do not have a record in FACTIVITIES at all or do not have a record in FACTIVITIES that has an ACTDATE that falls within the last year.
I tried a left outer join which gives me all FCAND records whether they have an FACTIVITIES record or not but as soon as I put in my selection criteria for only the ones that do not have an FACTIVITIES record within the last year, it slows down incredibaly and I lose all FCAND records that do not have an FACTIVITIES record at all.
I have two tables FCAND and FACTIVITIES joined by FCAND.ACODE equaling FACTIVITIES.ACTACODE. This is one to many where there may be many FACTIVITIES or none at all for each FCAND record. They each have a date of FACTIVITIES.ACTDATE. What I want is all FCAND records that either do not have a record in FACTIVITIES at all or do not have a record in FACTIVITIES that has an ACTDATE that falls within the last year.
I tried a left outer join which gives me all FCAND records whether they have an FACTIVITIES record or not but as soon as I put in my selection criteria for only the ones that do not have an FACTIVITIES record within the last year, it slows down incredibaly and I lose all FCAND records that do not have an FACTIVITIES record at all.