Adding <ALL> to a combo box

  • Thread starter AccessNoviceButTrying
  • Start date
A

AccessNoviceButTrying

Hi All,

I was hoping someone could help me with some SQL coding.

I have a table named [Priority].

I want a combo box on a form to pull the data from the table as its choices
but I want to add <All> as the top option. Please help.

Thanks
 
K

Krzysztof Naworyta

Juzer AccessNoviceButTrying

| I was hoping someone could help me with some SQL coding.
|
| I have a table named [Priority].
|
| I want a combo box on a form to pull the data from the table as its
| choices but I want to add <All> as the top option. Please help.

Select
id, field1, 1 as sort_order
From
Priority
UNION ALL
Select Top 1
0, "<All>", 0
From
MSysObjects
Order by
sort_order, field1
 
B

BruceM via AccessMonster.com

I don't understand the use of MSysObjects, but there is plenty I don't
understand. Why not just the following:

SELECT CustomerID, CompanyName
FROM Customers
UNION Select 0, "(All)" From Customers
ORDER BY CustomerID;

This is adapted from an example here:

http://www.mvps.org/access/forms/frm0043.htm

In the example aliases are used for the UNION part of the query. I don't see
the value of that. Also, in the example Null is the first UNION value.
Assuming CustomerID starts at 1 or more I use a value (0) rather than Null.


Krzysztof said:
Juzer AccessNoviceButTrying

| I was hoping someone could help me with some SQL coding.
|
| I have a table named [Priority].
|
| I want a combo box on a form to pull the data from the table as its
| choices but I want to add <All> as the top option. Please help.

Select
id, field1, 1 as sort_order
From
Priority
UNION ALL
Select Top 1
0, "<All>", 0
From
MSysObjects
Order by
sort_order, field1
Click to expand...
 
K

Krzysztof Naworyta

BruceM via AccessMonster.com wrote:
| I don't understand the use of MSysObjects, but there is plenty I don't
| understand. Why not just the following:
|
| SELECT CustomerID, CompanyName
| FROM Customers
| UNION Select 0, "(All)" From Customers
| ORDER BY CustomerID;

Because UNION (that means: UNION DISTINCT) is time consuming.

It is much better to use UNION ALL with SELECT DISTINCT:

Select ... From...
UNION ALL
Select Distinct 0, "<All>" From AnyTable

But using Top 1 is the most efficient.

| This is adapted from an example here:
|
| http://www.mvps.org/access/forms/frm0043.htm
|
| In the example aliases are used for the UNION part of the query. I
| don't see the value of that.

except for ease (?) of understanding while reading ;)

| Also, in the example Null is the first
| UNION value. Assuming CustomerID starts at 1 or more I use a value
| (0) rather than Null.

agreed! (If you sure that you has not put 0 to autonumber - it is possible!)
Null makes trouble, because selecting it your <All> disappears if first
column is hidden...

--
KN


| Krzysztof Naworyta wrote:
|| Juzer AccessNoviceButTrying
||
||| I was hoping someone could help me with some SQL coding.
|||
||| I have a table named [Priority].
|||
||| I want a combo box on a form to pull the data from the table as its
||| choices but I want to add <All> as the top option. Please help.
||
|| Select
|| id, field1, 1 as sort_order
|| From
|| Priority
|| UNION ALL
|| Select Top 1
|| 0, "<All>", 0
|| From
|| MSysObjects
|| Order by
|| sort_order, field1
 
B

BruceM via AccessMonster.com

Krzysztof said:
| I don't understand the use of MSysObjects, but there is plenty I don't
| understand. Why not just the following:
[quoted text clipped - 3 lines]
| UNION Select 0, "(All)" From Customers
| ORDER BY CustomerID;

Because UNION (that means: UNION DISTINCT) is time consuming.

It is much better to use UNION ALL with SELECT DISTINCT:

Select ... From...
UNION ALL
Select Distinct 0, "<All>" From AnyTable
Click to expand...

