Set value of one field depending on result of next record in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My thanks to Steve Clark for solution to my recent query on grouping by time
interval on a memo field. After some work to adapt to my Tables/Query
structures the function to which he pointed me worked perfectly as required.
I'm very appreciative of the help the experts offer in these discussion
groups.

The project to which that problem belongs has now given rise to an entirely
differerent question and I'm hoping for the same success in asking for a
solution.

Data table contains fields as follows

Date "date/time"
Number 1 "number"
Number 2 "number"
TimeInSeconds "number"

I need a query which will output the following fields

date
number 1
number 2
TimeInSeconds
RepeatFlag "Number" or "Boolean"

The Repeatflag field in the query will have its value set to 1 or True
TimeInSeconds = 0 and the next record has duplicate values in both fields
Number 1 and Number 2.

For info I am trying to establish if the phone number making a call in
record 1 is making the same call in record two (i.e. re-dialling the number)
because the first attempt did not connect (as measured by the 0 value in
TimeInSeconds")

As always, any help would be very much appreciated.

Regards

Michael Bond
 
Hi, Michael

If you have an AutoNumber field that has sequential numbers for these
records, then you may use the technique described on the following Web page:

http://support.microsoft.com/default.aspx?id=210504

However, AutoNumbers aren't guaranteed to be sequential, so if there are any
gaps, then this technique won't work for the record preceding each gap.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Gunny

thanks for this. The autonumber issue should not be a problem. Although my
source data is in an SQL database and I'm linking to it, the data table does
not have autonumbers .... but elsewhere in the project I'm using a "create
table" query to deal with the issue that S Clark offered a solution with. I
can use the same technique to "create" a table with the records which need to
be queried and set an autonumber to that table.

Thanks again .... I've got a few days work ahead of me now to implement this
solution .... but I approach it with optimism because, yet again, of the
invaluable assistance offered by users of this forum

Regards

Michael Bond
 
Back
Top