Grouping/Deleting Dates/Times (HELP ANYONE!)

A

Antney

Hi,

I originally posted this a couple of weeks ago but no one has been able to
help out on it. I am posting one more time to see if I can get an Access guru
to help out.

It is an Access database that I am using. Here is the table structure with
some data:
(the data is in the same order as the field names & data types)

Field Name Data Type
SectionTeacherDaysId AutoNumber
SectionTeacherId Number
DayofWeek Number
StartTime Date/Time
EndTime Date/Time

6 3306 Wednesday 3:30 PM 5:30 PM
7 3312 Monday 9:00 AM 12:00 PM
8 3565 Monday 9:00 AM 11:00 AM
9 3541 Monday 9:00 AM 11:00 AM
10 3540 Monday 9:00 AM 11:00 AM
11 3565 Friday 9:00 AM 11:00 AM
12 3541 Friday 9:00 AM 11:00 AM
13 3540 Friday 9:00 AM 11:00 AM
14 3282 Tuesday 10:00 AM 12:30 PM
15 2912 Tuesday 10:00 AM 12:30 PM

Here is the code that concatenates the data:

Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ""
sDay = ""
DaysTimes = ""

If IsNull(SId) Then Exit Function

Set Days = New ADODB.Recordset
Days.ActiveConnection = CurrentProject.Connection

QStr = "SELECT SectionDays.[SectionTeacherId], SectionDays.DayofWeek,
SectionDays.StartTime, SectionDays.EndTime " & _
"FROM SectionDays " & _
"WHERE ((SectionDays.[SectionTeacherId])=" & SId & ") " & _
"ORDER BY SectionDays.DayofWeek, SectionDays.StartTime"
Days.OPEN QStr, , adOpenStatic, adLockReadOnly, adCmdText
If Days.EOF Then
Days.Close
Set Days = Nothing
Exit Function
End If

Dim D As String
Dim PrevD As String
Dim T As String
Dim PrevT As String
PrevD = ""
PrevT = ""
Days.MoveFirst
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then sDay = sDay & D
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then sTime = sTime & IIf(PrevT <> "", ", ", "") & T
PrevD = D
PrevT = T
Days.MoveNext
Wend

Days.Close
Set Days = Nothing

DaysTimes = sDay & ", " & sTime
End Function

Here is the result of the code:

DayAndTime
MTuWThF, 8:15a-11:15a
MTuWThF, 11:30a-1:30p
MTuWThF, 8:30a-3:00p
MTuWThF, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p,
9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p
MTuWF, 10:30a-1:00p, 1:30p-5:00p, 10:30a-1:00p, 1:30p-5:00p, 10:30a-1:00p,
1:30p-5:00p, 10:30a-1:00p, 1:30p-5:00p

As you can see, it lists each days' time and a lot of them repeat. I don't
want/need to see each time, Mon. thru Fri., if each days' time is the same.
I've tried splitting them up in a query but it's still not working.

Here is the query & data for the query:

