Column headings in SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
I´ve pasted my SQL below. It is joining 3 queries.
My problem is that in the cases where I used Null for some columns, those
columns loose their heading. Is there a simple way to enter the heading here,
or should I just solve the problem by making a second query?
Not that that's a big problem, I'm just eager to learn...
Thanks!

SELECT [Lake-ID], [Year], [+/- (yr)], [Type], [Species], Null, Null, Null,
Null, Null, [Comment], [Reliability], [Source-ID]
FROM [Presentation - presence]
UNION SELECT [Lake-ID], [Year], [+/- (yr)], [Type], [Species], [Reason for
extinction], Null, Null, Null, Null, [Comment], [Reliability], [Source-ID]
FROM [Presentation - extinctions]
UNION SELECT [Lake-ID], [Year], [+/- (yr)], [Type], [Species], Null,
[Repeatingly], [Follow-up year], [Result - catch], [Result - reproduction],
[Comment], [Reliability], [Source-ID]
FROM [Presentation - stockings]
ORDER BY [Lake-ID], [Year], [Species];
 
Alias the field, e.g.:

SELECT [Lake-ID], [Year], [+/- (yr)], [Type], [Species],
Null AS Field1, Null AS Field2, ...

If you do this in the first SELECT statement in the query, Access is unable
to determine the data type of the field, so it will default to Text. If you
intended a number or date, the results will be nonsense. Workarounds:
a) Rearrange the SELECT statements so that the one that generates all fields
is first.

b) Fudge the data type with this kind of construct:
IIf(True, Null, 0) AS Field1
Because the True part is always True, it will always return Null, but since
the alternative is a number, Access will recognise the field as numeric.
 
Back
Top