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

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

Adding to combo box 7
query using combo box value 3
Using combo box(s) to create a query 1
Combo Box in a Query 3
Combo Box 2 Bound Columns? 4
Incorrect Data in Combo Box 3
updating combo box content 2
Need help making query with criteria based on combo box selections 18

Top