>Hi All,
>
>I have a limited students cursor, created by several selects that filter out students that do not fit the criteria. These are the students that I have to pick from (pickstdnts). Student has stuid as unique key
>I have a buspass table that holds all the buspasses printed and when (buspasses).
>
>In the buspasses table, there can be serveral records for a student, but "should" have only one not closed record for a student.
>
>--A student stamp is printed out each year, or, when specific info changes from what is in the buspasses table. If the record in buspasses is already marked as stamp printed (logical field), that record is closed (logical) and a new record is created.
>
>--A student pass is printed once, or when the buspass code changes from what is in the buspasses table.
>
>my problem is with the student pass. If this is the student's first time in the table, works great.
> But if this student is already in the table for some reason. The student can have more then one record in buspasses. I can not seem to get only those students that have not been pass printed at all and/or have the buspass code changed.
>
>I have this to start with, but I just do not feel it is correct.
>
>SELECT DISTINCT stuid FROM buspasses, climitedstudnts ;
> WHERE stuid == stuid_ ;
> AND code___ <> bpcode ;
> ORDER BY stuid ;
> INTO CURSOR cstuids nofilter
>
>SELECT DISTINCT stuid FROM cstuids ;
> WHERE stuid NOT IN (SELECT stuid FROM buspasses ;
> WHERE bp_print) ;
> INTO CURSOR ctemp nofilter
>
>
>I think that there could be a situation where the buspass code could change after the bus pass is printed and I miss it with this.
>
>So, I need to find all students that meet the criteria for printing a bus pass, never been printed, or been printed and bus pass code has changed since last time printed.
>
>Hope that makes sense. Any ideas?
>
>TIA,
>Beth
Beth,
To get students never printed
select B1.StudID from BusPasses B1. LEFT JOIN BusPasses B2
on B1.StudID = B2.StudID and B2.bp_Print =.T.
WHERE B1.Bp_Print = .F. and B2.StudID IS NULL
We may try similar logic for changed buspass code.
If it's not broken, fix it until it is.
My Blog