spliting one column into multiple based on site

[Giz]

Hi,

I have a table that has three columns: Site, temperature, and date. There
are 7 different sites and multiple records per unique site for any given
date. What I want is to convert this table into a table with 8 columns: 1
date column and 7 Site columns, with temperature the value listed under each
site column for that given date. The reason for this is so I can then take
into excel and easily analzye date useing functions for summary and graphing
purposes. Any help appreciated.

thanx

 

[John Spencer]

That should be doable with a cross-tab query. The cross-tab wizard query
should work you through this.

I think that the wizard will end up creating a query that looks something
like the following

TRANSFORM First([Temperature])
SELECT [Date]
FROM YourTable
GROUP BY [Date]
PIVOT [Site];

This assumes that you have one temperature per site per date. If you have
multiples temperatures for a site on the same date, then you need to decide
what you want to show.

 

[Giz]

I want to show all the temperature data for a given date, there is more than
one temperature for a given date, per site. my result table would look
something like this:

date site1 site2 site3 etc.
7/15 70 76 78
7/15 72 76 77
etc.

That should be doable with a cross-tab query. The cross-tab wizard query
should work you through this.

I think that the wizard will end up creating a query that looks something
like the following

TRANSFORM First([Temperature])
SELECT [Date]
FROM YourTable
GROUP BY [Date]
PIVOT [Site];

This assumes that you have one temperature per site per date. If you have
multiples temperatures for a site on the same date, then you need to decide
what you want to show.

Hi,

I have a table that has three columns: Site, temperature, and date. There
are 7 different sites and multiple records per unique site for any given
date. What I want is to convert this table into a table with 8 columns: 1
date column and 7 Site columns, with temperature the value listed under
each
site column for that given date. The reason for this is so I can then take
into excel and easily analzye date useing functions for summary and
graphing
purposes. Any help appreciated.

thanx

 

[John Spencer]

What does your source table look like?

Do you have something like:
Date : Site : Temp
7/15 : 1 : 70
7/15 : 1 : 73
7/15 : 1 : 72
7/15 : 2 : 73
7/15 : 2 : 75

If so, please fully describe each of the fields (type, contents).

For instance, if these were hourly readings then the rows could be built
with the Date and hour as the first column.

I want to show all the temperature data for a given date, there is more
than
one temperature for a given date, per site. my result table would look
something like this:

date site1 site2 site3 etc.
7/15 70 76 78
7/15 72 76 77
etc.

That should be doable with a cross-tab query. The cross-tab wizard query
should work you through this.

I think that the wizard will end up creating a query that looks something
like the following

TRANSFORM First([Temperature])
SELECT [Date]
FROM YourTable
GROUP BY [Date]
PIVOT [Site];

This assumes that you have one temperature per site per date. If you
have
multiples temperatures for a site on the same date, then you need to
decide
what you want to show.

Hi,

I have a table that has three columns: Site, temperature, and date.
There
are 7 different sites and multiple records per unique site for any
given
date. What I want is to convert this table into a table with 8 columns:
1
date column and 7 Site columns, with temperature the value listed under
each
site column for that given date. The reason for this is so I can then
take
into excel and easily analzye date useing functions for summary and
graphing
purposes. Any help appreciated.

thanx

 

[Giz]

yes, my source table looks just like that. The Date field is just a date,
i.e. "7/15". So what I would want from your example would be a table that
looks like this:

date : Site1 : Site2
7/15 : 70 : 73
7/15 : 73 : 75
7/15 : 72 :

thanx

What does your source table look like?

Do you have something like:
Date : Site : Temp
7/15 : 1 : 70
7/15 : 1 : 73
7/15 : 1 : 72
7/15 : 2 : 73
7/15 : 2 : 75

If so, please fully describe each of the fields (type, contents).

For instance, if these were hourly readings then the rows could be built
with the Date and hour as the first column.

I want to show all the temperature data for a given date, there is more
than
one temperature for a given date, per site. my result table would look
something like this:

date site1 site2 site3 etc.
7/15 70 76 78
7/15 72 76 77
etc.

That should be doable with a cross-tab query. The cross-tab wizard query
should work you through this.

I think that the wizard will end up creating a query that looks something
like the following

TRANSFORM First([Temperature])
SELECT [Date]
FROM YourTable
GROUP BY [Date]
PIVOT [Site];

This assumes that you have one temperature per site per date. If you
have
multiples temperatures for a site on the same date, then you need to
decide
what you want to show.

Hi,

I have a table that has three columns: Site, temperature, and date.
There
are 7 different sites and multiple records per unique site for any
given
date. What I want is to convert this table into a table with 8 columns:
1
date column and 7 Site columns, with temperature the value listed under
each
site column for that given date. The reason for this is so I can then
take
into excel and easily analzye date useing functions for summary and
graphing
purposes. Any help appreciated.

thanx

 

[John Spencer]

Quibble: A data field contains a FULL date and often a time so 7/15 is not
stored in the table, but a value that means 7/15/2005 (or some other year).

That aside, I'm not sure how to handle this problem. I think that some kind
of ranking query would have to be used to assign a sequence number to each
record for each site and date combination. You might also be able to
accomplish this with some VBA.

Unfortunately, I can't devote time to solve this. Perhaps someone else can
help - Tom Ellison is very good at these types of puzzles. I have to bang
around the room and sleep on them. Then I come up with a possible solution
and have to play with it.

My apoligies on leaving you stuck. If I come up with an idea, I will post
back.

yes, my source table looks just like that. The Date field is just a date,
i.e. "7/15". So what I would want from your example would be a table that
looks like this:

date : Site1 : Site2
7/15 : 70 : 73
7/15 : 73 : 75
7/15 : 72 :

thanx

What does your source table look like?

Do you have something like:
Date : Site : Temp
7/15 : 1 : 70
7/15 : 1 : 73
7/15 : 1 : 72
7/15 : 2 : 73
7/15 : 2 : 75

If so, please fully describe each of the fields (type, contents).

For instance, if these were hourly readings then the rows could be built
with the Date and hour as the first column.

I want to show all the temperature data for a given date, there is more
than
one temperature for a given date, per site. my result table would look
something like this:

date site1 site2 site3 etc.
7/15 70 76 78
7/15 72 76 77
etc.

:

That should be doable with a cross-tab query. The cross-tab wizard
query
should work you through this.

I think that the wizard will end up creating a query that looks
something
like the following

TRANSFORM First([Temperature])
SELECT [Date]
FROM YourTable
GROUP BY [Date]
PIVOT [Site];

This assumes that you have one temperature per site per date. If you
have
multiples temperatures for a site on the same date, then you need to
decide
what you want to show.

Hi,

I have a table that has three columns: Site, temperature, and date.
There
are 7 different sites and multiple records per unique site for any
given
date. What I want is to convert this table into a table with 8
columns:
1
date column and 7 Site columns, with temperature the value listed
under
each
site column for that given date. The reason for this is so I can
then
take
into excel and easily analzye date useing functions for summary and
graphing
purposes. Any help appreciated.

thanx