Select to reference an other record or row


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.
 
Ad

Advertisements

K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top