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):
Field - Classification TYPE of Text
Field - RemoveFromSF Yes/No
Field - RenamedFromAbove Yes/No
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.
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:
FROM A InnerA
WHERE InnerA.Classification > OuterA.Classification
GROUP BY InnerA.Classification)
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.
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.