Turning rows into columns

[BobCole]

I would like help on how to transform a table from one format to another, for a Tide Table.

The classic (and desirable) format for Tide Tables can be seen in this example, which is supposed to be viewed on one line:

March 20, 2002 - 1:23am High - 7:45am Low - 1:56pm High - 8:02pm Low

In the above example, the first high tide of the day is at 1:23 am, the last low tide is at 8:02 pm. People are used to seeing the data on one line. Please note that as often as not, the first tide of the day will be a low tide, and that on some days there are only three tides all day (and on very rare occasions, there are five).

Currently, my data is in this form, taking up four separate lines for the typical, four-tide day:

DATE - TIME - TIDE TYPE

March 20, 2002 - 1:23am - High
March 20, 2002 - 7:45am - Low
March 20, 2002 - 1:56pm - High
March 20, 2002 - 8:02pm - Low

(The data actually start out as a long date format with just two fields per line, the tide's date-and-time and the type of tide; but I can get it to this stage.)

I have been struggling for the last several weeks to learn Access, and to get my data into the classic one-line Tide Table form.

Can anyone help me? I do not know SQL, and would like to learn; but I would also like to use the Design View so I can see what I should be doing.

Thanks!

-- Hickory

 

[mdthornton]

If the problem was a bit more regular, ie every day has 4 tides, 2 low and 2 high you could do something but given the information you provided above I don't think there's an easy way to do this in SQL.

You could however write a relatively simple query like:

select tide_info: getTideInfo([tide_date])
from qTideDates

Where qTideDates returns a distinct set of dates you want tide information for.

The code for the getTideInfo function could be something like:

Public Function getTideInfo(x As Date)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("select tide_date, tide_type from Tides where Format([tide_date], 'yyyymmdd') = '" + Format(x, "yyyymmdd&quot + "' order by tide_date&quot
getTideInfo = Format(x, "mmmm dd, yyyy&quot
Do While Not rst.EOF
getTideInfo = getTideInfo + " - " + Format(rst![tide_date], "hh:mmam/pm&quot + " " + rst![tide_type]
rst.MoveNext
Loop
rst.Close
End Function

The benefit of this approach is that you can use this function anywhere in you db. Just pass it a date and you get a line of tide info back.

Don't want to be patronizing but if any of this is past your comfort level let me know if I can help further. Best Regards,
Mike

 

[paron]

I can think of a way that is harder to do, but easy to set up, and a way that is easy to do, but harder to set up. So -- is this a one-shot, or are you going to need to do this on a recurring basis? Like every month or every year? I.e., how often does the data come in? Once per lifetime or more often?

Ron

 

[BobCole]

Thanks to both Mike and Ron.

This project is for a website for a non-profit organization -- I have found that the biggest "draw" in the community is a localized tide table, and that is why I'm working on it. I have no budget to hire a programmer.

Mike's answer is fascinating, and I have saved it for reference, but I don't know how to use it or where to input it in Access. I am not even sure what it is: Visual Basic? Could you steer me to a book that would orient me? I would like to learn; my only programming background is in a now-obsolete language, years ago.

Ron, to answer your question: this is a once-a-year task, so in order to accomplish it I would prefer the method that is "harder to do, but easy to set up." But for my curiosity and education, I would also like to see what you mean by "a way that is easy to do, but harder to set up." I would like to improve my skills, but the high priority right now is getting the site up and running this spring.

Thank you very very much for your help, both of you.

-- Hickory

 

[paron]

Hickory -- haven't forgotten; just forgot some other things that I needed to do. Could you send some of the data - a couple of moons worth - to hillbiker@hotmail.com ? It will help to have a realistic sample to work on.

Ron

 

[mdthornton]

Hickory

If you want to explore my solution further I'd be happy to help you set it up. Shouldn't be too bad ;-)

As Ron says, give us some more details on your source table and we'll get you going....

Let me know. Best Regards,
Mike

 

[BobCole]

Crisis is over -- I kludged something in Excel to reformat my data. BUT please post your replies anyway if you have them; I still would be interested in seeing your solutions, as I am determined to learn more about Access.

Thank you very much.

-- Hickory

 

[paron]

Sorry, Bob, I was "winging it" until a data sample appeared! I had in mind some hand manipulation using other programs (which you already discovered independently, evidently) as a "hard to do, easy to set up" one-time or infrequent-use technique. I would have suggested VB based on Mike's code, modified to handle three-tide days, if you were doing this job more often.

Glad you found a way -- by the time the next set of data rolls in, you'll probably be writing your own code to handle it!

Ron

 

[BobCole]

Thanks! This is a great forum. I have two FINAL questions: what is the best Access book addressed to someone who is new to programming? And, ditto for Visual Basic?

I appreciate your generosity and your help. I have copied this thread and intend to use it as a tutorial for learning both Access and VB.

-- Hickory