Union Query Question

[rkl122]

I'm preparing a query as the control source for an unbound listbox. The following code gives the desired results:

SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]))
ORDER BY QrySbfShotList.CamerasFK

(My.control will be a control on the form. For the time being, I let the query prompt me for a value.)

It produces two columns like so:

CameraNum Camera ID
1 2
2 3
3 4
4 5
5 6
6 7
8 9
11 12


CameraNum is text; CameraID is numeric.

Now, I'm trying to use a trick I read about that should add a single textual entry to the top of the list like so:

SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]))
ORDER BY QrySbfShotList.CamerasFK

union

SELECT "(ALL)", "Dummy"
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]));

This produces

CameraNum Camera ID
ALL Dummy
1 2
11 12
2 3
3 4
4 5
5 6
6 7
8 9

The second (numeric) column is now out of order. This is reproducible for other values of my.control. If there are double digit entries they get inserted at the third row.

Why? What am I not understanding about how UNION works?

Thanks for any help, Ron

(BTW, I know I could put the "ALL" entry into tblCameras, thereby avoiding the need for a union, but I'd still like to know why the unexpected result.)

 

[spikepl]

Your second column is no longer numeric, becasue you forced a conversion to string by placing the string "dummy". Instead, use Null

 

[rkl122]

Thanks, it did occur to me that the second column is behaving like it's now text. But how do I represent the null? The word Null (without quotes) and "" give same result. Same thing when I try an undefined variable and leave it blank when the query prompts me for it.

Your second column is no longer numeric, becasue you forced a conversion to string by placing the string "dummy". Instead, use Null

 

[spikepl]

SELECT "(ALL)" As Allselected , Null As Dummy FROM QrySbfShotList 
union All
SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK 
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID 
WHERE (((QrySbfShotList.shootsFK)=[my].[control])) 
ORDER BY QrySbfShotList.CamerasFK

 

[rkl122]

Thx but I get an error that the orderby must refer only to fields represented by the first select. When I do that, I get strange hieroglyphic type characters in the Dummy column, but I can tell from the values in the first column that the order has not changed. -Ron

SELECT "(ALL)" As Allselected , Null As Dummy FROM QrySbfShotList 
union All
SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK 
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID 
WHERE (((QrySbfShotList.shootsFK)=[my].[control])) 
ORDER BY QrySbfShotList.CamerasFK

 

[spikepl]

order by 2

 

[rkl122]

Doesn't change the output.

order by 2

But get this: if I reverse the sequence of the two fields

SELECT DISTINCT  QrySbfShotList.CamerasFK, tblCameras.CameraNum...

then the results are ordered correctly!! It's as if having the text field first converts the numeric field to text. Is this some quirk of UNION, or a bug, or what??

EDIT - And the orderby clause is not needed at all. I really don't understand UNION, sigh.