Access 2003 - Referencing Other Records in a Query 1

[stephenj789]

I would like to create a field in an Access query that gets its value based on values in a different record. Here is an example:

Index Field1 CreatedField
1 A 1
2 A 2
3 B 1
4 B 2
5 B 3

In the 2nd record, if field1 of the current index of 2 (A) equals field1 of the current index of 2 minus 1 (A), then I want to add 1 to the prior created field for the value in the current created field. If it doesn't, I would like it to begin a new increment at 1.

I don't think it is possible to reference other records in a query, but I thought I might ask.

Thanks.

 

[mishbaker]

SELECT ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY [index] )AS CreatedField,*
FROM TestTable



All lessons learned from the School of Hard Knocks........at least tuition is cheap.

 

[dhookom]

I don't expect mishbaker's solution will work in MS Access.

It would help if you provided the name of your table/query. Try something like:

SELECT [Index], Field1, (SELECT Count(*) FROM [unknown] U WHERE U.Field1 = [unknown].Field1 and U.[Index] <=[unknown].[Index]) as CreatedField
FROM [unknown]

Duane
Hook'D on Access
MS Access MVP