How to Group by but only consecutive records 
Author Message
 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



Tue, 01 May 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Insert a consecutive in group

2. Putting consecutive records in a TEXTBOX

3. Suppressing Groups but the records are still grouped

4. Grouping records within the group

5. Grouping Tool Windows: tab group or tiled group

6. Stopping data from being grouped by Group 1 when that group is suppressed

7. Rolling consecutive month data

8. Consecutive page numbers on multiple reports

9. Search & Replace Consecutive Para Returns

10. Labels - Auto consecutive numbering

11. consecutive pagination for multi-doc user guide

12. Consolidaing Consecutive Blank Lines

 

 
Powered by phpBB® Forum Software