reverse crosstab

G

Giz

Hi,

Does Access have the capability to "reverse" crosstab? I have a table with
month (from jan to dec) listed vertically in the first column, and the day of
the month (1 - 31) listed horizontally in the first row of the table. (The
upper left cell in the table is blank). All the intersecting cells of this
month column and day row are temperatures. What I want is to have a table
with 2 vertical fields: the date and the temperature. Any way to do this in
Access?
 
M

MGFoster

Giz said:
Hi,

Does Access have the capability to "reverse" crosstab? I have a table with
month (from jan to dec) listed vertically in the first column, and the day of
the month (1 - 31) listed horizontally in the first row of the table. (The
upper left cell in the table is blank). All the intersecting cells of this
month column and day row are temperatures. What I want is to have a table
with 2 vertical fields: the date and the temperature. Any way to do this in
Access?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Union query. E.g.:

SELECT DateSerial(2005, 1, Day_Column) As Temp_date,
[Jan] As Temp
FROM Temperatures
WHERE [Jan] IS NOT NULL

UNION

SELECT DateSerial(2005, 2, Day_Column) As Temp_date,
[Feb] As Temp
FROM Temperatures
WHERE [Feb] IS NOT NULL

UNION

SELECT DateSerial(2005, 3, Day_Column) As Temp_date,
[Mar] As Temp
FROM Temperatures
WHERE [Mar] IS NOT NULL

.... etc. for 12 months ..

The Day_Column is the column that holds the day of the month.

Note the change of the month number in the DateSerial() function (2nd
parameter) and in the name of the column for the Temp aliased column and
in the WHERE clause. Repeat those changes for each month.

Change the Year to suit your needs (I assumed that the table only holds
data for 1 year, 2005).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxTB8oechKqOuFEgEQLW4QCeM2VzycLQ291FVkKGNL3gw1WVa4gAnRGZ
EnNI3eUtiIZtWGQc3bOfXpCa
=RUNL
-----END PGP SIGNATURE-----
 
G

Giz

I guess maybe I should be more clear with the way my data is structured. All
I have is a table imported from excel that is structured the way I described
in my first message. The days of the month are in row, so I don't have a
"Day_Column", and the months are in the first column. The table looks
something like this:

1 2 3 4 5 6 7 8 (days of
month)
jan 20 23 24 32 12 14 28 29 (temperatures)
feb 29 25 12 39 28 19 41 36

What I want is a table that looks like this:

date temp
jan 1 20
jan 2 23
jan 3 24

etc.

I see what your'e getting at with the union, but my table is not a
"crosstab" table format in the Access sense, so I don't think that the days
are in a "column", i.e the Day_Column. So, I don't think the Union query
works as is. How would I designate my days in a row as a column?? Any more
help is appreciated..

MGFoster said:
Giz said:
Hi,

Does Access have the capability to "reverse" crosstab? I have a table with
month (from jan to dec) listed vertically in the first column, and the day of
the month (1 - 31) listed horizontally in the first row of the table. (The
upper left cell in the table is blank). All the intersecting cells of this
month column and day row are temperatures. What I want is to have a table
with 2 vertical fields: the date and the temperature. Any way to do this in
Access?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Union query. E.g.:

SELECT DateSerial(2005, 1, Day_Column) As Temp_date,
[Jan] As Temp
FROM Temperatures
WHERE [Jan] IS NOT NULL

UNION

SELECT DateSerial(2005, 2, Day_Column) As Temp_date,
[Feb] As Temp
FROM Temperatures
WHERE [Feb] IS NOT NULL

UNION

SELECT DateSerial(2005, 3, Day_Column) As Temp_date,
[Mar] As Temp
FROM Temperatures
WHERE [Mar] IS NOT NULL

.... etc. for 12 months ..

The Day_Column is the column that holds the day of the month.

Note the change of the month number in the DateSerial() function (2nd
parameter) and in the name of the column for the Temp aliased column and
in the WHERE clause. Repeat those changes for each month.

