Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Cross-record' expressions? (XP SP1)

    Another novice question.

    In a calculated field in a Query, is it possible to reference other records?

    eg. the following situation:

    - Query ex. two joined Tables, has a field "ID"
    - Can have two records in the Query with the same ID (valid output from the query)
    - Query sorted by ID, so if there are duplicates, they will be consecutive records

    I some situations, I need to test whether the preceding or following record has the same ID.

    To test for "duplicates" I envisaged adding a new calculated field along the following lines:


    Duplicate: IIf( [ID] = [ID(proceeing record)], "Dup", IIf( [ID] = [ID(following record)], "Dup", "" ) )


    Possible?


    If so, what is the correct syntax / referencing to use?

    Thanks for any assistance
    David

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 'Cross-record' expressions? (XP SP1)

    This gets to be relatively complicated in the database world, as you typically are dealing with one record at a time, and there isn't any method of referring to a previous record or a following record in a query. There is a kind of duplicate query you can create using the query wizard that essentially finds all records where there are two or more records meeting certain duplicate criteria.

    But if you want to do something more permanent, it involves doing data access from VBA with either DAO or ADO, and stepping through records one at a time in a predetermined sort order while keeping track of things with VBA variables. That general subject is beyond the scope of what can readily be dealt with in the Lounge - books on Access programming typically have several chapters on the topic. I would suggest a visit to your local library or book store to get some idea of the issues involved.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Cross-record' expressions? (XP SP1)

    Thanks for the reply. I feared as much.

    It is not practical for me to come to grips with VBA for this issue. I have now narrowed my records down (via the query) sufficiently that I can export the data back into Excel & handle the problem there. Also more familiar territory!

    Otherwise I would just have fallen back on my old standby of Fortran (which dates me considerably, but which still provides enormous number crunching power ... & for me no new learning required).

    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •