Compare value with nextrecord in Query

[voslica]

Is there a way to compare the value in one field with the value of the same field in the next record? I can do it in Excel by using the following formula:

=If(A1=A2,"Match","No Match")

But I need to be able to do this in an Access query?

Any ideas?

 

[gemma-the-husky]

no because there is only one current record for the dbs cursor. you could do this with code, but try creating a findduplicates query (run the wizard) - this will create a self join on the table, which will retrieve duplicated values

 

[voslica]

I probably didn't give enough information. My reason for comparing the values between rows is not to find duplicates so the "findduplicate" query will not work. Maybe I should explain further what I am needing:

I receive an import from a database where the data comes in as follows:

Name ID# # of stock grants date of stock grant
John Doe 123 100 12/31/03
200 12/31/04
300 12/31/05

John Doe's ID# does not appear for all his stock grants. So what I am trying to do is fill the ID# throughout all the records for John Doe. If I downloaded this into Access the way it is now, John Doe's 12/31/04 and 12/31/05 stock grants would not be tied to him.

 

[neileg]

You will have to do this in VBA by stepping through the records. Similar things have been covered in here before.

 

[The_Doc_Man]

Neil is quite right. You have to use two recordsets because each points to only one record at a time. Look up "BOOKMARKS" and ways to synchronize two recordsets to the same query/table. Be aware that you are also ASSUMING a particular order unless you use a query or you are content to scan the table in order of its primary key. Dont' forget that a table-based recordset's order is always in the order of the prime key if there is one, or in no predictable order if it has no prime key. A query-based recordset is in the order of its sort-by clauses if it has one, or in the order of the group-by clauses if there is one, or in no particular order otherwise.

 

[voslica]

I have never worked with bookmarks. Can this be done in a query? If so, can you tell me how to do this?

 

[The_Doc_Man]

You open a recordset. One of its properties is the location of the bookmark. If you then CLONE the recordset (try the Help files), you can copy the bookmark from the first recordset to the second recordset. Now do a .NextRecord method on one of them. At this point, the two different recordsets point to two different records. The fields of the recordsets can then be compared.

Look up Recordset operations to get the bloody, gory details. But the above overview is what you wanted to know.