Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: I am Stumped on a needed Query.. Help


    I have a weird query that I need to create in my Access DB. I am taking over a DB from another persons design and have to create a query that has me stumped. I think I would have initially designed the table differently to handle the information, but, at this point it isn't an option. Let me give a sample of the table and the desired outcome from the query(ies):

    Table A
    Field - Classification TYPE of Text
    Field - RemoveFromSF Yes/No
    Field - RenamedFromAbove Yes/No

    Sample records:

    Classification RemoveFromSF RenamedFromAbove
    Class1 No No
    Class2 No No
    Class3 No No
    Class4 Yes No
    Class5 No Yes
    Class6 No No
    Class7 No No
    Class8 Yes No
    Class9 No No
    Class10 No No

    I want all Classifications where RemoveFromSF is equal to 'No', and the classifications where RemoveFromSF is equal to Yes if the next record's Renamed From Above equals "Yes'.

    So my Output would be:
    Class4 (Because Class 5 has RenamedFromAbove as Yes)

    So Class 8 is excluded because it's RemovedFromSF is Yes and Class9's RenamedFromAbove is No.

    I hope this makes sense and thanks in advance for any assistance.


  2. #2
    Join Date
    Oct 2002
    Baghdad, Iraq
    The issue is this: there is, strictly, no such thing as a "next" record. A relation is a set of records and sets aren't ordered.

    SQL bends the rules to make ORDER BY and GROUP BY and such work, adding ordering information to tables and queries and intermediate results, but it doesn't make that ordering available to query criteria.

    You might be able to do this:

    SELECT OuterA.Classification, 
        (SELECT First(InnerA.Classification) 
        FROM A InnerA 
        WHERE InnerA.Classification > OuterA.Classification
        GROUP BY InnerA.Classification) 
      AS NextClassification
    FROM A OuterA
    What that does is decides what is "next" based on text ordering using the greater than operator. I'm not sure if Access supports this.

    Also, DFirst might work here, though it's going to be pretty slow.

    Anyhow, once you've got NextClassification defined, the rest of the logic should be straightforward.

  3. #3
    Join Date
    Dec 2005

    Thanks for the reply. The query you provided would work fine. I was trying to only give a subset of my information and it was a little misleading. However, your answer put me on the path to what I needed to do. Not sure if I did it the most effecient way, but I will only rarely run the queries.

    I created 4 queries. One that retrieved all of the data excluding the rows where RemoveFromSF was "Yes". Second query that retrieved the rows where the RenamedFromAbove was "Yes". A third query that utilized the previous query with multiple criteria utilizing a PromptOrder field (that wasn't a part of my original post) = to the second query's PromptOrder - 1 (this gave me my record Class8 which was previously excluded). Then my Final query was a UNION ALL query to union Query One and Query Three.

    This worked wonderfully.

    Many thanks as I wasn't getting it until I read your post, and applied it to my situation.


Posting Permissions

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