Query help - retrieving data from previous row

[stevieb]

I have a table as follows:
Col1 = Name
Col2 = Date 1
Col3 = Date 2

(sorted by Name, then by Date 1)

Col3 is currently empty.

What I would like to do is update Col3 with a Date that is equal to (Date 1
- 1) of the next row. That is only if the next row contains the same Name.

ie. Before
Joe - Nov 1 - null
Joe - Dec 1 - null

After
Joe - Nov 1 - Nov 30
Joe - Dec 1 - null

Is it possible? If it is, would I have to change my logic and reverse order
the dates?

 

[John W. Vinson]

I have a table as follows:
Col1 = Name
Col2 = Date 1
Col3 = Date 2

(sorted by Name, then by Date 1)

You're mistaken. Tables *have no order*. They can be sorted for display
purposes, but you have essentially no control over the order of records as
stored on disk, and that order is or should be irrelevant to your use of the
database.

Col3 is currently empty.

What I would like to do is update Col3 with a Date that is equal to (Date 1
- 1) of the next row. That is only if the next row contains the same Name.

ie. Before
Joe - Nov 1 - null
Joe - Dec 1 - null

After
Joe - Nov 1 - Nov 30
Joe - Dec 1 - null

Is it possible? If it is, would I have to change my logic and reverse order
the dates?

You can do this with a Subquery or a domain function to identify the "next".
Something like

UPDATE mytable
SET Col3 = DMin("[Col2]", "[tablename]", "[Col1] = """ & [Col1] & """ AND Col2

#" & [Col2] & "#")

WHERE Col3 IS NULL;

 

[stevieb]

The only problem might be if it gets to the end of my subgroup of Nmaes where
there is no next minimum date. I guess I would have to then run a Dmax query
to re-set the "last" record in Col 3 to null.

Thanks - I am a pretty novice access user.

I have a table as follows:
Col1 = Name
Col2 = Date 1
Col3 = Date 2

(sorted by Name, then by Date 1)

You're mistaken. Tables *have no order*. They can be sorted for display
purposes, but you have essentially no control over the order of records as
stored on disk, and that order is or should be irrelevant to your use of the
database.

Col3 is currently empty.

What I would like to do is update Col3 with a Date that is equal to (Date 1
- 1) of the next row. That is only if the next row contains the same Name.

ie. Before
Joe - Nov 1 - null
Joe - Dec 1 - null

After
Joe - Nov 1 - Nov 30
Joe - Dec 1 - null

Is it possible? If it is, would I have to change my logic and reverse order
the dates?

You can do this with a Subquery or a domain function to identify the "next".
Something like

UPDATE mytable
SET Col3 = DMin("[Col2]", "[tablename]", "[Col1] = """ & [Col1] & """ AND Col2

#" & [Col2] & "#")

WHERE Col3 IS NULL;

 

[John W. Vinson]

The only problem might be if it gets to the end of my subgroup of Nmaes where
there is no next minimum date. I guess I would have to then run a Dmax query
to re-set the "last" record in Col 3 to null.

The DMax() function will return null if there is no "next". That should
automatically update Col3 from Null to Null (i.e. it'll do nothing but it will
leave the desired result).