Chronological Query/Report

[Benjamin Breeg]

I would like to create a report/query that displays a "date" field from two or more columns of a table into one column on a report (sorting ascending):

I´ve this:
NAME DATE1 DATE2 DATE3
Name1 23-02-2011 30-02-2011 03-03-2011
Name2 01-01-2011 30-02-2011 02-02-2011


Name3 02-01-2011


Want this:
Name2 01-01-2011
Name3 02-01-2011
Name2 02-02-2011
Name1 23-02-2011
Name1 30-02-2011
Name2 30-02-2011
Name1 03-03-2011

Thanks

[June7]

Why is data in this structure?

One way is with UNION query. There is no wizard or designer for UNION must type in the SQL View editor of the query designer. Try:

SELECT [Name], [Date] FROM
(SELECT [Name], Date1 As [Date] FROM tablename
UNION SELECT [Name], Date2 FROM tablename
UNION SELECT [Name], Date3 FROM tablename) As Q1 ORDER BY [Date];

[Benjamin Breeg]

Great!
It solved the problem. Thanks a lot.

By the way, what do you mean by "Why is data in this structure?"
Do you think it could be otherwise?
The reason it's in that structure is because it calculate a new date from other, Exmp. Date2=Date1+30..., and because those dates are time limits for me to do something, and when passed, i often need that info to be close my main form.
Do you think i could do with another table structure?
Thanks in advanced.

[June7]

Having similar name fields of same data type is an indicator that structure is not proper, hence having to resort to UNION query, which has issues I won't get into. The result of the UNION is conventional structure for data.

Also, a principle of relational database is 'save raw data, do calcs in report'. Since Date2 is calculated with a constant of 30, this date can always be calculated as needed. Are you using the Calculated datatype field? If so, then the date is being calculated 'on the fly' and is not actually a static value.

So the next question is: Why do you need to rearrange the data?

Those dates are in dd/mm/yyyy form instead Access convention of mm/dd/yyyy. Does this cause you issues when calculating?

[Benjamin Breeg]

Having similar name fields of same data type is an indicator that structure is not proper, hence having to resort to UNION query, which has issues I won't get into. The result of the UNION is conventional structure for data.

Also, a principle of relational database is 'save raw data, do calcs in report'. Since Date2 is calculated with a constant of 30, this date can always be calculated as needed. Are you using the Calculated datatype field? If so, then the date is being calculated 'on the fly' and is not actually a static value.

So the next question is: Why do you need to rearrange the data?

Those dates are in dd/mm/yyyy form instead Access convention of mm/dd/yyyy. Does this cause you issues when calculating?

I accept that principle, but i want do visualize the calculated date field in my form, so i can pass through records and have the value calculated.

I'm from Portugal and the way we display date is dd/mm/yyyy or yyyy/mm/dd, so it's for easy of comprehension. (I believe that there is no issue with calculate.)

Meanwhile, i've just realized that i did not got the question right.
Because i also want to see in that query, the column of the date field name, so i can correspond to the displayed date (my bad)

like this:

Name2 01-01-2011 Date1
Name3 02-01-2011 Date1
Name2 02-02-2011 Date3
Name1 23-02-2011 Date1
Name1 30-02-2011 Date2
Name2 30-02-2011 Date2
Name1 03-03-2011 Date3

[June7]

Then:
SELECT * FROM
(SELECT [Name], Date1 As [Date], "Date1" As Source FROM tablename
UNION SELECT [Name], Date2, "Date2" FROM tablename
UNION SELECT [Name], Date3, "Date3" FROM tablename) As Q1 ORDER BY [Date];

[Benjamin Breeg]

It solved the problem.

Thanks!