Adding additional options to combo box based on SQL statement

[bbatson]

Hello,

Suppose I have combo box that is linked to the following SQL statement: "SELECT DISTINCT Column1 FROM Tbl1;"

Suppose the results are 3 fields: Red, Green, Yellow

What if I wanted to add an additional field (say, Blue) to this list, although it doesn't appear in the table? I know I can type in the values myself, but I want the combo box to look the table (which is dynamic/changing), and add a few additional options.

Any advice is very much appreciated.

[ajalwaysus]

What I have done in the past, and I don't know if there is a better way, but I UNION ALL the values I want together.

First, I created a dummy table called DUAL (it's an oracle thing), that has only one column and no values in it.

Second, I write my query as such:

SELECT "Blue" FROM DUAL
UNION ALL
SELECT DISTINCT Column1 FROM Tbl1;

This way my combo box has all the values in the table, plus the value "blue". I personally would say an actual value should be added to the table. I derived this idea so that I could include a blank in my combo boxes.

Hope this helps,
-AJ

[bbatson]

That's a great idea and I'm sure that will work. Thanks AJ.

[Megalog]

Actually you dont need to create a dummy table, you can alias the value in there.
This should work:

SELECT DISTINCT "Blue" AS [Column1] FROM Tbl1
UNION ALL
SELECT DISTINCT Column1 FROM Tbl1;

[ajalwaysus]

@Megalog
That would work too, I would just wonder which would be faster. Now faster doesn't mean much in this case, but my mind goes straight to worst case scenarios, like a massive table.

-AJ

[Megalog]

Hrm performance-wise, I'm not sure.

I would add another table like you suggested if the added values would possibly change down the road. But in cases where the data isnt going to be immense, or you simply want to add an "All" option, it's a quick permanent fix. Two different solutions for two separate scenarios.

[ajalwaysus]

@Megalog
It is a good argument that I would like to look into more later, but for this case, I don't see a downside to either solution. Nice catch Megalog.

-AJ

[NeoPa]

Interesting point. As I just happen to have a table lying around with 149,981 records in it I thought I'd try it out. I can confirm, without any shadow of a doubt, that trawling through these records, even with the DISTINCT predicate, takes a while to complete. Performance-wise it's not good. This is a bit of a shame as that's how I normally do it. I see a change of thinking coming my way :(

Having said that, if you know you have a local table with not too many records then this is a quick and easy method.

[NeoPa]

Well. Maybe not.

For absolute thoroughness I decided to test on the same table after closing and re-opening the database to lose any caching advantages. The results surprised me a little after my earlier tests.

I used two sets of SQL and they both came in at 5 seconds (as exactly as I could tell).

SELECT DISTINCT 'All' AS Result
FROM tblFileStructure
UNION ALL
SELECT DISTINCT Drive AS Result
FROM tblFileStructure

SELECT DISTINCT Drive AS Result
FROM tblFileStructure

I know that using the DISTINCT predicate is not typical in queries generally, but I wanted to include all the work done without skewing the results by displaying time of the various hundreds of thousands of records in the table.

I believe that, as long as the table used is already included in the SQL anyway, the technique of MegaLog's to add a single specific line to your output is actually perfectly workable without any performance delay.

[Megalog]

Maybe the SQL isnt optimal in the first place? I just ran a test against a table with a million unique records, using two SQL sets.

I've added a second value (Purple), removed the Distinct parameter except on the actual table lookup, and am using a Union instead of Union All.

SELECT "Blue" AS [Column1] 
FROM Tbl1 
UNION 
SELECT "Purple" as [Column1] 
FROM Tbl1  
UNION 
SELECT DISTINCT Column1 FROM Tbl1;

Here is the same code posted earlier, with a second value inserted.
If you dont specify DISTINCT for Blue or Purple, then it gets paired up with every value from Tbl1.

SELECT DISTINCT "Blue" AS [Column1]
FROM Tbl1 
UNION ALL 
SELECT DISTINCT "Purple" AS [Column1] 
FROM Tbl1 
UNION ALL 
SELECT DISTINCT [Column1] FROM Tbl1;

Sample 1 comes out 2 seconds faster than sample 2 in my tests. (13 seconds versus 15 seconds)

[Megalog]

Just ran the same test using AJ's method, and it came out to 9 seconds versus the 13 seconds I had earlier. So, it would appear he is correct that in a large dataset there is a more efficient method.

Values inserted using a dummy table named 'Extra':

SELECT "Blue" FROM Extra 
UNION
SELECT "Purple" FROM Extra
UNION
SELECT DISTINCT Column1 FROM Tbl1;

[NeoPa]

Maybe my testing wasn't significant then.

I have a couple of comments/questions though :

1. What is the significance of your first set of SQL where each record is returned for the colours Blue & Purple? I see no relevance to that at all and can't imagine a scenario where it may be required.
2. Did you close and re-open the database between running your sets of SQL? I know when I tried rerunning mine it executed in a flash.
3. I guess from what you've posted the small table version must be considered as (at least theoritically) quicker, even if on some occasions (especially when Access optimises the access) this difference is minimal.

[Megalog]

@NeoPa
Not sure what you mean.. "Blue" & "Purple" are only returned once, and then all the distinct values from Tbl1 follow. I'm doing the same thing as before, just adding in another value to try to stress test the scenario a bit more.

BTW, I do this in my db's.. I sometimes have 2 or 3 values I add in on a combo box which trigger actions when selected.

Did you close and re-open the database between running your sets of SQL? I know when I tried rerunning mine it executed in a flash.

Yep I did. I'm guessing the difference was noticeable since I was running it through a little over a million records.

I guess from what you've posted the small table version must be considered as (at least theoritically) quicker, even if on some occasions (especially when Access optimises the access) this difference is minimal.

To be honest, I'm still confused as to why there's a difference at all between these two setups. One requires a blank table that no actual values are stored in, and the other requires no table at all.. both values are aliased into the entire join.

[NeoPa]

@Megalog
I'm referring to the first set of SQL in post #10. This doesn't match your description. This returns a million Blues, a million Purples and a million other records from my reading of it.
@Megalog
I didn't follow the meaning of this. I wasn't aware anything referred to an empty table. I thought we were dealing with two scenarios :

1. Linking in to a table with a single record.
2. Linking in to the same table as used in the following part, but using the DISTINCT predicate to ensure only one record returned.

I think we're somehow talking at cross-purposes here, but I can't see where that started.

[Megalog]

@NeoPa
It would return a million of each if it were using "UNION ALL". Basically I tried it two ways: One way has SELECT paired with UNION, the second has SELECT DISTINCT paired with UNION ALL. The last SELECT statement (the one pulling the actual values from the main large table) is a DISTINCT in both cases.

I didn't follow the meaning of this. I wasn't aware anything referred to an empty table. I thought we were dealing with two scenarios :

1. Linking in to a table with a single record.
2. Linking in to the same table as used in the following part, but using the DISTINCT predicate to ensure only one record returned.

I think we're somehow talking at cross-purposes here, but I can't see where that started.

Look up at AJ's first post, he says: "First, I created a dummy table called DUAL (it's an oracle thing), that has only one column and no values in it."

I think where the confusion started is that I started using different SQL arrangments, and then applied both arrangements to both solutions, coming up with 4 different tests. And i only posted 3 of those tests. =)

[NeoPa]

@Megalog
Well, I've learned something. I always assumed that would only lose values that were similar but from different record sources. I never realised it had the effect of the DISTINCT predicate within the individual SELECT queries as well.
@Megalog
I misread that sort of. I just reread it and what you say makes perfect sense. I understood it (because of my previous thinking of what was required anyway) to mean it had no value (Blank or Null) in the one field in its one record, rather than no actual records as such (as you interpreted it by the looks of it). I cannot say that my interpretation is/was correct now I read it again, but perhaps it may have been. I wouldn't expect the process to work without a record to provide the data to be passed as the literal, but I will test that in case it does.
@Megalog
I must take some responsibilty for not knowing some things about SQL that someone of my experience should ;)

[NeoPa]

I think AJ must have meant a record with a Null value rather than no records at all - as an empty table produces no literal value in the output.

Phew. Something I was right about at last. I was forgetting the feeling ;)