Select to reference an other record or row

  • Thread starter Thread starter R.Garcia
  • Start date Start date
R

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.
 
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
 
Back
Top