>I have a table with the following fields:
>
>GROUP (C) 20
>EMPLOYEE (C) 20
>BEGINTIME (C) 8
>ENDTIME (C) 8
>TOTALTIME (I) 4
>STATUS (C) 3
>
>I'm attempting to create a view for a report that would look something like the following:
>
>------------------------------------------------------------------
> EMPL TOTAL INSIDE OUTSIDE OTHER
>GROUP EMPL CNT MIN CNT MIN CNT MIN CNT MIN
>------------------------------------------------------------------
> A EMP1 4 100 2 50 1 25 1 25
> EMP2 2 100 0 00 2 100 0 00
> --------------------------------------------------
>GROUP A TOTAL: 6 200 2 50 3 125 1 25
>
>I've got the view so I get a view of the Group, empl, empl cnt and total minutes. But for the Inside,Outside,Other comes from the status field. I'm thinking I need to build an expression using the IIF() function, but I don't know where to start with that. Right now my view is group on Group and Empl and sort by the same.
>
>Any and all idea's welcome.
>Kirk Kelly
>kkelly@cqisol.com

A couple of things-

First and most obvious: CHANGE the name of the group field. GROUP is an SQL reserved word. You can often get by with using a VFP reserved word as a field name, but using an SQL reserved word as a field name is begging for trouble.

Secondly, you don't need to use a view for this. I use a couple of guidelines for deciding when to create a view as opposed to a plain jane query:
1. Does it need to be updatable? If so, obviously it should be a view.
2. Is it used from a lot of different places, or do advanced users need to be able to open it and view data? If so, it is a good candidate for a view.

Firstly, the fact that your query contains a GROUP BY clause means it can't (or at least shouldn't) be updatable. And in this particular instance, the results that you need are too complex to practically handle with a single SELECT statement.

You should handle this one in steps. Build two or more cursors with the mins, maxs, avgs totals that you need, and combine them using your VFP data processing powers. Then run your report on the ned result.