Rowsource of Unbound Query for a ComboBox (1 Viewer)

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
Hi
I'm an Access hobbyist so my skills are pretty rudimentary, especially in SQL (I've started working on that). I found this code on a website and it serves my purpose perfectly. It took me a couple of days to figure out what's going on, but I now get how the Query works. I just can't figure out why they have done one of things they have done; perhaps someone can enlighten me.

The data in the example is a list of counties. A continuous form is used to display records, and an unbound combobox is used, in the form header, to filter records according to the county chosen in the combobox drop down list--pretty standard stuff. What is, somewhat, unique is that the when the form opens the combobox displays "All Counties"

This is accomplished with a Union which, in the second Select, adds a zero in the CountyID and the text "All Counties" in county name. These only appear in the results of the query, nothing is actually added to the table. The Onload event of the form sets the value of the the Combobox to zero so that "All Counties" is displayed.

This is the SQL:

SELECT CountyID, County, 1 As SortColumn FROM Counties UNION SELECT 0, "All Counties", 1 FROM Counties
ORDER BY SortColumn, County;

Like I said, I now get how this works. However, I cannot for the life of me figure out why they have created a third column "SortColumn" and filled it with 1's. Then they have Ordered the results, firstly, by SortColumn. I don't understand what purpose this serves, especially since all rows of the results have the same value for SortColumn.

Greatly appreciate any help anyone could offer.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:48
Joined
Jul 9, 2003
Messages
16,244
My guess is that to get "All Countries" to the top of the list in the combo-box so that it is the first and main entry showing, you might need to add a sort column to cover all circumstances. Maybe they are just playing safe. Maybe they are using a combobox they used for something else and it just happened to have that sort field in it... Seeing as "All Countries", starts with an "A" it would probably migrate to the top of the list most of the time. I prefer <ALL> as the special character "<" is generally listed first.

There's a Blog on my website about this exact same situation, my solution, or rather, one I found years ago which uses a Function:-

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:48
Joined
Feb 28, 2001
Messages
26,996
Actually, I would suggest taking a closer look at that. Is it UNION SELECT 0, "All Counties", 1 or UNION SELECT 0, "All Counties", 0 ?

Because if it is the second one, that lets them put a header of "All Counties" at the top of the list because of putting the 0 before all the 1s.

However, I think Uncle G's answer is OK too, since that wouldat least place an entry in the combo box that has a number of zero to go with "All Counties" as a selection.

If that "All Counties" entry really IS a 1, then I have to say I'm not sure why they have a 1 in that spot. On the other hand, if the record source Counties is itself the result of a query, then they may be guarding against empty records that would contain nulls.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:48
Joined
Jul 9, 2003
Messages
16,244
You can download the sample file from my website for free using the coupon code:- pihayzp
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:48
Joined
Feb 19, 2013
Messages
16,553
depends on the data from wherever you got this example from.

sorting by sortcolumn ensures your 'All Counties' appears at the top of the dropdown.

it may be that data included a county that alphabetically comes before All. If your data doesn't, then you don't need the sortcolumn, just sort on county - but as an example to be used who knows where, it is right to include it.

As a general rule with union queries, the order records are displayed will put the last union at the top - so since this is just one record if you did not have the sort column and were not worried about displaying the counties in alphabetical order, you could drop the column
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
My guess is that to get "All Countries" to the top of the list in the combo-box so that it is the first and main entry showing, you might need to add a sort column to cover all circumstances. Maybe they are just playing safe. Maybe they are using a combobox they used for something else and it just happened to have that sort field in it... Seeing as "All Countries", starts with an "A" it would probably migrate to the top of the list most of the time. I prefer <ALL> as the special character "<" is generally listed first.

There's a Blog on my website about this exact same situation, my solution, or rather, one I found years ago which uses a Function:-


Thanks for the response. It's late here so I'll have a look at your blog tomorrow. I should have included one more thing, which is that the bount column of the combobox is 1, which is CountyID, Counties is 2. The width of County ID is zero so only County is displayed in the list. The reason "All Counties" appears when the form is opened is that the value of the combobox is set to zero on the OnLoad event of the form. I added a record to the Counties table that comes before All Counties alphabetically. All Counties is still displayed when the form opens but on the drop down list it shows in second place. I think you might be right about it being something that was used in a previous circumstance. If I remove "1 as SortColumn", and all the related SQL the results remain the same. I don't know how to get in touch with the person who posted this originally but I'll try a little harder and see if I can get an answer from them

Thanks again for taking the time to contribute
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
Actually, I would suggest taking a closer look at that. Is it UNION SELECT 0, "All Counties", 1 or UNION SELECT 0, "All Counties", 0 ?

Because if it is the second one, that lets them put a header of "All Counties" at the top of the list because of putting the 0 before all the 1s.

However, I think Uncle G's answer is OK too, since that wouldat least place an entry in the combo box that has a number of zero to go with "All Counties" as a selection.

If that "All Counties" entry really IS a 1, then I have to say I'm not sure why they have a 1 in that spot. On the other hand, if the record source Counties is itself the result of a query, then they may be guarding against empty records that would contain nulls.


