Strange results from Access Union Query

[Dale Fye]

I'm trying to combine data from two different tables, to display them in a
single list. To exacerbate the problem, I am using Sharepoint Lists as my
data source, which may be causing some of the problems.

I've had a couple of challenges.

1) if I write the query with "Null as Org_ID" in the first part of the
union, then Access is interpreting that field as a string, and returns the
Ascii value of the Org_ID in the second part of the query, rather than the
actual numeric value. To resolve this, I'm setting the of that field, and
the LO_ID in the 2nd part to zero instead of Null. This will involve a
little more coding later, so if anyone can recommend a solution for this I'd
be happy to hear it.

2) Using the query below, Access is returning 4 fields, but their column
headings are LO_ID, ID, Field1, and Field2 instead of LO_ID, Org_ID, Field1,
and Field2. Any ideas why this is occuring?

Select LO_ID, Org_ID, Field1, Field2
FROM (
SELECT tbl_Learning_Objectives.LO_ID, 0 AS Org_ID,
tbl_Learning_Objectives.Learning_Objective as Field1, "" AS Field2, Sort_Order
FROM local_Parameters INNER JOIN tbl_Learning_Objectives ON
local_Parameters.DefaultYear = tbl_Learning_Objectives.PlanYear
UNION
SELECT 0 AS LO_ID, tbl_Organizations.ID AS Org_ID,
tbl_Organizations.Org_Abbr as Field1, tbl_Organizations.Org_Name as Field2,
NZ([Lab_Sort],99)*100+NZ([Org_Sort],99) as Sort_Order
FROM tbl_Organizations) as LOs_and_Orgs
ORDER BY Sort_Order, Field1

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Clifford Bass]

Hi Dale,

That is a problem that I have hassled over several times: Union queries
returning the wrong field type due to null values. It is related to the
first select in the union. That one is the one that determines the field
types. How does the query processor decide what type "Null as Org_ID" is?
The solution, which just came to me as I read your question is this:

Create a table, maybe named tblFIELDS, with each of the different types
of fields defined. So you might have INTEGER_FIELD, TEXT_FIELD,
BOOLEAN_FIELD, DOUBLE_FIELD, CURRENCY_FIELD and so on. The table will remain
empty. As such there is no need for an index. Then use it as your initial
union item like this:

SELECT INTEGER_FIELD AS LO_ID, INTEGER_FIELD AS Org_ID, TEXT_FIELD AS
Field1, TEXT_FIELD AS Field2, INTEGER_FIELD AS Sort_Order
FROM tblFIELDS
union
SELECT tbl_Learning_Objectives.LO_ID, Null,
tbl_Learning_Objectives.Learning_Objective, null, Sort_Order
FROM local_Parameters INNER JOIN tbl_Learning_Objectives ON
local_Parameters.DefaultYear = tbl_Learning_Objectives.PlanYear
UNION
SELECT 0, tbl_Organizations.ID, tbl_Organizations.Org_Abbr,
tbl_Organizations.Org_Name, NZ([Lab_Sort],99)*100+NZ([Org_Sort],99)
FROM tbl_Organizations) as LOs_and_Orgs
ORDER BY Sort_Order, Field1

Note that while it should not matter with Access, but just to be sure,
I removed all of the ...As Field Alias... items from the succeeding selects.
They are not really needed anyway.

Let me know if that does the trick.

Clifford Bass

I'm trying to combine data from two different tables, to display them in a
single list. To exacerbate the problem, I am using Sharepoint Lists as my
data source, which may be causing some of the problems.

I've had a couple of challenges.

1) if I write the query with "Null as Org_ID" in the first part of the
union, then Access is interpreting that field as a string, and returns the
Ascii value of the Org_ID in the second part of the query, rather than the
actual numeric value. To resolve this, I'm setting the of that field, and
the LO_ID in the 2nd part to zero instead of Null. This will involve a
little more coding later, so if anyone can recommend a solution for this I'd
be happy to hear it.

2) Using the query below, Access is returning 4 fields, but their column
headings are LO_ID, ID, Field1, and Field2 instead of LO_ID, Org_ID, Field1,
and Field2. Any ideas why this is occuring?

Select LO_ID, Org_ID, Field1, Field2
FROM (
SELECT tbl_Learning_Objectives.LO_ID, 0 AS Org_ID,
tbl_Learning_Objectives.Learning_Objective as Field1, "" AS Field2, Sort_Order
FROM local_Parameters INNER JOIN tbl_Learning_Objectives ON
local_Parameters.DefaultYear = tbl_Learning_Objectives.PlanYear
UNION
SELECT 0 AS LO_ID, tbl_Organizations.ID AS Org_ID,
tbl_Organizations.Org_Abbr as Field1, tbl_Organizations.Org_Name as Field2,
NZ([Lab_Sort],99)*100+NZ([Org_Sort],99) as Sort_Order
FROM tbl_Organizations) as LOs_and_Orgs
ORDER BY Sort_Order, Field1

 

[John Spencer]

I believe another way to force the type is to use something like the following.

SELECT tbl_Learning_Objectives.LO_ID
, IIF(False,CInt(1),Null) as Org_ID,
tbl_Learning_Objectives.Learning_Objective
, IIF(False,"",null) as Field2
, Sort_Order
FROM local_Parameters INNER JOIN tbl_Learning_Objectives ON
local_Parameters.DefaultYear = tbl_Learning_Objectives.PlanYear
UNION
SELECT 0, tbl_Organizations.ID, tbl_Organizations.Org_Abbr,
tbl_Organizations.Org_Name, NZ([Lab_Sort],99)*100+NZ([Org_Sort],99)
FROM tbl_Organizations) as LOs_and_Orgs
ORDER BY Sort_Order, Field1

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Clifford Bass]

Hi John,

Nice alternative!

Clifford Bass