Pulling the Record Number into a Query - Attempt 2

[John Ortt]

I have a table of dates in ascending order but with varying intervals. I
would like to create a query to pull out the date (in field 1) and then pull the date from the subsequent record (and store it in field 2).

I would like to run a query that returns all the data in the table plus the record number, in a similar sense to the getuser() command to get the User's Identity, I would like a GetRecord() command.

Is this possible, or can someone think of a better way of doing this?
Thanks, John

Also Posted to:
------------------
microsoft.public.access
comp.databases.ms-access

[SA]

John:

Possibly the reason that you received no replies on Attempt 1, is that you
post is pretty unclear. There appear to be two parts to your question; if
I've missed something then you're back to attempt three.

1.) You want to create a query that contains a field that pulls a date and
then in a created field within the query, pulls the next ascending date into
each row in the query. Not hard, you simply have to use a subquery within
your query. I'll assume that the date field is the primary key for table
in question (so that the dates are all unique.)

Here's sample SQL:

SELECT YourTable.YourDateField, (Select First(YourDateField) _
as NextDate from YourTable as [Temp] WHERE _
[Temp].[YourDateField] > [YourTable].[YourDateField]) _
AS NextDate FROM YourTable _
ORDER BY YourTable.YourDateField

What this query does, is use a subquery (which is required to include a
primary key comparison with the target table,) in its second field (the same
table is used but is aliased so that Access compares your table to a
separate unique rowset) to return the first date greater than the date
displayed in the first field (i.e. YourDateField); the second field is
aliased as "NextDate"

2.) Related to the second part of your post, i.e. "I would like to run a
query that returns all the data in the table plus the record number ......
This makes no sense. Access doesn't store absolute record numbers (unless
you have an auto number field, in which case simply add that field), because
they have no meaning over time, (i.e as records may be deleted from earlier
records.) If what you are talking about adding some type of row number to
the returned query simply to display a row number, then again, all you need
is a subquery to do that. (See our web under Code and Design Tips; Queries
area, to see how to create an numbered return for each row in your query.)
How that releates to GetUser() is totally unclear.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg
"John Ortt" <Jo******@STOPSPAMMINGMEbaesystems.com> wrote in message
news:3f********@baen1673807.greenlnk.net...

I have a table of dates in ascending order but with varying intervals. I would like to create a query to pull out the date (in field 1) and then

pull

the date from the subsequent record (and store it in field 2).

I would like to run a query that returns all the data in the table plus

the

record number, in a similar sense to the getuser() command to get the

User's

Identity, I would like a GetRecord() command.

Is this possible, or can someone think of a better way of doing this?
Thanks, John

Also Posted to:
------------------
microsoft.public.access
comp.databases.ms-access

[SA]

John:

Possibly the reason that you received no replies on Attempt 1, is that you
post is pretty unclear. There appear to be two parts to your question; if
I've missed something then you're back to attempt three.

1.) You want to create a query that contains a field that pulls a date and
then in a created field within the query, pulls the next ascending date into
each row in the query. Not hard, you simply have to use a subquery within
your query. I'll assume that the date field is the primary key for table
in question (so that the dates are all unique.)

Here's sample SQL:

SELECT YourTable.YourDateField, (Select First(YourDateField) _
as NextDate from YourTable as [Temp] WHERE _
[Temp].[YourDateField] > [YourTable].[YourDateField]) _
AS NextDate FROM YourTable _
ORDER BY YourTable.YourDateField

What this query does, is use a subquery (which is required to include a
primary key comparison with the target table,) in its second field (the same
table is used but is aliased so that Access compares your table to a
separate unique rowset) to return the first date greater than the date
displayed in the first field (i.e. YourDateField); the second field is
aliased as "NextDate"

2.) Related to the second part of your post, i.e. "I would like to run a
query that returns all the data in the table plus the record number ......
This makes no sense. Access doesn't store absolute record numbers (unless
you have an auto number field, in which case simply add that field), because
they have no meaning over time, (i.e as records may be deleted from earlier
records.) If what you are talking about adding some type of row number to
the returned query simply to display a row number, then again, all you need
is a subquery to do that. (See our web under Code and Design Tips; Queries
area, to see how to create an numbered return for each row in your query.)
How that releates to GetUser() is totally unclear.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

"John Ortt" <Jo******@STOPSPAMMINGMEbaesystems.com> wrote in message
news:3f********@baen1673807.greenlnk.net...

I have a table of dates in ascending order but with varying intervals. I would like to create a query to pull out the date (in field 1) and then

pull

the date from the subsequent record (and store it in field 2).

I would like to run a query that returns all the data in the table plus

the

record number, in a similar sense to the getuser() command to get the

User's

Identity, I would like a GetRecord() command.

Is this possible, or can someone think of a better way of doing this?
Thanks, John

Also Posted to:
------------------
microsoft.public.access
comp.databases.ms-access

[John Ortt]

Thanks for the post SA. You have answered my question.

Essentially I just wanted the Date and the subsequent Date on the same line
with as few queries as possible.

My reference to the GetUser() Command was just an example of how I envisaged
a GetRecord() command working.

I was originally going to use the record number minus 1 in a separate table
to link each record with the record which followed it. The way you have
used is far more efficient.

I apologise for the two posts, I attempted to cancel the first post after
somebody in work pointed out that we are not supposed to crosspost to
internal & external newsgroups.

My Cancel request seems to have failed.......

Thanks Again,

John
"SA" <~f***********@nspm.com> wrote in message
news:bp*********@ngspool-d02.news.aol.com...

John:

Possibly the reason that you received no replies on Attempt 1, is that you
post is pretty unclear. There appear to be two parts to your question; if
I've missed something then you're back to attempt three.

1.) You want to create a query that contains a field that pulls a date and
then in a created field within the query, pulls the next ascending date into each row in the query. Not hard, you simply have to use a subquery within your query. I'll assume that the date field is the primary key for table
in question (so that the dates are all unique.)

Here's sample SQL:

SELECT YourTable.YourDateField, (Select First(YourDateField) _
as NextDate from YourTable as [Temp] WHERE _
[Temp].[YourDateField] > [YourTable].[YourDateField]) _
AS NextDate FROM YourTable _
ORDER BY YourTable.YourDateField

What this query does, is use a subquery (which is required to include a
primary key comparison with the target table,) in its second field (the same table is used but is aliased so that Access compares your table to a
separate unique rowset) to return the first date greater than the date
displayed in the first field (i.e. YourDateField); the second field is
aliased as "NextDate"

2.) Related to the second part of your post, i.e. "I would like to run a
query that returns all the data in the table plus the record number ......
This makes no sense. Access doesn't store absolute record numbers (unless you have an auto number field, in which case simply add that field), because they have no meaning over time, (i.e as records may be deleted from earlier records.) If what you are talking about adding some type of row number to the returned query simply to display a row number, then again, all you need is a subquery to do that. (See our web under Code and Design Tips; Queries area, to see how to create an numbered return for each row in your query.)
How that releates to GetUser() is totally unclear.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg
"John Ortt" <Jo******@STOPSPAMMINGMEbaesystems.com> wrote in message
news:3f********@baen1673807.greenlnk.net...

I have a table of dates in ascending order but with varying intervals. I would like to create a query to pull out the date (in field 1) and

then pull

the date from the subsequent record (and store it in field 2).

I would like to run a query that returns all the data in the table

plus the

record number, in a similar sense to the getuser() command to get the

User's

Identity, I would like a GetRecord() command.

Is this possible, or can someone think of a better way of doing this?
Thanks, John

Also Posted to:
------------------
microsoft.public.access
comp.databases.ms-access