TideTable grouped by date

[Hickory]

The classic format for Tide Tables can be seen here; it is supposed to be viewed on one line:

DATE - Tide1 Time & Tide1 Type - Tide2 Time & Tide2 Type - Tide3 Time & Tide3 Type - Tide4 Time & Tide4 Type

or for example:

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 from time to time, 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

 

[cpod]

My solution is somewhat complex (4 queries). If you would like to post your email address I will send you a sample database illustrating the solution I came up with.

 

[Rich]

Have you tried a Crosstab Query?

 

[Hickory]

Cpod -- I got your e-mail solution. Very clever, I'm still trying to understand every step, but I'm learning a lot of new functions! Thank you so much!

Rich -- I tried the crosstab query route but it doesn't seem to work for this.

Again, thank you so much!

-- Hickory