The values 0 and "All" are literal, not selected from a table. The union
query essentially appends a single record with the values 0 and "All" onto
another recordset. Neither "Distinct" or "Union All" affects the end result
in this situation. There is nothing to narrow down with Distinct (or Top 1),
as the UNION recordset, such as it is, consists of one "record". I can't
imagine there is an efficiency improvement one way or the other.
But using Top 1 is the most efficient.

| This is adapted from an example here:
|
| http://www.mvps.org/access/forms/frm0043.htm
|
| In the example aliases are used for the UNION part of the query. I
| don't see the value of that.

except for ease (?) of understanding while reading ;)
Click to expand...

I guess that's a matter of opinion.
| Also, in the example Null is the first
| UNION value. Assuming CustomerID starts at 1 or more I use a value
| (0) rather than Null.

agreed! (If you sure that you has not put 0 to autonumber - it is possible!)
Null makes trouble, because selecting it your <All> disappears if first
column is hidden...
Click to expand...

I have used the code with Null, and it works, but for some reason I just
prefer to use a number if CustomerID (in this example) is a number or
autonumber field.
--
KN

|| Juzer AccessNoviceButTrying
||
[quoted text clipped - 16 lines]
|| Order by
|| sort_order, field1
Click to expand...
 
K

Krzysztof Naworyta

"BruceM via AccessMonster.com" <u54429@uwe> ...

| The values 0 and "All" are literal, not selected from a table.

agreed!

| The union
| query essentially appends a single record with the values 0 and "All"
| onto another recordset.
| Neither "Distinct" or "Union All" affects the end result
| in this situation. There is nothing to narrow down with Distinct
| (or Top 1),
| as the UNION recordset, such as it is, consists of one "record".

you're wrong!
UNION appends a single record onto another recordset that MUST be
calculated as DISTINCT (!)

| I can't
| imagine there is an efficiency improvement one way or the other.

there is no matter of imagination. Just make tests:

BigTable has 100000 records, indexed on both fields.
ID is PrimaryKey

qr1Union:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION
SELECT 0, "<All>" From BigTable
ORDER BY Field1;

qr2UnionAllDistinct:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION ALL
SELECT DISTINCT 0, "<All>" From BigTable
ORDER BY Field1;

qr3UnionAllTop1:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION ALL
SELECT Top 1 0, "<All>" From BigTable
ORDER BY Field1;

qr4UnionAllTop1Small:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION ALL
SELECT Top 1 0, "<All>" From MSysObjects
ORDER BY Field1;



Module:

*****************************************************************
Sub test1()
Dim db As Database
Dim rs As DAO.Recordset

Dim qr As Long, i As Long, loops As Long
Dim t As Currency

Dim qrs

qrs = Array("qr1Union", "qr2UnionAllDistinct", "qr3UnionAllTop1",
"qr4UnionAllTop1Small")

Set db = CurrentDb
loops = 10

For qr = LBound(qrs) To UBound(qrs)

t = Timer
For i = 0 To loops
Set rs = db.OpenRecordset(qrs(qr))
rs.MoveLast
Next
Debug.Print qrs(qr), Timer - t
Next

Debug.Print String(30, "-")

For qr = UBound(qrs) To LBound(qrs) Step -1

t = Timer
For i = 0 To loops
Set rs = db.OpenRecordset(qrs(qr))
rs.MoveLast
Next
Debug.Print qrs(qr), Timer - t
Next


Debug.Print String(30, "-")
Debug.Print String(30, "-")
End Sub

******************** end of module *****************************

results:

no UnionAllTop1 UnionAllTopSmall UnionAllDistinct Union
---------------------------------------------------------
1 32,20 32,66 40,78 60,58
2 15,64 15,69 19,89 27,61
3 15,73 15,69 20,58 27,67
4 15,69 15,75 20,00 27,78
5 15,70 15,86 20,11 27,75
6 15,69 15,70 20,03 27,66
7 15,73 15,66 20,11 28,19
---------------------------------------------------------
Avg: 18,06 18,14 23,07 32,46
Avg: 100,00% 100,48% 127,78% 179,79%

