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.
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.