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!!!
|
|
0
|
|
|
|
Reply
|
 Utf
|
2/5/2010 8:54:07 PM |
|
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" wrote:
> 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!!!
>
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
2/5/2010 9:43:01 PM
|
|
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" wrote:
> 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" wrote:
>
> > 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!!!
> >
> >
> >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
2/6/2010 12:04:01 AM
|
|
|
2 Replies
195 Views
(page loaded in 3.082 seconds)
Similiar Articles: Removing Auto Date on Header of Table Printout (in 2007 ...... grounding of Access 2003 but wonder if anyone can help ... Is there a way I can remove this date without removing the ... Privacy Policy | All Times Are GMT(UTC) | powered ... Delete first 2 digits off a number - microsoft.public.access ...Hi can anyone help. I have a field in a table in my database ... the 9, (new format 01234 546785) and I need to remove it. ... Privacy Policy | All Times Are GMT(UTC) | powered by remove the date, leave the time - microsoft.public.excel.misc ...... Groups ... > > 1) To remove the date portion of ... Column Leaving The Date Untouched - I have a column which has dates and times 2400 format... - Free Excel Help Characters deleting forward - WHY? - microsoft.public ...... Groups About ... in an already typed line it starts deleting ahead - understand? Can anyone help ... Privacy Policy | All Times Are GMT(UTC) | ... Removing Dashes - microsoft.public.excel.miscI wanted to know if anyone knew how to remove ... with dash changes to date ... Dashes - Hi Im trying to remove dashes from a group of numbers... - Free Excel Help Date/Time stamp changes - microsoft.public.word.docmanagement ...Time stamp on incoming emails is not correct, pls help ... ... How to Remove Date & Time Track Changes | eHow.com How to ... on a file contains information pertaining to the times ... remove headers & Footers Excel 2007 - microsoft.public.excel ...Hi There I am trying to remove the headers and footers from excel document can anyone please help. ... able to print to ... save date ... Privacy Policy | All Times Are ... Dates for European Summer Time - microsoft.public.excel.misc ...Does anyone know of any formulae to compute the start ... Ron > >Many thanks, > >V Glad to help ... Summer Time Order 2002 permanently changed the dates and times ... deleting resources 2003 - microsoft.public.project.server ...It worked 2 or 3 times, deleting one name at a time, but ... Need Help with Deleting Empty Paragraphs in Word 2003 ... the rates from any file that you give to anyone ... Emails delete in Entourage but not on Exchange server (Sync issues ...... the two properly "synced" <br><br>Any help ... Sync Issues After Exchange 2010 Upgrade - Anyone Know a ... email from exchange 2007 server Entourage 2008 not deleting ... Grouping/Deleting Dates/Times (HELP ANYONE!) DataBaseHi, 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 g Google GroupsHelp; Report an issue about Google Groups; Keyboard shortcuts; Change language ... interest you, or sign in to see all of your groups. Go back to the old Google Groups Facebook Help Center | Facebook... can set a group email address to help your group ... If you're a group admin, you can remove most posts in your ... and other members can add you to groups. This way anyone ... Sort data in a range or table - Excel - Office.com... or largest to smallest), and dates and times ... Issue: Remove any leading spaces ... tab, in the Sort & Filter group, click Reapply. Issue: Check that dates and times are ... How to create and manage events - Facebook Help Center | FacebookYou can sort friends by groups or friend lists to help you find people ... A host can remove anyone from an event who was sent an ... To change the time or date of an event: Go to ... 7/21/2012 6:59:00 AM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 