As you can see, using your "simple" UNION is almost 2x slower than
UnionAllTop1.

Is it a big difference ?
Ep, it depends... ;)
Discussion slightly academic (there are not such big combos), but IMHO
worth to know...
 
B

BruceM via AccessMonster.com

The code for calculating the time could prove interesting in some cases.
However, we are not talking about the same queries, or in some other way are
not in synch. You agree that the values 0 and "All" are literal, not
selected from a table, yet all of your examples show "UNION ... From
BigTable".

Krzysztof said:
"BruceM via AccessMonster.com" <u54429@uwe> ...

| The values 0 and "All" are literal, not selected from a table.

agreed!
Click to expand...
Then why do all of your examples show "UNION ... From BigTable"?
| The union
| query essentially appends a single record with the values 0 and "All"
[quoted text clipped - 3 lines]
| (or Top 1),
| as the UNION recordset, such as it is, consists of one "record".

you're wrong!
UNION appends a single record onto another recordset that MUST be
calculated as DISTINCT (!)

| I can't
| imagine there is an efficiency improvement one way or the other.

there is no matter of imagination. Just make tests:

BigTable has 100000 records, indexed on both fields.
ID is PrimaryKey

qr1Union:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION
SELECT 0, "<All>" From BigTable
ORDER BY Field1;

qr2UnionAllDistinct:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION ALL
SELECT DISTINCT 0, "<All>" From BigTable
ORDER BY Field1;

qr3UnionAllTop1:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION ALL
SELECT Top 1 0, "<All>" From BigTable
ORDER BY Field1;

qr4UnionAllTop1Small:
-----------------------------------
SELECT ID, Field1 FROM BigTable
UNION ALL
SELECT Top 1 0, "<All>" From MSysObjects
ORDER BY Field1;

Module:

*****************************************************************
Sub test1()
Dim db As Database
Dim rs As DAO.Recordset

Dim qr As Long, i As Long, loops As Long
Dim t As Currency

Dim qrs

qrs = Array("qr1Union", "qr2UnionAllDistinct", "qr3UnionAllTop1",
"qr4UnionAllTop1Small")

Set db = CurrentDb
loops = 10

For qr = LBound(qrs) To UBound(qrs)

t = Timer
For i = 0 To loops
Set rs = db.OpenRecordset(qrs(qr))
rs.MoveLast
Next
Debug.Print qrs(qr), Timer - t
Next

Debug.Print String(30, "-")

For qr = UBound(qrs) To LBound(qrs) Step -1

t = Timer
For i = 0 To loops
Set rs = db.OpenRecordset(qrs(qr))
rs.MoveLast
Next
Debug.Print qrs(qr), Timer - t
Next

Debug.Print String(30, "-")
Debug.Print String(30, "-")
End Sub

******************** end of module *****************************

results:

no UnionAllTop1 UnionAllTopSmall UnionAllDistinct Union
---------------------------------------------------------
1 32,20 32,66 40,78 60,58
2 15,64 15,69 19,89 27,61
3 15,73 15,69 20,58 27,67
4 15,69 15,75 20,00 27,78
5 15,70 15,86 20,11 27,75
6 15,69 15,70 20,03 27,66
7 15,73 15,66 20,11 28,19
---------------------------------------------------------
Avg: 18,06 18,14 23,07 32,46
Avg: 100,00% 100,48% 127,78% 179,79%

As you can see, using your "simple" UNION is almost 2x slower than
UnionAllTop1.

Is it a big difference ?
Ep, it depends... ;)
Discussion slightly academic (there are not such big combos), but IMHO
worth to know...
Click to expand...
 
K

Krzysztof Naworyta

