converting a 3 table column into 8 column table based on Site ID.

[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 (actually temperature columns for each of the
7 sites), 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

 

[Allen Browne]

Presumably there is some character (such as a space or a comma) separating
the items in the Temperature column? If so, you could use the Split()
function to parse the items (assuming Access 2000 or later).

You already have the 7 other columns ready to accept the values. Now:
1. Create a query into this table.

2. Change it to an Update query (Update on Query menu).

3. In the Update row under the column for the first site, enter:
Split([Temperature], ",")(0)

4. In the Update row udner the column for the second site:
Split([Temperature], ",")(1)
and so on.

5. Run the query to populate the fields.

 

[John Spencer]

Answered in your posting of 12/20/05 10:30AM.

 

[Jeff Boyce]

To build on Allen's response...

You do NOT need (nor want) to create a table in Access solely to export it
to Excel. As Allen points out, you can create a query to
reorganize/reformat your data for subsequent export.

If you have:
Site
Temp
Date (by the way, "Date" is a reserved word, and will confuse Access -- use
something else)

and you have

multiple records per unique site for any given date.

how can you tell the temp/site/date records apart?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Giz]

my data currently looks like this in a 3 column table:

date site temp
7/15 1 78
7/15 1 77
7/15 2 80
7/15 2 75
etc

What I want is this:

date site1 site2
7/15 78 80
7/15 77 75

 

[Jeff Boyce]

One way to do this is to first create a series of queries, each one for a
different site, returning the DateOfMeasurement & Temp for a psecific site,
plus "empty" placeholders for the other sites.

Then create a Union query that combines all these 1-per-site queries.

Rough, untested aircode would look something like:

SELECT DateOfMeasurement, Temp As Site1Temp, Site2Temp
FROM YourTable
WHERE Site = 1
UNION
SELECT DateOfMeasurement, Temp As Site2Temp, Site1Temp
FROM YourTable
WHERE Site = 2

Regards

Jeff Boyce
<Office/Access MVP>

 

[John Vinson]

my data currently looks like this in a 3 column table:

date site temp
7/15 1 78
7/15 1 77
7/15 2 80
7/15 2 75
etc

What I want is this:

date site1 site2
7/15 78 80
7/15 77 75

Jeff's suggested query is more flexible, but you may be able to get
what you want with a simple Crosstab query, using the [Date] as the
row header and [Site] as the column header. Try the Crosstab Query
Wizard.

John W. Vinson[MVP]