By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Union select with All need to order by number

P: n/a
I have the following SQL statement in code that is set to the RowSource of a
combobox. The combobox has two columns.

SELECT '(ALL)' As Site, '' As [Fac Count], 0 As SortFirst
FROM [BUILDING_TABLE]
GROUP BY SITE_COMPLEX

UNION SELECT SITE_COMPLEX As Site, Count(BLDG_ID) As [Fac Count], 1 As
SortFirst
FROM [BUILDING_TABLE]
WHERE Nz(SITE_COMPLEX,'') IN
(SELECT SITE FROM [USER_SITE_TABLE] WHERE ALLOWED_TO_VIEW = True And User_ID
= 'John_Doe') GROUP BY SITE_COMPLEX ORDER BY SortFirst, [Fac Count] DESC

I've split it up a bit, hopefully for easier reading. It works but I want
the sorting on Fac Count to be a numerical sort and not a textual sort.
Because I have in the first part of the statement (before the UNION), "" As
[Fac Count], the sorting is textual. If I change it to 0 As [Fac Count], the
sorting is numerical which is what I want. But I don't want 0 to show up
next to (All) in the combobox. I want it to be blank.

I tried

Null As Site - this gave gibberish for the counts in the combobox
Empty As Site - this caused an error.
As Site - this also cause an error

Don't know what else to try.

Thanks for any help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 26 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:6553251fbc7e9@uwe:
I have the following SQL statement in code that is set to the
RowSource of a combobox. The combobox has two columns.

SELECT '(ALL)' As Site, '' As [Fac Count], 0 As SortFirst
FROM [BUILDING_TABLE]
GROUP BY SITE_COMPLEX

UNION SELECT SITE_COMPLEX As Site, Count(BLDG_ID) As [Fac
Count], 1 As SortFirst
FROM [BUILDING_TABLE]
WHERE Nz(SITE_COMPLEX,'') IN
(SELECT SITE FROM [USER_SITE_TABLE] WHERE ALLOWED_TO_VIEW =
True And User_ID = 'John_Doe') GROUP BY SITE_COMPLEX ORDER BY
SortFirst, [Fac Count] DESC

I've split it up a bit, hopefully for easier reading. It
works but I want the sorting on Fac Count to be a numerical
sort and not a textual sort. Because I have in the first part
of the statement (before the UNION), "" As [Fac Count], the
sorting is textual. If I change it to 0 As [Fac Count], the
sorting is numerical which is what I want. But I don't want 0
to show up next to (All) in the combobox. I want it to be
blank.

I tried

Null As Site - this gave gibberish for the counts in the
combobox Empty As Site - this caused an error.
As Site - this also cause an error

Don't know what else to try.

Thanks for any help.
You need a number as [fac count] in the upper select.

Do you wish to display [fac count] as a column in the listbox?

If you dont, just put 0 as [Fac Count] in the first select
statement.
If you do, you could put a dcount("BLDG_ID","BUILDING_TABLE") as
[Fac Count].
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 26 '06 #2

P: n/a
Thanks for the reply.

I do want to display [fac count] in the combobox, but next to the "(All)"
entry I want it to be spaces. Next to the other sites I want the actual
number.

So the combobox will have entries similar to the following:

(All)
New York 224
Boston 115
Las Vegas 90
Tucson 62
And so on. I have a good reason why I want (All) to not have a record count
next to it, but it's a very long story and is not really germane to my
question. It looks like there might not be a way to do what I want.

Bob Quintal wrote:
>I have the following SQL statement in code that is set to the
RowSource of a combobox. The combobox has two columns.
[quoted text clipped - 29 lines]
>>
Thanks for any help.

You need a number as [fac count] in the upper select.

Do you wish to display [fac count] as a column in the listbox?

If you dont, just put 0 as [Fac Count] in the first select
statement.
If you do, you could put a dcount("BLDG_ID","BUILDING_TABLE") as
[Fac Count].
--
Message posted via http://www.accessmonster.com

Aug 26 '06 #3

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:655bb9acb1873@uwe:
Thanks for the reply.

I do want to display [fac count] in the combobox, but next to
the "(All)" entry I want it to be spaces. Next to the other
sites I want the actual number.

So the combobox will have entries similar to the following:

(All)
New York 224
Boston 115
Las Vegas 90
Tucson 62
And so on. I have a good reason why I want (All) to not have
a record count next to it, but it's a very long story and is
not really germane to my question. It looks like there might
not be a way to do what I want.

Bob Quintal wrote:
>>I have the following SQL statement in code that is set to
the RowSource of a combobox. The combobox has two columns.
[quoted text clipped - 29 lines]
>>>
Thanks for any help.

You need a number as [fac count] in the upper select.

Do you wish to display [fac count] as a column in the listbox?

If you dont, just put 0 as [Fac Count] in the first select
statement.
If you do, you could put a dcount("BLDG_ID","BUILDING_TABLE")
as [Fac Count].
Don't give up hope yet. Put the count column into the query
twice. In the (all) statement, use the number in one of those
columns, and your " " in the second. Sort on the one with the
number, but make it's column width 0, and display the one with
the space. It will have the numbers left justified, but the sort
order will be correct. If you feel really industrious, you could
even replace the displayable version with an expression that
adds spaces to the left of the string, making it right
justified.

..

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 26 '06 #4

P: n/a
Tried your idea and it appears to have worked. Need to test it some more.

Thanks!

Bob Quintal wrote:
>Thanks for the reply.
[quoted text clipped - 29 lines]
>>>If you do, you could put a dcount("BLDG_ID","BUILDING_TABLE")
as [Fac Count].

Don't give up hope yet. Put the count column into the query
twice. In the (all) statement, use the number in one of those
columns, and your " " in the second. Sort on the one with the
number, but make it's column width 0, and display the one with
the space. It will have the numbers left justified, but the sort
order will be correct. If you feel really industrious, you could
even replace the displayable version with an expression that
adds spaces to the left of the string, making it right
justified.

.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 27 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.