"BruceM via AccessMonster.com" <u54429@uwe> in
| However, we are not talking about the same queries, or in some other way
are
| not in synch. You agree that the values 0 and "All" are literal, not
| selected from a table, yet all of your examples show "UNION ... From
| BigTable".

Not all examples! My 4th query uses MSysObjects.
And I agree (something that suprised me a little), that using relatively
small table (MSysObjects), comparing to BigTable, brings nothing.




| Then why do all of your examples show "UNION ... From BigTable"?

The problem, that you don't want to understand, is not the second SELECT.
It's the first!
Using UNION (that means: UNION DISTINCT), forces the Jet to calculate
distinct rows in every "select" in UNION query. "Every" means the first
also (!)
;)

There is no difference while testing big or small table.
Every time testing it, UNION is ~2x slower then UNION ALL + Top 1
(in local database!)

Sometimes "2 times" means nothing (0,1 seconds compared to 0,2 seconds)
Sometimes it makes big difference (1 hour compared to 2 hours)

But in most situations it doesn't matter, while our tables have only
several rows...
 
B

BruceM via AccessMonster.com

I got thrown off track by your use of mSysObjects. Of course the UNION needs
a FROM. My brain wandered away for a while.

I did some tests on a table with several hundred records. From what I can
tell it is indeed faster to use Top 1, but I get inconsistent results with
Union and Union All. Sometimes Union All is faster, but often it is
essentially the same as Union. I probably would need to shut down everything
I can on the computer to get the cleanest possible reading. In any case, the
difference in ten iterations of the query is less than 2/100 of a second, so
the difference for a single iteration is less than 2/1000. As you say, good
to know, but of no real consequence in a typical situation where I would use
a union query to add "All" to a list. If things are bogging down and I am
using a union query (or any query for that matter) I will adapt your code to
test it.

I have to say I do not understand your use of Step in the second loop, and
could not find anything about it in VBA Help or in a web search. My only
guess, since you seem to be looping from Ubound backward to LBound, is that
you are stepping "backward" through the array. If you care to respond,
please do so without shouting out that I am wrong or that I do not understand.
I seek information, not reprimands.
 
K

Krzysztof Naworyta

Juzer BruceM via AccessMonster.com <u54429@uwe> napisa³
| I got thrown off track by your use of mSysObjects. Of course the UNION
| needs
| a FROM. My brain wandered away for a while.

This is access SQL.
You can not write:

Select ...From ...
UNION [ALL]
Select 0, 'All"

It's possible in MS SQL (T-SQL), but not in access, unfortunately.


| I did some tests on a table with several hundred records. From what I
| can tell it is indeed faster to use Top 1, but I get inconsistent
| results with Union and Union All. Sometimes Union All is faster, but
| often it is essentially the same as Union. I probably would need to
| shut down everything I can on the computer to get the cleanest possible
| reading. In any case, the difference in ten iterations of the query is
| less than 2/100 of a second, so the difference for a single iteration
| is less than 2/1000. As you say, good to know, but of no real
| consequence in a typical situation where I would use
| a union query to add "All" to a list. If things are bogging down and I
| am using a union query (or any query for that matter) I will adapt your
| code to test it.


Making some comparisons (even for small tables or fast-track procedures)
we can increase loops to avoid the influence of inaccurate of system clock
or OS...

Just set:
loops = 200 '(or more)

and see your results.


Lets forget for a moment about combos with "<All>".
Lets say we need a complex grouped query with extra last record with
summarizing some columns.
Is there a reason to force Jet to the extra work, by calculating distinct
rows by using UNION? I don't think so!


| I have to say I do not understand your use of Step in the second loop,
| and could not find anything about it in VBA Help or in a web search.
| My only guess, since you seem to be looping from Ubound backward to
| LBound, is that you are stepping "backward" through the array. If you
| care to respond,
| please do so without shouting out that I am wrong or that I do not
| understand. I seek information, not reprimands.

