Select to reference an other record or row

[R.Garcia]

Hi, I have one table on Microsoft Access 2007 like this

Table1
Field1 Field2 Field3 Field4
1 Data1 1 2
1 Data2 3 4
1 Data3 5 6
1 Data4 7 8
2 Data5 9 10
2 Data6 11 12
2 Data7 13 14
2 Data8 15 16

Then I need a query to get something like this

Query Result
Field1 Field2 Field3 Field4
1 Data1 1 2
1 Data2 1 2
1 Data3 3 4
1 Data4 5 6
2 Data5 9 10
2 Data6 9 10
2 Data7 11 12
2 Data8 13 14

note that changes is just only on field3 and field4 whit an offset (-1,0)
and for each change in field1 the first record is duplicate from above and
the last data for each change on the content of field1 I lost the content
from field3 and field4 and don't care.

in MS Excel is very easy with offset command, but I can't do that with
access select sentence.

Thanks on advance.

 

[KARL DEWEY]

To do this you must have some field that sets the order of the records
because as they are pulled from the table they may not be in any order, like
this --
1 Data4 7 8
2 Data5 9 10
2 Data8 15 16
2 Data6 11 12
1 Data2 3 4
1 Data1 1 2
1 Data3 5 6
2 Data7 13 14