
How to Group by but only consecutive records
I want a query to group records on 3 or 4 fields but in one field (PO) only if
they are consecutive records. For example, let's say I have:
PO----------------Date-------Category---------Group---------FOBExt
10000------------4/15--------Sleepwear-------Regular-------1000
10001------------4/15--------Sleepwear-------Regular-------1500
10003A----------4/15--------Sleepwear-------Regular-------1400
10004------------10/15-------Daywr--- --------Fash-----------1600
10005------------10/15-------Daywr---- -------Fash-----------2000
10006------------4/15--------Sleepwear-------Regular-------1800
I would want the following reduction grouping on the PO, Date, Category, Group
and Summing FOBExt:
FirstPO----------LastPO----------Date-------Category---------Group--------
-SumFOBExt
10000------------10003A----------4/15--------Sleepwear-------Regular-------3900
10004------------10005----------10/15--------Daywr-------------Fash-------
---3600
10006------------10006------------4/15--------Sleepwear-------Regular------1800
Notice it groups 10000 and 10001 and 10003A on one line eventhough they are not
consecutive numbers (but they are consecutive fields). (Also the field PO is 5
numbers with an optional Letter.)
Notice also that PO 10006 is not included in this group because, though it has
the same criteria on the Group By, it is not consecutive. I do group by
queries all the time but they would compact this down to two lines with a false
10000 to 10006 record. So the question is how do I separate the odd fish?
How to groupby with only consecutive records?
Thanks in advance,
Doyle60