While testing anything we have to avoid everything that could affect the
results: RAM, other programs running in the background...

It is possible, that running qr1 first can give other results than when it
would be running as last one.

There was some interesting article made by Ken Getz...
Try this:
http://books.google.pl/books?id=qtg...resnum=6&ved=0CCMQ6AEwBQ#v=onepage&q=&f=false

page 377.

or: http://tiny.pl/hq36v


So my loops start from 0 to 3 (with optional Step=1) and then from 3 to 0,
with step = -1


Everything about "Step" you can find in VBA help in topic
"For ... Next...".
Just set focus on word "For" in module and press F1. ;)
 
K

Krzysztof Naworyta

Juzer BruceM via AccessMonster.com <u54429@uwe> napisa³


(...)
| If you care to respond,
| please do so without shouting out that I am wrong or that I do not
| understand.

I do apologise for my poor english.
We're talking through a huge help with google translator ;)
Keep this in your mind.
 
B

BruceM via AccessMonster.com

Comments inline.

Krzysztof said:
Juzer BruceM via AccessMonster.com <u54429@uwe> napisał
| I got thrown off track by your use of mSysObjects. Of course the UNION
| needs
| a FROM. My brain wandered away for a while.

This is access SQL.
You can not write:

Select ...From ...
UNION [ALL]
Select 0, 'All"
Click to expand...

Yes, I acknowledged I was in error.
It's possible in MS SQL (T-SQL), but not in access, unfortunately.

| I did some tests on a table with several hundred records. From what I
| can tell it is indeed faster to use Top 1, but I get inconsistent
[quoted text clipped - 8 lines]
| am using a union query (or any query for that matter) I will adapt your
| code to test it.

Making some comparisons (even for small tables or fast-track procedures)
we can increase loops to avoid the influence of inaccurate of system clock
or OS...

Just set:
loops = 200 '(or more)
Click to expand...

I forgot to mention I tried 100 loops.
and see your results.

Lets forget for a moment about combos with "<All>".
Lets say we need a complex grouped query with extra last record with
summarizing some columns.
Is there a reason to force Jet to the extra work, by calculating distinct
rows by using UNION? I don't think so!
Click to expand...

Since the question was about adding "All" I addressed it on that level. For
a more complex query I will consider what I have learned from this discussion.
| I have to say I do not understand your use of Step in the second loop,
| and could not find anything about it in VBA Help or in a web search.
[quoted text clipped - 3 lines]
| please do so without shouting out that I am wrong or that I do not
| understand. I seek information, not reprimands.

While testing anything we have to avoid everything that could affect the
results: RAM, other programs running in the background...

It is possible, that running qr1 first can give other results than when it
would be running as last one.
Click to expand...

If I was evaluating a query I would probably apply the test just to that
query. I may run several queries an once to see which seems to be more
efficient, but then I think I would test a single query several times.
There was some interesting article made by Ken Getz...
Try this:
http://books.google.pl/books?id=qtg...resnum=6&ved=0CCMQ6AEwBQ#v=onepage&q=&f=false

page 377.

or: http://tiny.pl/hq36v

So my loops start from 0 to 3 (with optional Step=1) and then from 3 to 0,
with step = -1

Everything about "Step" you can find in VBA help in topic
"For ... Next...".
Just set focus on word "For" in module and press F1. ;)
Click to expand...

Like so many things in Help, you need extra information to find the topic. I
think I get it now.

Thanks for the discussion.
 
B

BruceM via AccessMonster.com

I expect your English is far better than my command of any language other
than English. I appreciate the time you have put into your replies. As I
mentioned, I expect to use the timing code in the future.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clearing combo boxes and a text box 2
Creating a Search using a combo box and then creating a report. 4
Dynamic Naming of Form Button 3
Combo box 0
Trying to remove error from conflicting code 0
Weekly Report 2
Combo or list box 0
Adding sequence numbers to paragraphs 0

Top