Prev: Update Query
Next: Query of a subform
From: R.Garcia on 4 Mar 2010 10:33
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.
From: KARL DEWEY on 4 Mar 2010 11:31
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

--
Build a little, test a little.


"R.Garcia" wrote:

> 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.
 | 
Pages: 1
Prev: Update Query
Next: Query of a subform