Change the Year to suit your needs (I assumed that the table only holds
data for 1 year, 2005).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxTB8oechKqOuFEgEQLW4QCeM2VzycLQ291FVkKGNL3gw1WVa4gAnRGZ
EnNI3eUtiIZtWGQc3bOfXpCa
=RUNL
-----END PGP SIGNATURE-----
Click to expand...
 
G

Giz

let me post that table again without the comments:

1 2 3 4 5 6 7 8
jan 20 23 24 32 12 14 28 29
feb 29 25 12 39 28 19 41 36

the upper left cell is empty, and when this table is imported into Access
all columns are simply called "field 1", "field 2", etc....

thanx again
Giz said:
I guess maybe I should be more clear with the way my data is structured. All
I have is a table imported from excel that is structured the way I described
in my first message. The days of the month are in row, so I don't have a
"Day_Column", and the months are in the first column. The table looks
something like this:

1 2 3 4 5 6 7 8 (days of
month)
jan 20 23 24 32 12 14 28 29 (temperatures)
feb 29 25 12 39 28 19 41 36

What I want is a table that looks like this:

date temp
jan 1 20
jan 2 23
jan 3 24

etc.

I see what your'e getting at with the union, but my table is not a
"crosstab" table format in the Access sense, so I don't think that the days
are in a "column", i.e the Day_Column. So, I don't think the Union query
works as is. How would I designate my days in a row as a column?? Any more
help is appreciated..

MGFoster said:
Giz said:
Hi,

Does Access have the capability to "reverse" crosstab? I have a table with
month (from jan to dec) listed vertically in the first column, and the day of
the month (1 - 31) listed horizontally in the first row of the table. (The
upper left cell in the table is blank). All the intersecting cells of this
month column and day row are temperatures. What I want is to have a table
with 2 vertical fields: the date and the temperature. Any way to do this in
Access?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Union query. E.g.:

SELECT DateSerial(2005, 1, Day_Column) As Temp_date,
[Jan] As Temp
FROM Temperatures
WHERE [Jan] IS NOT NULL

UNION

SELECT DateSerial(2005, 2, Day_Column) As Temp_date,
[Feb] As Temp
FROM Temperatures
WHERE [Feb] IS NOT NULL

UNION

SELECT DateSerial(2005, 3, Day_Column) As Temp_date,
[Mar] As Temp
FROM Temperatures
WHERE [Mar] IS NOT NULL

.... etc. for 12 months ..

The Day_Column is the column that holds the day of the month.

Note the change of the month number in the DateSerial() function (2nd
parameter) and in the name of the column for the Temp aliased column and
in the WHERE clause. Repeat those changes for each month.

Change the Year to suit your needs (I assumed that the table only holds
data for 1 year, 2005).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxTB8oechKqOuFEgEQLW4QCeM2VzycLQ291FVkKGNL3gw1WVa4gAnRGZ
EnNI3eUtiIZtWGQc3bOfXpCa
=RUNL
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
D

Duane Hookom

Select CVDate([field1] & " 1, 2005") As TheDate, Field2 as Temperature
FROM tblImported
UNION ALL
Select CVDate([field1] & " 2, 2005") , Field3
FROM tblImported
UNION ALL
Select CVDate([field1] & " 3, 2005") , Field4
FROM tblImported
UNION ALL
Select CVDate([field1] & " 4, 2005") , Field5
FROM tblImported
UNION ALL
--etc--
Select CVDate([field1] & " 31, 2005") , Field32
FROM tblImported;


--
Duane Hookom
MS Access MVP
--

Giz said:
let me post that table again without the comments:

1 2 3 4 5 6 7 8
jan 20 23 24 32 12 14 28 29
feb 29 25 12 39 28 19 41 36

the upper left cell is empty, and when this table is imported into Access
all columns are simply called "field 1", "field 2", etc....

thanx again
Giz said:
I guess maybe I should be more clear with the way my data is structured.
All
I have is a table imported from excel that is structured the way I
described
in my first message. The days of the month are in row, so I don't have a
"Day_Column", and the months are in the first column. The table looks
something like this:

1 2 3 4 5 6 7 8 (days of
month)
jan 20 23 24 32 12 14 28 29 (temperatures)
feb 29 25 12 39 28 19 41 36

