Combining Columns into one long

[Stillpause]

I am still fairly new to Access and have looked everywhere for an answer to this. I am working with Access 2002 and am also working with a non-relational database. This is my issue:

I have a query with a date/time column and four number columns.
Example:
Date L1 L2 L3 L4
1/1/10 10:00:00 AM 100 200 300 400
1/1/10 11:00:00 AM 200 300 400 500
etc.

What I would like to do is one of two things either get a StDev of all the numbers from the four columns. I cannot add the numbers together this will give me a false Stdev. Or is there a way to combine the four columns into one long column and get a Stdev that way?

Example of Result (if combining columns):

All lanes
100
200
300
400
200
300
400
500

Any help would be greatly appreciated.

 

[boblarson]

Use a UNION query:

SELECT [Date], L1 FROM TableNameHere
UNION
SELECT [Date], L2 FROM TableNameHere
UNION
SELECT [Date], L3 FROM TableNameHere
UNION
SELECT [Date], L4 FROM TableNameHere;

And I would not use DATE for a table header (Access Reserved Word) which can cause issues in the database. See here for a list of Reserved Words.

 

[Stillpause]

Thank you very much for the help that did the trick. One other question is there a way for me to get repeat values to show up. It seems to be using an automated DISTINCT function and dropping any duplicate values.

 

[boblarson]

Instead of UNION use UNION ALL

 

[Stillpause]

Great, Thank you for all your help.