Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Criteria from multiple records (Access 2000)

    I have a table containing order records that are related as follows.

    The X-Ref field on records where Type = 2 indicates another record where Type = 1; e.g., the X-Ref fields on the records below where Ref = 1002 and 1005 both refer to the record where Ref = 1001. (The Ref field is the primary key.)

    Ref, Type, Status, X-Ref
    1001, 1, N, -
    1002, 2, Y, 1001
    1003, 1, N, -
    1004, 1, Y, -
    1005, 2, Y, 1001

    I am trying to design a query that will list records (like the first one in the above example) where Type = 1 and Status = N and Ref = the X-Ref on any other record where Type = 2 and Status = Y. If easier, it could be the other way around; i.e., list records (like 1002 and 1005 in the above example) where Type = 2 and Status = Y and X-Ref = the Ref on any other record where Type = 1 and Status = N.

    Is it possible to use query criteria to cross-reference other records like this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria from multiple records (Access 2000)

    You can create a query in design view and add the table twice. Link the first instance of the table to the second one on Ref vs X-Ref. Return the Ref field from both instances.
    See the attached demo (zipped)

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Criteria from multiple records (Access 2000)

    Thanks once again, Hans.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Criteria from multiple records (Access 2000)

    The records in this table form nonconsecutive series where each type 1 record may have zero or more associated type 2 records that are related to the type 1 record by the X-ref field, as follows.

    Ref, Type, Status, X-Ref
    1001, 1, Y, -
    1002, 1, Y, -
    1003, 2, Y, 1001
    1004, 2, Y, 1001
    1005, 2, Y, 1002
    1006, 1, Y, -
    1007, 2, N, 1002

    In this example, there are three series: 1001-1003-1004, 1002-1005-1007, and 1006 by itself.

    I am trying to design a query that will list records in this table where the status is Y and there are no type 2 records in the same series with a status of N. In this example, the result should be as follows.
    1001, 1, Y, -
    1003, 2, Y, 1001
    1004, 2, Y, 1001
    1006, 1, Y, -

    The following records should be excluded because they form a series and one of them has a status of N.
    1002, 1, Y, -
    1005, 2, Y, 1002
    1007, 2, N, 1002

    I managed to achieve this filtering by creating a subquery that lists all records with a status of N, then creating a main query using the table left joined to four copies of the subquery with the four possible cross-references (Ref > Ref, Ref > X-Ref, X-Ref > Ref, X-Ref > X-Ref) and listing only records where the status in all four subqueries is null. Is there a simpler way to achieve this?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria from multiple records (Access 2000)

    Try the attached.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Criteria from multiple records (Access 2000)

    Yes, that's simpler, thanks Hans.

    Could this method easily be extended to exclude a series if *any* record in the series (i.e., type 1 or 2) has a status of N?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Criteria from multiple records (Access 2000)

    I managed to come up with this variation that seems to work OK.

Posting Permissions

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