If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Different Data Formats- Rows Vs. Columns
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#2
|
|||
|
|||
Different Data Formats- Rows Vs. Columns
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. -- Duane Hookom Microsoft Access MVP "TXgirl via AccessMonster.com" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 . |
#3
|
|||
|
|||
Different Data Formats- Rows Vs. Columns
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.. Duane Hookom wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#4
|
|||
|
|||
Different Data Formats- Rows Vs. Columns
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. "TXgirl via AccessMonster.com" wrote: 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.. Duane Hookom wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 . |
#5
|
|||
|
|||
Different Data Formats- Rows Vs. Columns
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. KARL DEWEY wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#6
|
|||
|
|||
Different Data Formats- Rows Vs. Columns
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. "TXgirl via AccessMonster.com" wrote: 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. KARL DEWEY wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 . |
Thread Tools | |
Display Modes | |
|
|