Different Data Formats- Rows Vs. Columns

[TXgirl via AccessMonster.com]

I have 13 yrs of tide data and precipitation data, each in a different table.
(.csv format) They are both measured hourly but rain data has the hours
labeled in columns (24 of them) and the tide table has the year in columns
(13 of them) with rows set up for date/time. Is there a way to format the
rain data to match tide data?

Thanks

 

[Duane Hookom]

You can use a normalizing union query on the rain data. Then create a
crosstab based on the union query.

If you need more assistance, come back with the table and field names and
data types.

 

[TXgirl via AccessMonster.com]

Thanks for the help! I had started comparing data manually, but I knew there
had to be a better way. The rain data is set up with the following fields:
Year (#), Date (txt), 0000(#), 100(#), 200(#), 3(#), etc through 2300(#) for
the hours

Tide Data has the following fields: Date(txt), Time(txt), Count(#), 1997(#),
1998(#) through 2010(#) for the years..

You can use a normalizing union query on the rain data. Then create a
crosstab based on the union query.

If you need more assistance, come back with the table and field names and
data types.

I have 13 yrs of tide data and precipitation data, each in a different table.
(.csv format) They are both measured hourly but rain data has the hours

[quoted text clipped - 3 lines]

Thanks

 

[KARL DEWEY]

Below is a union to move the rain data to feed a crosstab query to output as
the tide data.
qryRainData --
SELECT [Year], [Date], "0000" AS Time, [0000] AS [Count]
FROM [rain data]
WHERE [0000] Is Not Null
UNION ALL SELECT [Year], [Date], "100" AS Time, [100] AS [Count]
FROM [rain data]
WHERE [100] Is Not Null
UNION ALL SELECT [Year], [Date], "200" AS Time, [200] AS [Count]
FROM [rain data]
WHERE [200] Is Not Null
through
UNION ALL SELECT [Year], [Date], "2300" AS Time, [2300] AS [Count]
FROM [rain data]
WHERE [2300] Is Not Null;

I assumed the the measurement was in the Year field so I could not figure
what the 'Count' field was for.

TRANSFORM First(qryRainData.[Count]) AS FirstOfCount
SELECT qryRainData.[Date], qryRainData.[Time]
FROM qryRainData
GROUP BY qryRainData.[Date], qryRainData.[Time]
PIVOT [Year];


--
Build a little, test a little.

Thanks for the help! I had started comparing data manually, but I knew there
had to be a better way. The rain data is set up with the following fields:
Year (#), Date (txt), 0000(#), 100(#), 200(#), 3(#), etc through 2300(#) for
the hours

Tide Data has the following fields: Date(txt), Time(txt), Count(#), 1997(#),
1998(#) through 2010(#) for the years..

You can use a normalizing union query on the rain data. Then create a
crosstab based on the union query.

If you need more assistance, come back with the table and field names and
data types.

I have 13 yrs of tide data and precipitation data, each in a different table.
(.csv format) They are both measured hourly but rain data has the hours

[quoted text clipped - 3 lines]

Thanks

--



.

 

[TXgirl via AccessMonster.com]

Yes, the measurement is in the year field. The count is just used to show
how many data points we have per year. Your suggested approach is a bit
intimidating b/c I've never programmed in Access. -Where do I even save it
to? (This is taking my understanding of Access to a different level)

Thanks.

Below is a union to move the rain data to feed a crosstab query to output as
the tide data.
qryRainData --
SELECT [Year], [Date], "0000" AS Time, [0000] AS [Count]
FROM [rain data]
WHERE [0000] Is Not Null
UNION ALL SELECT [Year], [Date], "100" AS Time, [100] AS [Count]
FROM [rain data]
WHERE [100] Is Not Null
UNION ALL SELECT [Year], [Date], "200" AS Time, [200] AS [Count]
FROM [rain data]
WHERE [200] Is Not Null
through
UNION ALL SELECT [Year], [Date], "2300" AS Time, [2300] AS [Count]
FROM [rain data]
WHERE [2300] Is Not Null;

I assumed the the measurement was in the Year field so I could not figure
what the 'Count' field was for.

TRANSFORM First(qryRainData.[Count]) AS FirstOfCount
SELECT qryRainData.[Date], qryRainData.[Time]
FROM qryRainData
GROUP BY qryRainData.[Date], qryRainData.[Time]
PIVOT [Year];

Thanks for the help! I had started comparing data manually, but I knew there
had to be a better way. The rain data is set up with the following fields:

[quoted text clipped - 15 lines]

 

[KARL DEWEY]

What I posted was query SQL (Query Structured Language) that runs the query
behind the scenes. It is a whole lot easier than trying to describe the
contents of a query as laid out in the design view grid.

Open a new query in design view, close the table list window, and select
VIEW - SQL View from the ribbon.

Paste my post, editing it for your table and names. Remove any hard returns
that creep in due to the copying and pasting actions. The only hard returns
precede the action words in all caps.

You can name the first query as I did - qryRainData - or as you desire but
if different then you have to edit to match in the second query.


--
Build a little, test a little.

Yes, the measurement is in the year field. The count is just used to show
how many data points we have per year. Your suggested approach is a bit
intimidating b/c I've never programmed in Access. -Where do I even save it
to? (This is taking my understanding of Access to a different level)

Thanks.

Below is a union to move the rain data to feed a crosstab query to output as
the tide data.
qryRainData --
SELECT [Year], [Date], "0000" AS Time, [0000] AS [Count]
FROM [rain data]
WHERE [0000] Is Not Null
UNION ALL SELECT [Year], [Date], "100" AS Time, [100] AS [Count]
FROM [rain data]
WHERE [100] Is Not Null
UNION ALL SELECT [Year], [Date], "200" AS Time, [200] AS [Count]
FROM [rain data]
WHERE [200] Is Not Null
through
UNION ALL SELECT [Year], [Date], "2300" AS Time, [2300] AS [Count]
FROM [rain data]
WHERE [2300] Is Not Null;

I assumed the the measurement was in the Year field so I could not figure
what the 'Count' field was for.

TRANSFORM First(qryRainData.[Count]) AS FirstOfCount
SELECT qryRainData.[Date], qryRainData.[Time]
FROM qryRainData
GROUP BY qryRainData.[Date], qryRainData.[Time]
PIVOT [Year];

Thanks for the help! I had started comparing data manually, but I knew there
had to be a better way. The rain data is set up with the following fields:

[quoted text clipped - 15 lines]

Thanks

--



.