Grouping/Deleting Dates/Times

A

Antney

Hi,

Can anyone help me with this? As you can see below, I have class schedules,
which lists days and times but it repeats each time for each day, which are
the same. #1 shows the days the class is available and the times. #2 shows
just the days the class is available. #3 shows just the times the class is
available. I've figured out a way to pull out only the days from #1 but now I
need to pull out the times and if they repeat, don't give me the duplicates.
Can anyone help!?

If this doesn't make sense, I can explain further.

Any help would be greatly appreciated.

Thanks!

#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
 
J

John W. Vinson

Hi,

Can anyone help me with this? As you can see below, I have class schedules,
which lists days and times but it repeats each time for each day, which are
the same. #1 shows the days the class is available and the times. #2 shows
just the days the class is available. #3 shows just the times the class is
available. I've figured out a way to pull out only the days from #1 but now I
need to pull out the times and if they repeat, don't give me the duplicates.
Can anyone help!?

If this doesn't make sense, I can explain further.

Any help would be greatly appreciated.

Thanks!

#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
Click to expand...

It doesn't, to me.

What are the fieldnames and datatypes of your tables? Do you have one big text
string for all the times, or is each time in a field of its own, or what?
Either structure would be improperly normalized, but would need different
techniques to extract the data.
 
A

Antney

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

I would like to pull out the times without the duplicates.

Can anyone help?!

Thanks!
 
A

Antney

Please see my new post.

Thank you!

John W. Vinson said:
It doesn't, to me.

What are the fieldnames and datatypes of your tables? Do you have one big text
string for all the times, or is each time in a field of its own, or what?
Either structure would be improperly normalized, but would need different
techniques to extract the data.
Click to expand...
 
K

KARL DEWEY

You did not respond to John's request not mine for how your data is stored.
Did you not understand what was asked for you to provide?

I try again. The information you posted that represents you classes is
stored somewhere in some format.

Where is that and what format? Word file, Text files, CSV files Excel file
or on a piece of paper?

--
Build a little, test a little.


Antney said:
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

I would like to pull out the times without the duplicates.

Can anyone help?!

Thanks!



KARL DEWEY said:
Post your table and field names with datatype if this is an Access question.
Click to expand...
Click to expand...
 
J

John W. Vinson

Please see my new post.
Click to expand...

I did, and I agree with Karl: it does not address the questions we asked.

Again:

Please tell us the structure of your table (fieldnames, datatypes, and
content). Are the times just text strings all in one big text or memo field,
or are they separate fields, or are they even in an Access table at all? You
haven't said.
 
A

Antney

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, which I listed before but it's still
not working.

Do either one of you know how I can get rid of the duplicate times?

If you need more data or information, let me know.

Thank you.
 
A

Antney

I don't want you guys to get confused, the example of data I listed is not
the same data as the example of the result of the code. I just wanted you
guys to see examples of each but you can see, in the example of the result of
the code, that times are repeated.

Thanks again.

Antney said:
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, which I listed before but it's still
not working.

Do either one of you know how I can get rid of the duplicate times?

If you need more data or information, let me know.

Thank you.

John W. Vinson said:
I did, and I agree with Karl: it does not address the questions we asked.

Again:

Please tell us the structure of your table (fieldnames, datatypes, and
content). Are the times just text strings all in one big text or memo field,
or are they separate fields, or are they even in an Access table at all? You
haven't said.
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 (HELP ANYONE!) 2
Day/Time Format 4
Finding data between blank rows 2
comparing times in excel 1
Schedule Worksheet help please 2
overtime formula based on dynamic date range? 2
report with fixed labels/times slots 6
Current Date in Query 12

Top