A Quiz on Query Condition - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

A Quiz on Query Condition

Kevin Yip

10 days ago

Hi Richard, here is a quiz of my own that demonstrates some key concepts in database.  Has any student asked you about this before, or has a TechHelp video been made?  This is a somewhat tricky problem that we may come across once in a while, or perhaps quite often.

In the list of names below, sorted by last name and then first name, how do you create a query that only displays names from "Mitchell, Sophia" onwards?  In other words, the query should find the proper "cut off point" and return only the last 10 names in this list below, starting from "Mitchell, Sophia".

ID     LastName     FirstName
972    Anderson     Alexander
956    Bennett      James
341    Bennett      Henry
36     Brooks       Noah
576    Carter       Olivia
108    Coleman      Mia
166    Cooper       Benjamin
571    Edwards      Samuel
576    Edwards      Liam
729    Hayes        Isabella
740    Mitchell     Sophia
806    Mitchell     Ava
179    Parker       Daniel
459    Peterson     Grace
627    Phillips     Chloe
159    Reynolds     Ethan
239    Richardson   Charlotte
379    Sullivan     Emma
507    Turner       Lucas
33     Wallace      Amelia

You can't use the condition:

   LastName >= "Mitchell" And FirstName >= "Sophia"

because that will miss "Mitchell, Ava".  The FirstName field is *not* necessarily sorted ascendingly from record to record, so you can't use the ">=" operator, nor any operator -- because there is no discernible order in the FirstName field, since it is a secondary sort field.

The ID field doesn't help us either.  It is an autonumber field that increments by one every time a new record is entered.  The names were not entered alphabetically -- think of them as employees of a company, whose names were entered in the order of when they were hired.

Anyone interested in solving this, please post your answer below.  There may be more than one answer.  Ideally, the solution should cause no change to the table's structure.  But any solution is fine if the problem can be solved, which is the bottom line.

Richard Rost @Reply

10 days ago

Kevin, one clarification before I take a crack at this. I notice that within duplicate last names, the first names in your sample appear to be sorted descending. For example, Bennett has James before Henry, Edwards has Samuel before Liam, and Mitchell has Sophia before Ava.

Can we assume that FirstName DESC is actually the secondary sort order, or is that just a coincidence in this particular sample? In other words, if there were many more records, could the first names within the same last name appear in any arbitrary order?

That makes a big difference in how I'd approach the solution.

	Kevin Yip OP @Reply 10 days ago
Thanks for catching that mistake. Both fields should be ascending, last name first, and first name second. Maybe you could just edit the post for me to avoid cluttering up the thread. Thanks again.

A Toykan @Reply

9 days ago

Hi Kevin Try this:

SELECT ID, LastName, FirstName
FROM yourtable
WHERE LastName > Mitchell OR (LastName = Mitchell AND FirstName >= Sophia)
ORDER BY LastName ASC, FirstName ASC

1.Anderson, Alexander
2.Bennett, Henry
3.Bennett, James
4.Brooks, Noah
5.Carter, Olivia
6.Coleman, Mia
7.Cooper, Benjamin
8.Edwards, Liam
9.Edwards, Samuel
10.Hayes, Isabella
11.Mitchell, Ava (Sorted ASC, Ava is before Sophia)
12.Mitchell, Sophia (Juntion Point)
13.Parker, Daniel
14.Peterson, Grace
15.Phillips, Chloe
16.Reynolds, Ethan
17.Richardson, Charlotte
18.Sullivan, Emma
19.Turner, Lucas
20.Wallace, Amelia

Records 1–10: Filtered out because their last names come alphabetically before Mitchell.
11th Record (Mitchell, Ava): Has the last name Mitchell, but fails the condition FirstName >= Sophia (Ava<Sophia). Therefore, it is excluded.
12th Record (Mitchell, Sophia): Has the last name Mitchell and satisfies the condition FirstName >= Sophia (Sophia >= Sophia). Included (1st returned record).
Records 13–20 (Parker – Wallace): Included directly regardless of their first names because the first condition (LastName > Mitchell) is true (e.g., Parker > Mitchell). (Returned records 2–9).

Therefore, based on the example in the problem, the number of records meeting the condition is actually only 9. However, if you want to structure this for a larger dataset and list only the first 10 consecutive records from that point forward, you must use
SELECT TOP 10 ID, LastName, FirstName
FROM yourtable
WHERE LastName > "Mitchell" OR (LastName = "Mitchell" AND FirstName >= "Sophia")
ORDER BY LastName ASC, FirstName ASC

Kevin Yip OP @Reply

9 days ago

A  Very nice solution!  Another way to solve it is via concatenation of the last name and first name fields:

     SELECT * FROM NamesT WHERE LastName & "|" & FirstName >= "Mitchell|Sophia"

A delimiter "|" is necessary because without it, "WoodTiger" would be greater than "WoodsTiger" even though it shouldn't be.  A delimiter would make "Wood|Tiger" less than "Woods|Tiger" as it should.

This method is also great if you have more than two fields to deal with.

	Donald Blackwell @Reply 9 days ago
