Sort Order In Query Dies On Filter

[VariableZ]

I have a table (Table1) with a combo box (Table1Column7) that gets data from another table (Table2) through a query (Query1). Query1 filters out certain data. Table2 has an ID field, and the combo box from Table1 references that as the bound column so that I don't have to store so much information in Table1, but the next column over (Table2Column2) shows up when you look at Table1. I then have a query (Query2) that left joins Table1 and Table2 (and others) and sorts the information by 3 columns, including Table2Column2 (if I sort by Table1Column7, the sort is based on Table2s ID numbers, which is not helpful). It works properly... at first.

The problem is that if I filter Query2 by Table1Column7, the sort order becomes based on the ID field in Table1 (or the first field listed in Query2, but Table1's ID field just happens to be first). I have no such problem when filtering any other column. Any ideas where I might have gone wrong? And, more importantly, any ideas how I might fix it?

[Dal Jeanis]

Wow, you like your life complicated.

First question - By Table1, are you referring to a form? A table with a combo box is... not something I can visualize. I'm hoping that you have just glitched in the description, or left the name of the form as = the name of the table, which Access does by default and I always change right off.

Okay, I got it. I'm guessing that you are using the primal invention of the evil one (a.k.a. lookup fields) - see
http://access.mvps.org/access/lookupfields.htm

My second guess is that your filter is being deceived by that spawn of satan - you are trying to filter a numeric field on an alpha value or vice versa. Access denies you this evil practice, and therefore after eliminating the offensive material, it does whatever it feels like doing with the rest of your query.

So, the simple solution might be to correct your filter to the opposite of what it is. If you are filtering on the numeric value, switch to the alpha, or vice versa. I do not guarantee that this will escape the influence of the evil one.

The metaphysically virtuous solution would be to eliminate the lookup aspect of the field at the table level, and change the combo boxes, wherever they appear, to accomplish the same thing without enering the shadow of the devil.

[June7]

Lookups themselves are not evil, it is the set up in table of Lookups with alias, not to be confused with lookups (comboboxes) on forms.

[VariableZ]

So if I'm correctly reading that link you sent, Dal, I shouldn't use lookups in my tables at all? Up until now I thought that using a lookup field was the 'right' thing to do, as it stored a number field instead of text, thus saving space, and for a few other reasons. Is there any good reason to use a lookup in a table?

[Dal Jeanis]

Actually, June7 answered your question before you asked it! June7's just that good!

The last paragraph of my tongue-in-cheek post indicates the normal practice - you don't set the lookup as a characteristic of the table, you just use a lookup in forms and queries as needed. Here's a current thread about using either a frame or a combo box for that display: https://www.accessforums.net/forms/o...ing-36228.html

The bottom line is that a lookup in the table itself is confusing: Access displays one thing, while a different thing is stored. that might be fine for a simple user with simple needs, but it leads to really baffling results like yours (assuming I've correctly diagnosed the source of your app's odd behavior).

Try switching the filter to the opposite of what it is: if you're currently using the key, use the text as filter instead. If you're currently using the text, use the key as filter. If that fixes the problem, then you have learned something.

By the way, a much more basic and important rule is this: ALWAYS back up your database before you begin making changes to it, and back up frequently throughout the process if you are making random attempts to diagnose a problem, especially those changes proposed by strangers on the Internet. Some of them peoples is crazy, others is ignorant, and it's your stuff that you're messing with. Act accordingly.

My standard is to append the date and a sequence letter onto the name of the file, so the fifth backup today might be Mydb 2013-0709E.accdb. When you've figured out the fix, then you back up the production version, add only that fix to the production version, verify the fix, then back up the new production database. I know that sounds paranoid, but it's not paranoia when the gremlins really ARE out to get you. (Obviously, if your shop has a standard deployment procedure, then you use that instead.)

[VariableZ]

The table lookup field is indeed what I did. I was operating under the assumption that it was a good idea; standard operating procedure. While the underlying field was numeric, I filtered by the text value, and while that technically worked, my sort then died as a result. I am now in the process of changing my data so that the underlying data is the actual text, and no lookup is used in the table. So far, my sort is now intact regardless of my filters, so while it's a bit of an undertaking to change all the code, reports, and forms, I'm thinking it'll probably be worth it once I get everything changed over. I fully agree with you regarding the backup procedure. Gremlins are everywhere. Thanks for your help.

[June7]

I avoid alias lookups whenever possible and save the actual descriptor value. I know a number key can save space but storage is cheap nowadays. Can still use a table as source for list but instead of a number, as example, for state ID why not just the state two-letter code as the primary key? Or instead of 1, 2, 3, 4 for Excellent, Good, Fair, Poor why not just the words? If the descriptor is short, why not? Saves a lot of headaches.

[Dal Jeanis]

June7 - I'd agree on everything but the "good", "fair", "poor", since my life is interesting enough as it is. Maybe store as "1 - Awful", "2 - Poor" etc?

[June7]

Now don't bite off that tongue in your cheek. But seriously, are you suggesting the number saved in the text string?

[Dal Jeanis]

If you want them to sort in any order other than...

Excellent
Fair
Good
Poor

[June7]

Got it. Guess that's having your cake and eating it, too. Users see meaningful descriptors in combobox and still allows simple filter/sort without table joins.

[VariableZ]

You guys are a trip. Thanks for your help!

[Dal Jeanis]

Yeah, we keep ourselves entertained pretty well. Take Care.