Thanks for the suggestion. My post is accurate and it is a 1 not a zero. I should have mentioned that the bound column is 1, which is CountyID the width of which is set to zero so all that shows in the list is County. The reason that "All Counties" is in the box when the form loads is that the OnLoad event of the form sets the value of the combobox to zero. On the drop down list All Countries appears alphabetically and when I added a new county that come before All Counties, alphabetically, All Counties shows up second on the list.

Thanks for the help
 

Micron

AWF VIP
Local time
Today, 06:48
Joined
Oct 20, 2018
Messages
3,476
As a general rule with union queries, the order records are displayed will put the last union at the top
Does this depend on data type? I was not able to replicate that on a number field.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:48
Joined
Feb 19, 2013
Messages
16,553
My post is accurate and it is a 1 not a zero.
OK - the post may be accurate - but it is wrong

the 1 in the 2nd part of your union query should be 0 - read all the other posts
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:48
Joined
Feb 19, 2013
Messages
16,553
As a general rule with union queries, the order records are displayed will put the last union at the top
. Maybe - I'll try later
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:48
Joined
May 7, 2009
Messages
19,169
there is no need for SortColumn since their values are the same.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:48
Joined
Feb 19, 2013
Messages
16,553
I was not able to replicate that on a number field.

for me, this query puts the union element at the top (with actions unsorted)
SELECT ActionPK, ActionName
FROM admActions
union select 0, "All" FROM admActions;

This one places the union element randomly in the (unsorted) list
SELECT ActionName, ActionPK
FROM admActions
union select "All", 0 FROM admActions;

This one places the union element at the top - but sorted on the meaningless PK
SELECT ActionName, ActionPK
FROM admActions
union select "All", 0 FROM admActions
ORDER BY ActionPK;
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
OK - the post may be accurate - but it is wrong

the 1 in the 2nd part of your union query should be 0 - read all the other posts

Hi and thanks for taking the time to help out. I summed up everybody's comments in a separate reply.
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
there is no need for SortColumn since their values are the same.
Thanks again arnelgp. You're right (I think the original SQL is wrong). I have a summary posted in a separate reply.
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
for me, this query puts the union element at the top (with actions unsorted)
SELECT ActionPK, ActionName
FROM admActions
union select 0, "All" FROM admActions;

This one places the union element randomly in the (unsorted) list
SELECT ActionName, ActionPK
FROM admActions
union select "All", 0 FROM admActions;

This one places the union element at the top - but sorted on the meaningless PK
SELECT ActionName, ActionPK
FROM admActions
union select "All", 0 FROM admActions
ORDER BY ActionPK;
Thanks again CJ. This has been a huge learning experience for me and at this point my brain is a little burnt out. I'll have a closer look at what you posted in a few days. I have posted a summary in a separate response.
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
Hello and thanks to all of you for helping out with my question. I really do appreciate it. Here's a summary, as best I can do it, of what I think the situation is.

First of all, this code was posted by Ken Sheridan. Here is the location, the file in question is called ComboDemo, and the example I refer to here is the second from the bottom, on his start form, and is called "Open form for 'Drilling Down' through hierarchies to return records".

It is a form that drills down using three comboboxes, one of which is County. When the form loads, and following any related events, the text "All Counties" is displayed in this combobox. The Rowsource for the combobox is:


SELECT CountyID, County, 1 As SortColumn FROM Counties UNION SELECT 0, "All Counties", 1 FROM Counties
ORDER BY SortColumn, County;

As the SQL is written, I can't figure out the purpose of the SortColumn. As pointed out by arnelgp, no matter what the data is, the value for SortColumn will always be 1. So, what's the point of Ordering by SortColumn?

The conversation kind of got on to the order of the drop down list, which is fine as far as I'm concerned--I welcome all replies--but it wasn't really what my question was. Curiously however, I think that is where the solution lies.

The code for the form assigns a value of zero to this combobox whenever "All Counties" should be displayed. The way the SQL is written however, does not ensure that "All Countries" will appear at the top of the combobox dropdown list. I entered a county "Aberdeen" into the table and it appears, as it should, above "All Counties"

However, it would give purpose to the sort column if, as pointed out by CJ and The_Doc_Man that if instead of:

Union Select 0,"All Counties",1

one uses:

Union Select 0,"All Counties", 0

Then "All Counties" would always appear at the top of the drop down list and that would give purpose to the inclusion of the SortColumn

So it looks to me like the SQL in the demo has a slight error in it. I don't know how to contact Mr. Sheridon so I can't ask him directly. I'll try to find a way to get in touch with him directly, or through a forum that he is active on.

Thanks again for all the help. This has been a tremendous learning experience for me and this forum has once again proved to be a great resource.
 

AJJJR

Registered User.
Local time
Today, 03:48
Joined
Mar 19, 2018
Messages
56
So, I finally did touch base with Ken Sheridan, who offered his version of how to do a "drill down" on his onedrive page.

Indeed it was an error. His reply was:

Mea culpa! The SortColumn value in the second part of the UNION operation should of course be zero, to force the 'All Counties' row to sort first in the list. The reason this had got through the net is that, with my limited list of counties for demo purposes, 'All Counties' sorts before any of the country names alphabetically.

As it happens the same would be true even if I'd included all current English counties. That's not to say that the SortColumn can be excluded, however. The possibility of county names being changed, as was the case in the 1974 local government reorganisation, cannot be ruled out.

I've now corrected the file.

Thanks to all that participated. I really did learn a lot here.
 

Users who are viewing this thread

Top Bottom