Hi Randy,

Your select looks fine to me but could use a couple of small changes. I prefer to use EMPTY() function and the replacement date in IIF is irrelevant as long as it is far in the future. It can be constant than.

SELECT AreaFK, StationFK, CrewFK, ;
	MIN(IIF(EMPTY(nextminor),{^9999/12/31}) AS NextMinor, ;
	MIN(IIF(EMPTY(nextmajor),{^9999/12/31}) AS NextMajor, ;
	.F. AS doMinGroup, .F. AS doMajGroup ;
	FROM premaint  GROUP BY 1,2,3  ORDER BY 1,2,3 ;
	INTO TABLE maintdue

>
>Did I do this the best way, or is there another/better way?
>
>The application schedules preventive maintenance on equipment. Each item subject to PM is assigned an Area (essentially a supervisor), a Station (where it is located), and a Crew (which crew under a supervisor will do the PM). There are two kinds of maintenance, minor (like ckecking the oil) and major (like changing the oil). My challenge was to issue a block of work orders when anything in an Area-Station-Crew grouping was due, rather than letting them trickle out one or two at a time based solely on the due date.
>
>One of the tricks was that for some items, either the nextminor or nextmajor date fields were blank based on the nature of the equipment.
>I've come up with the following code:
>

SELECT AreaFK, StationFK, CrewFK, ;
>	MIN(IIF(ISBLANK(nextminor),DATE()+4000,nextminor)) AS NextMinor, ;
>	MIN(IIF(ISBLANK(nextmajor),DATE()+4000,nextmajor)) AS NextMajor, ;
>	.F. AS doMinGroup, .F. AS doMajGroup ;
>	FROM premaint  GROUP BY 1,2,3  ORDER BY 1,2,3 ;
>	INTO TABLE maintdue

>Now the app can go back to the premaint table and flag all records that are due for maintenance in the next week, and print the workorders.
>
>I'm satisfied with the results I'm getting so far, and would like to thank everyone who takes time to review my code.