An easy one I hope (Union Query)

[bidmet]

I have written a Union Query as will follow. To start with I placed the word NULL where the 0's are now, but this was causing problems. The 0's solved the problem but now obviously returns a 0 instead of a blank field. Is there any other way to do it, to keep the field blank and the query working??

Why is it only doing it for 'Sensor1'?

My UNION QUERY

SELECT [Time], [Value] AS Sensor1, 0 AS Sensor2, 0 AS Sensor3, 0 AS Sensor4, 0 AS Sensor12
FROM L0O20S1

UNION
SELECT [Time], NULL, [Value], NULL, NULL, NULL
FROM L0O20S2

UNION
SELECT [Time], NULL, NULL, [Value], NULL, NULL
FROM L0O20S3

UNION SELECT [Time], NULL, NULL, NULL, [Value], NULL
FROM L0O20S4

UNION SELECT [Time], NULL, NULL, NULL, NULL, [Value]
FROM L0O20S12
ORDER BY [Time];

 

[Norie]

Can you explain what the actual purpose of the query is?

Also can you give a summary of how your data is structured.

 

[bidmet]

I have a database which is provided by another piece of software, this database has a number of tables (L0O20S1 for example is one table). The fields in the tables are not changeable, and the data inside the tables is regularly added to.
The fields are called Time and Value.

The aim of the query is to link the values together by date/time, showing all the information from each table under one date/time where appropriate.

The query does this fine, but any Value in the first column caused a problem with the following columns if there was a NULL involved.

This was solved by putting in the 0 for the first part of the query, but gives an obvious value of 0 when ideally I need the field to be blank.

This is due to possibly having Values that could be 0 from the from the original software which I would want to show.