Compare a value from one record to the previous record. No date ordering!

[Woodster]

I have researched solutions for this scenario but they all seem dependant on sorting the data by date.
My situation is different.

It may be easiest to show the data I am working with. - Table below, sorted by transaction ID

The sequence # on the left I have just added here for reference.

I need to flag each record as being 'Family' or Individual'.
This is decided by whether there is just one record or more than one record for any given Family ID (e.g. 'F-TGJ7').
i.e. Whenever more than one record has the same Family ID, then they should be flagged as 'Family', else 'Individual'.

Sorting by date or transaction ID does not group common Family ID's, so I have to sort by Family ID
e.g. Row 9 needs to be Family as it matches rows 5 & 6.



Hope someone can help?

#	TransactionID	                        FamilyID	IndividualID	CrtDtTm
1	TR-131613433542179357C-X2SV	F-TGJ7	I-CN9Y	2011-09-16  00:52:15Z
2	TR-131613433542179357C-X2SV	F-TGJ7	I-L6UN	2011-09-16  00:52:15Z
3	TR-131613445998279400C-X2SV	F-TGJ7	I-CN9Y	2011-09-16  00:54:20Z
4	TR-131613445998279400C-X2SV	F-TGJ7	I-L6UN	2011-09-16  00:54:20Z
5	TR-131613491817979503C-HCHB	F-ZXO7	I-G6B0	2011-09-16  01:01:58Z
6	TR-131613504264679528C-HCHB	F-ZXO7	I-G6B0	2011-09-16  01:04:02Z
7	TR-131613606671179827C-X2SV	F-TGJ7	I-CN9Y	2011-09-16  01:21:06Z
8	TR-131613606671179827C-X2SV	F-TGJ7	I-L6UN	2011-09-16  01:21:06Z
9	TR-131613609068079825C-HCHB	F-ZXO7	I-G6B0	2011-09-16  01:21:30Z
10	TR-131613743160080211C-XK0I	F-A2V1	I-0UTG	2011-09-16  01:43:51Z
11	TR-131613743160080211C-XK0I	F-A2V1	I-E719	2011-09-16  01:43:52Z
12	TR-131613743160080211C-XK0I	F-A2V1	I-LMQZ	2011-09-16  01:43:51Z
13	TR-131613743160080211C-XK0I	F-A2V1	I-TB0W	2011-09-16  01:43:51Z
14	TR-131614036182881113C-ZI19	F-T89T	I-ZH9P	2011-09-16  02:32:41Z
15	TR-131614558696482407C-RR02	F-HO6A	I-A86N	2011-09-16  03:59:47Z
16	TR-131648472222518046C-38HE	F-1QRO	I-6GVD	2011-09-20  02:12:02Z
17	TR-131648472222518046C-38HE	F-1QRO	I-TCQF	2011-09-20  02:12:02Z

(and BTW I realise some Individuals have multiple records. These will be sorted out later.)

Thanks,

[June7]

You need to do a Totals query that will count the occurrance of each FamilyID (SELECT FamilyID, Count(FamilyID) As CountOfID FROM tablename GROUP BY FamilyID). Then can join this query to the table on the FamilyID. This will give you the 'flag'. Any records showing count greater than 1 are 'family'.

[Woodster]

I see where you're going thanks and I tried the query ...

But unfortunately what I get is a count of transactions (1 transaction = 1 record)
per Family ID.

No family ID has only 1 record.

In fact even a case where there is only one person for a Family ID can still have unlimited transactions/records.

So what I need is a count of Individual ID's per Family ID, right?

But how do I do that?

Thanks.

[June7]

Shouldn't matter which field is in the Count. (I tested.) The count is of records. But go ahead and try the change.

You used the query I suggested? Show your exact SQL statement.