As your list was presented, I got the same exact result with the following: SELECT * FROM YourTable WHERE LastName >= "Mitchell" ORDER BY LastName, FirstName Again, keep in mind, that is assuming the view being queried is provided as you provided above because the database takes all records with a last name of "Mitchell" or greater and then sorts by lastname, firstname

	Kevin Yip OP @Reply 9 days ago
Donald I should've said that my list is just a sample, a partial list of a much bigger table that have names that we are not aware of. In other words, the solution should work for any situation, not just the one particular situation shown in my top post.

A Toykan @Reply

9 days ago

Kevin Great alternative! One caveat worth mentioning though.. Access's default text comparison, the General/ linguistic sort order used by Jet/ACE, as opposed to binary/ordinal can treat certain punctuation and symbol characters as low-weight or ignorable during comparison. That means the "|" delimiter may not reliably preserve the ordering it's meant to enforce in all cases. That could undermine exactly what the delimiter is meant to guarantee, keeping "Wood|Tiger" and "Woods|Tiger" properly distinct. This is a genuine technical issue and a known Access Jet/ACE behavior.

A safer option is to use a character that Access's default collation won't discount, such as a digit or letter that can't naturally appear in the field. eg. concatenating with a fixed-width separator, or padding with something like a number or a letter which sort predictably.

Kevin Yip OP @Reply

9 days ago

A In the US version at least, Access does not ignore special characters in sorting. When "Option Compare Database" (the default) or "Option Compare Text" is used in VBA, Access also groups accented alphabets together in sorting (see picture below), even though accented characters have higher ASCII values than the alphabet "z." I agree that delimiters can't be characters that could appear in names, such as commas and periods (Martin Luther King, Jr.). But a fixed-width field may be tricky to implement because of the need to determine what that width should be.

	Kevin Yip OP @Reply 9 days ago

Donald Blackwell @Reply

9 days ago

Kevin Interesting.... I submitted that response last night when the site was wonky and then immediately after, thought better and was going to delete it as soon it came up but got the error that was going on instead so didn't worry about it.

I've been having issues with my web browsers randomly resubmitting stuff on sites that I previously submitted that didn't post but that's the first time on this site for any browser. Happens to me on FB alot, regardless if I'm using Edge, FireFox, Opera or Zen.

I had hoped that would resolve since I just did a complete Repair-in-Place the other evening but apparently not :(

A Toykan @Reply

8 days ago

Kevin Yeah I agree with you, a fixed-width field would be more tricky. When I looked into it, I couldn’t find any record showing that the Access issue that can occur when using special characters such as | has actually been resolved. With that in mind, I still consider it risky to use them, so I’d rather stick with my usual practice and avoid them.

As Richard likes to say, “it works until it doesn’t.” So I think being cautious is always a good idea. :)

Donald A worn out mouse micro switch can sometimes cause a single click to be registered as two clicks. So even though you think you clicked the Submit button once, the mouse may actually be sending two separate click signals. If this happens in all browsers, I would suspect the mouse first, since it would be a hardware issue rather than something browser specific.

Another possibility is the browser’s restore previous session feature. In some cases, while restoring the previous session or checking the restore point record, the browser may end up replaying the last POST request. This can happen silently in the background.

And, although it’s less common, some antivirus programs have web protection modules that intercept and inspect HTTP traffic. In rare cases, they can cause requests to be buffered or even sent again.

I’d start with the simplest test: try a different mouse. That’s the quickest and, in my opinion, the most likely way to confirm or rule this out.
If the problem still occurs, then I’d disable the browser’s session restore feature in all browsers and test again.
If it still happens after that, temporarily disable the antivirus web protection and see whether anything changes.

	Donald Blackwell @Reply 8 days ago
Great ideas! Thanks A

Richard Rost @Reply

7 days ago

Ah, got it. Since the intended sort is LastName ASC, FirstName ASC, then A's solution is the one I would use:

LastName > "Mitchell" OR (LastName = "Mitchell" AND FirstName >= "Sophia")

That's the proper way to express "on or after this point" in a two-field sort. It is basically a compound comparison: anything with a later last name qualifies, and for the same last name, only first names at or after Sophia qualify.

And yes, with FirstName ascending, Mitchell, Ava comes before Mitchell, Sophia, so it should not be included. That leaves 9 records in the sample, not 10.

My question before was whether the apparent ordering of FirstName was accidental or a rule we could count on. If the records within each LastName could appear in arbitrary order, then there would be no meaningful definition of "from Mitchell, Sophia onward" without another field defining that order. Tables themselves have no guaranteed record order, so a recordset loop would not solve that by itself either. You would still need an ORDER BY clause, perhaps using ID as an additional tie-breaker if that is the desired order.

Kevin's concatenation method is clever, especially when comparing several fields, but I would generally favor the explicit OR condition. It clearly shows the intended logic, doesn't depend on delimiter behavior, and can make better use of an index on LastName and FirstName.

But yeah, my first stab would most likely be concatenating, too. I've that in the past myself. I think I even taught that in one of my lessons, lol.

Next Unseen