I have had great success searching in the past for solutions but I am stumped by this one.
I have a listing of people as well as their start and end dates in separate fields. The nature of the data could have one line with John Doe and start date of 1/1/12 and End date of 1/8/12 then the same John Doe has a new line with start date of 1/9/12 and end date of 1/15/12.
I can get a GROUP BY to work where I show John Doe of 1/1/12 and end date of 1/15/12 with summary lines of $$ from other fields in the data.
My issue is say John doe has a start date of 1/1/12 and end date of 1/8/12 then another line with start date of 1/20/12 and end date of 2/1/12. I would want those 2 lines to be separate in the query result unlike the first example where one line would adequately cover the time period.
I used query builder for the first example with First on start date and Last on end date with a sum for the $$ figure in another field but it ignores the gaps in the second example.
You already found out that by using GROUP BY on the name, FIRST and LAST on the dates, and SUM on the $, that you will wind up with one composite record. You want multiple records; the solution that's obvious to me is to use GROUP BY on all the fields. Alternatively, turn off the TOTALS query symbol, and run a straight SELECT query. Then you'll simply get each record separately.