What I want is a table that looks like this:

date temp
jan 1 20
jan 2 23
jan 3 24

etc.

I see what your'e getting at with the union, but my table is not a
"crosstab" table format in the Access sense, so I don't think that the
days
are in a "column", i.e the Day_Column. So, I don't think the Union query
works as is. How would I designate my days in a row as a column?? Any
more
help is appreciated..

MGFoster said:
Giz wrote:
Hi,

Does Access have the capability to "reverse" crosstab? I have a table
with
month (from jan to dec) listed vertically in the first column, and
the day of
the month (1 - 31) listed horizontally in the first row of the table.
(The
upper left cell in the table is blank). All the intersecting cells of
this
month column and day row are temperatures. What I want is to have a
table
with 2 vertical fields: the date and the temperature. Any way to do
this in
Access?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Union query. E.g.:

SELECT DateSerial(2005, 1, Day_Column) As Temp_date,
[Jan] As Temp
FROM Temperatures
WHERE [Jan] IS NOT NULL

UNION

SELECT DateSerial(2005, 2, Day_Column) As Temp_date,
[Feb] As Temp
FROM Temperatures
WHERE [Feb] IS NOT NULL

UNION

SELECT DateSerial(2005, 3, Day_Column) As Temp_date,
[Mar] As Temp
FROM Temperatures
WHERE [Mar] IS NOT NULL

.... etc. for 12 months ..

The Day_Column is the column that holds the day of the month.

Note the change of the month number in the DateSerial() function (2nd
parameter) and in the name of the column for the Temp aliased column
and
in the WHERE clause. Repeat those changes for each month.

Change the Year to suit your needs (I assumed that the table only holds
data for 1 year, 2005).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxTB8oechKqOuFEgEQLW4QCeM2VzycLQ291FVkKGNL3gw1WVa4gAnRGZ
EnNI3eUtiIZtWGQc3bOfXpCa
=RUNL
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
M

MGFoster

Giz said:
let me post that table again without the comments:

1 2 3 4 5 6 7 8
jan 20 23 24 32 12 14 28 29
feb 29 25 12 39 28 19 41 36

the upper left cell is empty, and when this table is imported into Access
all columns are simply called "field 1", "field 2", etc....
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Same thing applies, I just had the columns/rows switched. The query
would look like this:

SELECT DateSerial(2005, 1, 1) As Temp_date, Field2 As Temp
FROM Temperatures
WHERE Field1 = 'jan'

UNION

SELECT DateSerial(2005, 1, 2) As Temp_date, Field3 As Temp
FROM Temperatures
WHERE Field1 = 'jan'

UNION

SELECT DateSerial(2005, 1, 3) As Temp_date, Field4 As Temp
FROM Temperatures
WHERE Field1 = 'jan'

.... etc. for each day of month ...

and then repeat for each month:

SELECT DateSerial(2005, 2, 1) As Temp_date, Field2 As Temp
FROM Temperatures
WHERE Field1 = 'feb'

UNION

SELECT DateSerial(2005, 2, 2) As Temp_date, Field3 As Temp
FROM Temperatures
WHERE Field1 = 'feb'

UNION

.... etc. ...
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxTbZYechKqOuFEgEQKvDwCfYwduJbyhL2idSWEEFKJ+bOSOZWUAoPoY
u4hQBmrq1Dz6hFStzSs6SK8y
=fjHi
-----END PGP SIGNATURE-----
 
G

Giz

It looks like this will work, Thanx a lot!!!

By the way, is there a function to make the date in format yyyymmdd instead
of dd/mm/yyyy??
 
J

John Vinson

By the way, is there a function to make the date in format yyyymmdd instead
of dd/mm/yyyy??
Click to expand...

The date is actually *stored* as a double float number, a count of
days and fractions of a day since midnight, December 30, 1899. You can
set its format (in the table design window, or on a Form or Report) to
anything you like, "yyyymmdd" being just one option. It will sort
chronologically and search properly regardless of how you have it
formatted.

John W. Vinson[MVP]
 
Top