Query:
For #1, here is the code I used in my Access query to pull out the days:
Day(s): IIf([DayAndTime]=" ","Days Not
Scheduled",Left([DayAndTime],InStr([DayAndTime],",")-1))
Results are #2

For #1, Here is the code I used to pull out the times: Time(s):
IIf([DayAndTime]=" ","Times Not
Scheduled",Mid([DayAndTime],InStr([DayAndTime],",")+2))
Results are #3

Data:
#1. MTuWThF, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p,
9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p

#2. MTuWThF

#3. 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p

If ANYONE can help out with this, it would be greatly appreciated!!!

Thank you!!!
 
K

KARL DEWEY

I do not see and do not remember you ever saying what you want the output to
be.
Does this crosstab come close?
TRANSFORM Min([StartTime] & " - " & [EndTime]) AS Expr1
SELECT Antney.[SectionTeacherId], Antney.[DayofWeek]
FROM Antney
GROUP BY Antney.[SectionTeacherId], Antney.[DayofWeek]
PIVOT Antney.[StartTime];

--
Build a little, test a little.


Antney said:
Hi,

I originally posted this a couple of weeks ago but no one has been able to
help out on it. I am posting one more time to see if I can get an Access guru
to help out.

It is an Access database that I am using. Here is the table structure with
some data:
(the data is in the same order as the field names & data types)

Field Name Data Type
SectionTeacherDaysId AutoNumber
SectionTeacherId Number
DayofWeek Number
StartTime Date/Time
EndTime Date/Time

6 3306 Wednesday 3:30 PM 5:30 PM
7 3312 Monday 9:00 AM 12:00 PM
8 3565 Monday 9:00 AM 11:00 AM
9 3541 Monday 9:00 AM 11:00 AM
10 3540 Monday 9:00 AM 11:00 AM
11 3565 Friday 9:00 AM 11:00 AM
12 3541 Friday 9:00 AM 11:00 AM
13 3540 Friday 9:00 AM 11:00 AM
14 3282 Tuesday 10:00 AM 12:30 PM
15 2912 Tuesday 10:00 AM 12:30 PM

Here is the code that concatenates the data:

Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ""
sDay = ""
DaysTimes = ""

If IsNull(SId) Then Exit Function

Set Days = New ADODB.Recordset
Days.ActiveConnection = CurrentProject.Connection

QStr = "SELECT SectionDays.[SectionTeacherId], SectionDays.DayofWeek,
SectionDays.StartTime, SectionDays.EndTime " & _
"FROM SectionDays " & _
"WHERE ((SectionDays.[SectionTeacherId])=" & SId & ") " & _
"ORDER BY SectionDays.DayofWeek, SectionDays.StartTime"
Days.OPEN QStr, , adOpenStatic, adLockReadOnly, adCmdText
If Days.EOF Then
Days.Close
Set Days = Nothing
Exit Function
End If

Dim D As String
Dim PrevD As String
Dim T As String
Dim PrevT As String
PrevD = ""
PrevT = ""
Days.MoveFirst
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then sDay = sDay & D
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then sTime = sTime & IIf(PrevT <> "", ", ", "") & T
PrevD = D
PrevT = T
Days.MoveNext
Wend

Days.Close
Set Days = Nothing

DaysTimes = sDay & ", " & sTime
End Function

Here is the result of the code:

DayAndTime
MTuWThF, 8:15a-11:15a
MTuWThF, 11:30a-1:30p
MTuWThF, 8:30a-3:00p
MTuWThF, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p,
9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p
MTuWF, 10:30a-1:00p, 1:30p-5:00p, 10:30a-1:00p, 1:30p-5:00p, 10:30a-1:00p,
1:30p-5:00p, 10:30a-1:00p, 1:30p-5:00p

As you can see, it lists each days' time and a lot of them repeat. I don't
want/need to see each time, Mon. thru Fri., if each days' time is the same.
I've tried splitting them up in a query but it's still not working.

Here is the query & data for the query:

Query:
For #1, here is the code I used in my Access query to pull out the days:
Day(s): IIf([DayAndTime]=" ","Days Not
Scheduled",Left([DayAndTime],InStr([DayAndTime],",")-1))
Results are #2

For #1, Here is the code I used to pull out the times: Time(s):
IIf([DayAndTime]=" ","Times Not
Scheduled",Mid([DayAndTime],InStr([DayAndTime],",")+2))
Results are #3

Data:
#1. MTuWThF, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p,
9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p

#2. MTuWThF

#3. 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p

If ANYONE can help out with this, it would be greatly appreciated!!!

Thank you!!!
Click to expand...
 
A

Antney

Thanks! I will try it and if it doesn't work, I'll repost (with the format I
want to see it in). ;)

KARL DEWEY said:
I do not see and do not remember you ever saying what you want the output to
be.
Does this crosstab come close?
TRANSFORM Min([StartTime] & " - " & [EndTime]) AS Expr1
SELECT Antney.[SectionTeacherId], Antney.[DayofWeek]
FROM Antney
GROUP BY Antney.[SectionTeacherId], Antney.[DayofWeek]
PIVOT Antney.[StartTime];

--
Build a little, test a little.


Antney said:
Hi,

I originally posted this a couple of weeks ago but no one has been able to
help out on it. I am posting one more time to see if I can get an Access guru
to help out.

It is an Access database that I am using. Here is the table structure with
some data:
(the data is in the same order as the field names & data types)

Field Name Data Type
SectionTeacherDaysId AutoNumber
SectionTeacherId Number
DayofWeek Number
StartTime Date/Time
EndTime Date/Time

6 3306 Wednesday 3:30 PM 5:30 PM
7 3312 Monday 9:00 AM 12:00 PM
8 3565 Monday 9:00 AM 11:00 AM
9 3541 Monday 9:00 AM 11:00 AM
10 3540 Monday 9:00 AM 11:00 AM
11 3565 Friday 9:00 AM 11:00 AM
12 3541 Friday 9:00 AM 11:00 AM
13 3540 Friday 9:00 AM 11:00 AM
14 3282 Tuesday 10:00 AM 12:30 PM
15 2912 Tuesday 10:00 AM 12:30 PM

Here is the code that concatenates the data:

Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ""
sDay = ""
DaysTimes = ""

If IsNull(SId) Then Exit Function

Set Days = New ADODB.Recordset
Days.ActiveConnection = CurrentProject.Connection

QStr = "SELECT SectionDays.[SectionTeacherId], SectionDays.DayofWeek,
SectionDays.StartTime, SectionDays.EndTime " & _
"FROM SectionDays " & _
"WHERE ((SectionDays.[SectionTeacherId])=" & SId & ") " & _
"ORDER BY SectionDays.DayofWeek, SectionDays.StartTime"
Days.OPEN QStr, , adOpenStatic, adLockReadOnly, adCmdText
If Days.EOF Then
Days.Close
Set Days = Nothing
Exit Function
End If

Dim D As String
Dim PrevD As String
Dim T As String
Dim PrevT As String
PrevD = ""
PrevT = ""
Days.MoveFirst
While Not Days.EOF
D = DLookup("[DayAbbrev]", "DayofWeek", "[DayNo]=" &
Format(Days.Fields("DayofWeek")))
If D <> PrevD Then sDay = sDay & D
T = Format(Days.Fields("StartTime"), "h:nna/p") & "-" &
Format(Days.Fields("EndTime"), "h:nna/p")
If T <> PrevT Then sTime = sTime & IIf(PrevT <> "", ", ", "") & T
PrevD = D
PrevT = T
Days.MoveNext
Wend

Days.Close
Set Days = Nothing

DaysTimes = sDay & ", " & sTime
End Function

Here is the result of the code:

DayAndTime
MTuWThF, 8:15a-11:15a
MTuWThF, 11:30a-1:30p
MTuWThF, 8:30a-3:00p
MTuWThF, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p,
9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p
MTuWF, 10:30a-1:00p, 1:30p-5:00p, 10:30a-1:00p, 1:30p-5:00p, 10:30a-1:00p,
1:30p-5:00p, 10:30a-1:00p, 1:30p-5:00p

As you can see, it lists each days' time and a lot of them repeat. I don't
want/need to see each time, Mon. thru Fri., if each days' time is the same.
I've tried splitting them up in a query but it's still not working.

Here is the query & data for the query:

Query:
For #1, here is the code I used in my Access query to pull out the days:
Day(s): IIf([DayAndTime]=" ","Days Not
Scheduled",Left([DayAndTime],InStr([DayAndTime],",")-1))
Results are #2

For #1, Here is the code I used to pull out the times: Time(s):
IIf([DayAndTime]=" ","Times Not
Scheduled",Mid([DayAndTime],InStr([DayAndTime],",")+2))
Results are #3

Data:
#1. MTuWThF, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p,
9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p

#2. MTuWThF

#3. 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p,
12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p, 9:00a-12:00p, 12:30p-3:30p

If ANYONE can help out with this, it would be greatly appreciated!!!

Thank you!!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping/Deleting Dates/Times 8
Day/Time Format 4
Finding data between blank rows 2
Schedule Worksheet help please 2
SUMProduct working most of the time... 3
Grouping - Distinct 5
Adding times past midnight 5
Order Query By TIME HELP? 4

Top