Need to add a field based upon the next record

  • Thread starter Thread starter RMires
  • Start date Start date
R

RMires

I have a field in a query (or table) and I need to add a field to that query
which gets filled by the first field in the next record. For example, if I
have a field:

Field 1
g
h
s
h
w

I need to create a new field:

Field 1 Field 2
g h
h s
s h
h w
w

The last record in Field 2 is unimportant and can remain Null. I know I
probably have to create a subquery but am not very familiar with the subject.
 
RMires said:
I have a field in a query (or table) and I need to add a field to
that query which gets filled by the first field in the next record.
For example, if I have a field:

Field 1
g
h
s
h
w

I need to create a new field:

Field 1 Field 2
g h
h s
s h
h w
w

The last record in Field 2 is unimportant and can remain Null. I know
I probably have to create a subquery but am not very familiar with
the subject.
Click to expand...

You need to tell us how to identify the "next" record. Sort order will
effect what the next record is. So tell us what field to use to sort the
data so it appears in the order you posted (is it a coincidence that the
sample data you posted is in alphabetical order?).
 
Order is determined by a third "time" field

Time Field1 Field2
2:00 g c
3:00 c k
4:00 k r
5:00 r w
6:00 w
 
Assuming
1. that the datatype of that field is Date/Time
2. there can never be two records with the same time entered
This is possible.
I will continue to use the name "Time" as you've shown it, but hopefully
you have not actually used a reserved keyword as the name of your field.

You will need to use what is called a "correlated subquery" to
accomplish this.

SELECT [Time], Field1,
(select Top 1 Field1 from tablename as i where i.[Time]>o.[Time] order
by i.[Time]) as Field2
FROM tablename as o
order by o.[Time]
 
Here is a different approach:

SELECT [Time],field1,first(tmpfield2) as field2 from (
SELECT t.[Time], t.field1,n.field1 as tmpfield2
FROM tablename AS t LEFT JOIN tablename AS n ON t.[Time] < n.[Time]
order by t.[Time],n.[Time]) as q
group by [Time],field1
 
Back
Top