2006.02.27 06:11 PM

Access SQL Numeric Column UNIONs and Nulls

It's been all Access all the time around here this week, so let's just make it an even three in a row.

Today I was responding to a comment left on my earlier Access SQL TRANSFORM Statement post regarding ways to transform attributed table rows into separate, properly typed columns when I recalled an issue I ran into over a year ago with JET's inability to discern a numeric column's data type during UNION operations involving nulls. I figured I'd post it here in case anyone else finds themselves in the same situation.

Here's the setup. Imagine you have a table containing a key, some attributes, and an amount (or some other numeric value), like this:

column  type
key     long integer
project long integer
year    integer
amount  currency

key  project year    amount
1    100     2003    $0.50
2    100     2004    $1.00
3    100     2004    $1.50
4    100     2005    $2.00
5    200     2004    $3.00
6    200     2005    $4.00
7    200     2005    $4.50
8    200     2006    $5.00

Now imagine that what you want to do is organize the amounts into separate columns based on some characteristic of each amount's respective row, perhaps for a later GROUP BY and SUM, but you don't want to (or can't) use a TRANSFORM statement. One way to do this would be to select subsets of the rows in two or more filtered queries, with each query targeting a a particular column for the filtered value, and then UNION the queries together, like this:

SELECT project, amount as PrevYear, null as ThisYear FROM thetable WHERE year = year(now)-1
UNION
SELECT project, null as PrevYear, amount as ThisYear FROM thetable WHERE year = year(now)

In this example, the first query includes only rows for last year and presents their amounts in the PrevYear column, while the second query includes only rows for this year and presents their amounts in the ThisYear column. Individually these queries return the following results, respectively:



Put them together with a UNION, though, and you get jibberish:

Switch the order of the two queries around the UNION and you get different jibberish:

The problem seems to be caused by JET's needing to create in-memory resultsets for each query before stitching them together in a temporary table for the UNION, and in each query there is a column containing nothing but nulls, making it impossible for JET to determine an appropriate data type for the column. To complete the UNION, though, it has to join together the values of this untyped column with the values from the same-named column in the other query, which does have a clear data type: the type of the amount column. Somewhere during this operation something goes wrong (a pointer is mismatched or length inappropriately computed or temp table column type incorrectly specified or something) and the end result are values interpreted as some wacky Far East characters. I've seen this in Access 2002 and 2003, but don't know whether it's a problem in earlier versions. It does not appear to be a problem for text or date columns.

The solution? Well, like the doctor says, if it hurts when you do that, don't do it. Rather than use nulls for the empty columns in each query, use 0's:

SELECT project, amount as PrevYear, 0 as ThisYear FROM thetable WHERE year = year(now)-1
UNION
SELECT project, 0 as PrevYear, amount as ThisYear FROM thetable WHERE year = year(now)

Not exactly the same result, but sans the gibberish:

Alternatively, you can preserve your nulls using a single non-UNION query with a couple of in-line expressions:

SELECT 
  project, 
  IIF(year = year(now)-1, amount, null) as PrevYear, 
  IIF(year = year(now), amount, null) as ThisYear
FROM thetable
WHERE year BETWEEN year(now)-1 and year(now)

Note that the resulting Recordset is editable. You can even leave off the WHERE clause if you don't mind disregarding the nulls resulting from all the non-matching rows.

This officially concludes Access week at ewbi.develops.


Comments

Hi
I have problem in using UNION Query.
I have created 2 crosstab (Crosstab A & B) query and combine these two crosstab using Union Query.
The problem is that in the Union Query the same ID is showed twice and the sum of the data doesn't sum up.
E.g.
Company_ID / Jan / Feb
1 100 200
1 300 400
2 100 200
2 50 100

What I want is as follows:
Company_ID / Jan / Feb
1 400 600
2 150 300

Your assistance would be greatly apreciated.
Thank you.

Eny

Eny | 2006.11.28 07:10 PM

Hi Eny,

Unless I'm missing something I think you just need to go one more step.

UNION doesn't GROUP BY or SUM multiple result sets, it simply combines the unique rows appearing in the original result sets into one super set. In order to get what you want, you'll need to SELECT Company_ID, SUM(Jan), SUM(Feb) from the UNION results with a GROUP BY Company_ID. Note that you'll probably want to use UNION ALL in your combining query to avoid losing any duplicate rows.

Let me know if I've misunderstood your question. Good luck.

ewbi.develops | 2006.11.29 12:51 AM

Using the IIF command you can have a union SQL statement return NULL or a value:

SELECT SID, DisplayName,Id as UserId,iif(true,null,-1) as LocationId,iif(true,null,-1) as OrganizationId
FROM UsersExpanded
union

SELECT SID, DisplayName,iif(true,null,-1) as UserId,id as LocationId,iif(true,null,-1) as OrganizationId
FROM QryLocationsExpanded

UNION SELECT SID, DisplayName,iif(true,null,-1) as UserId,iif(true,null,-1) as LocationId,id as OrganizationId
FROM qryOrganizationsExpanded;

Govt man | 2008.08.12 05:42 AM


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d834c9ef8853ef

Listed below are links to weblogs that reference Access SQL Numeric Column UNIONs and Nulls: