breaking apart similar record groups

[smuthcrmnl777]

I need a way to identify groups of data that are stored in a table. If you look at the data below, the top 2 records are grouped and the bottom two are grouped. I need to give each of these records an incremental numeric value. The way to identify the groups is by the date and Line-Sample column. The only problem is when I group like this I will get a subgroup size of 4. I have to only have 2. I am not sure how to break apart efficiently to achieve my goal. any clues to how I can do this?

DATE_CREATED	TIME_CREATED	SID Line	Line-Sample	Sample	XR	CU	CA
02/07/06	13:29:32	40-69-REJ	40	40-69-REJ	69-REJ	6.0938	0.2196	6.0996
02/07/06	13:32:26	40-69-REJ-F	40	40-69-REJ	69-REJ	5.8862	0.2436	6.4099
								
02/07/06	13:33:28	40-69-REJ	40	40-69-REJ	69-REJ	6.9669	0.2231	6.4753
02/07/06	13:34:52	40-69-REJ-F	40	40-69-REJ	69-REJ	7.447	0.2316	6.5104

FYI: Fields Line and Line-Sample are commands that break apart SID1.

 

[ZmrAbdulla]

Do it in a report.. Query won't allow you to do what you are after.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[smuthcrmnl777]

I can't do it in a report. I need this data later. a report defeats the overal purpose.

 

[ZmrAbdulla]

I can see all as only one group not even two.

DATE_CREATED Sample
02/07/06 69-REJ
02/07/06 69-REJ
02/07/06 69-REJ
02/07/06 69-REJ

Values in all rows have the same value for the DaT & Sample columns.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[lespaul]

so for each 40-69-REJ you want to find the next time entry and if it's a 40-69-REJ-F match those two. Is there ALWAYS a -F record time stamped AFTER the 40-69-REJ record and time stamped BEFORE the next record with 40-69-REJ?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

You may try a ranking query with theta join:
SELECT A.DATE_CREATED, A.TIME_CREATED, A.SID, A.[Line-Sample], A.Sample, A.XR, A.CU, A.CA, Count(*) AS Rank
FROM tblSample AS A INNER JOIN tblSample AS B ON A.SID = B.SID AND (A.DATE_CREATED+A.TIME_CREATED)>=(B.DATE_CREATED+B.TIME_CREATED)
GROUP BY A.DATE_CREATED, A.TIME_CREATED, A.SID, A.[Line-Sample], A.Sample, A.XR, A.CU, A.CA

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886