Hope someone can help me with this. I work for a community college where we
generate a building schedule from a list of active classes in a table called
Active Class Schedule. My co-worker currently does this manually by exporting
that table to Excel and inserting the class in a spreadsheet for the number
of times the class meets. Some of our classes may only meet on a certain day
and others may meet on multiple days, such as MTW.

The fields that are used are as follows:
StartDate
EndDate
Days (which has info such as MTWRFS)

Is there a way to create a query to generate multiple line items for a
class, showing the date that the class is meeting and use the Make Table
Query to generate the Building Schedule? I'm not sure how to format my
calculated field with DateDiff (if that is the right one). I apologize if
there is a message for this already. Please direct me to it if there is. Any
help would be appreciated.

The dates have to come from somewhere, so create a counting table. It has
one field of type Number, named (say) CountID, marked as primary key. Save
the table as tblCount. Now enter a record for each number from 0 to say
4000. You can use the code in this link to create the data:
http://allenbrowne.com/ser-39.html

Now create a query using both tables. There must be no join between the 2
tables in the upper pane of query design. Type this expression into the
Field row:
TheDate: [StartDate] + CountID
In the Criteria row under this, enter:
<= [EndDate]
This query yields all dates between the starting date and the ending date.

Now you need to filter that to the right days of the week. It would be
easiest if your Days field contains a number between 1 (Sunday) and 7
(Saturday), so a class that met on Monday Tuesday and Wednesday would be
number 234.

Add the Days field to the query grid.
In the Criteria row, enter:
Like "*" & Weekday([StartDate] + CountID) & "*"
This restricts the dates to the right days of the week.

In the end, you can use this as is, or you can turn it into an Append query
(Append on Query menu) to add these dates for the class to the table you
created for this purpose.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
> Hope someone can help me with this. I work for a community college where
> we
> generate a building schedule from a list of active classes in a table
> called
> Active Class Schedule. My co-worker currently does this manually by
> exporting
> that table to Excel and inserting the class in a spreadsheet for the
> number
> of times the class meets. Some of our classes may only meet on a certain
> day
> and others may meet on multiple days, such as MTW.
>
> The fields that are used are as follows:
> StartDate
> EndDate
> Days (which has info such as MTWRFS)
>
> Is there a way to create a query to generate multiple line items for a
> class, showing the date that the class is meeting and use the Make Table
> Query to generate the Building Schedule? I'm not sure how to format my
> calculated field with DateDiff (if that is the right one). I apologize if
> there is a message for this already. Please direct me to it if there is.
> Any
> help would be appreciated.

Hi Allen,

Thank you so much for the information. I will try it and let you know. One
question - Is there a way I can update the Days field to a number (as you
suggest) and still keep the information intact? I'm trying to use what I
already have. Would I be able to specify each occurrence in the criteria row
or is that asking too much of a query? Most are pretty consistent but there
are a few exceptions.


"Allen Browne" wrote:

> The dates have to come from somewhere, so create a counting table. It has
> one field of type Number, named (say) CountID, marked as primary key. Save
> the table as tblCount. Now enter a record for each number from 0 to say
> 4000. You can use the code in this link to create the data:
> http://allenbrowne.com/ser-39.html
>
> Now create a query using both tables. There must be no join between the 2
> tables in the upper pane of query design. Type this expression into the
> Field row:
> TheDate: [StartDate] + CountID
> In the Criteria row under this, enter:
> <= [EndDate]
> This query yields all dates between the starting date and the ending date.
>
> Now you need to filter that to the right days of the week. It would be
> easiest if your Days field contains a number between 1 (Sunday) and 7
> (Saturday), so a class that met on Monday Tuesday and Wednesday would be
> number 234.
>
> Add the Days field to the query grid.
> In the Criteria row, enter:
> Like "*" & Weekday([StartDate] + CountID) & "*"
> This restricts the dates to the right days of the week.
>
> In the end, you can use this as is, or you can turn it into an Append query
> (Append on Query menu) to add these dates for the class to the table you
> created for this purpose.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
> news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
> > Hope someone can help me with this. I work for a community college where
> > we
> > generate a building schedule from a list of active classes in a table
> > called
> > Active Class Schedule. My co-worker currently does this manually by
> > exporting
> > that table to Excel and inserting the class in a spreadsheet for the
> > number
> > of times the class meets. Some of our classes may only meet on a certain
> > day
> > and others may meet on multiple days, such as MTW.
> >
> > The fields that are used are as follows:
> > StartDate
> > EndDate
> > Days (which has info such as MTWRFS)
> >
> > Is there a way to create a query to generate multiple line items for a
> > class, showing the date that the class is meeting and use the Make Table
> > Query to generate the Building Schedule? I'm not sure how to format my
> > calculated field with DateDiff (if that is the right one). I apologize if
> > there is a message for this already. Please direct me to it if there is.
> > Any
> > help would be appreciated.
>
>

Use Replace() in an update query to replace the existing characters with
numbers.

Back up the database before you run updates (just in case something you
enter something wrong.)

1. Create a query using this table.

2. Change it to an Update query (Update on query menu.)
Access adds an update row to the grid.

3. In the Update row under the Days field, enter:
Replace([Days], "M", 2)
Run the query.

4. Change the expression above to:
Replace([Days], "T", 3)
Run the query.

5. Repeat for the other day names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pauline B." <(E-Mail Removed)> wrote in message
news:A82F4CF2-D20F-4434-948C-(E-Mail Removed)...
> Hi Allen,
>
> Thank you so much for the information. I will try it and let you know. One
> question - Is there a way I can update the Days field to a number (as you
> suggest) and still keep the information intact? I'm trying to use what I
> already have. Would I be able to specify each occurrence in the criteria
> row
> or is that asking too much of a query? Most are pretty consistent but
> there
> are a few exceptions.
>
>
> "Allen Browne" wrote:
>
>> The dates have to come from somewhere, so create a counting table. It has
>> one field of type Number, named (say) CountID, marked as primary key.
>> Save
>> the table as tblCount. Now enter a record for each number from 0 to say
>> 4000. You can use the code in this link to create the data:
>> http://allenbrowne.com/ser-39.html
>>
>> Now create a query using both tables. There must be no join between the 2
>> tables in the upper pane of query design. Type this expression into the
>> Field row:
>> TheDate: [StartDate] + CountID
>> In the Criteria row under this, enter:
>> <= [EndDate]
>> This query yields all dates between the starting date and the ending
>> date.
>>
>> Now you need to filter that to the right days of the week. It would be
>> easiest if your Days field contains a number between 1 (Sunday) and 7
>> (Saturday), so a class that met on Monday Tuesday and Wednesday would be
>> number 234.
>>
>> Add the Days field to the query grid.
>> In the Criteria row, enter:
>> Like "*" & Weekday([StartDate] + CountID) & "*"
>> This restricts the dates to the right days of the week.
>>
>> In the end, you can use this as is, or you can turn it into an Append
>> query
>> (Append on Query menu) to add these dates for the class to the table you
>> created for this purpose.
>>
>> "Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
>> news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
>> > Hope someone can help me with this. I work for a community college
>> > where
>> > we
>> > generate a building schedule from a list of active classes in a table
>> > called
>> > Active Class Schedule. My co-worker currently does this manually by
>> > exporting
>> > that table to Excel and inserting the class in a spreadsheet for the
>> > number
>> > of times the class meets. Some of our classes may only meet on a
>> > certain
>> > day
>> > and others may meet on multiple days, such as MTW.
>> >
>> > The fields that are used are as follows:
>> > StartDate
>> > EndDate
>> > Days (which has info such as MTWRFS)
>> >
>> > Is there a way to create a query to generate multiple line items for a
>> > class, showing the date that the class is meeting and use the Make
>> > Table
>> > Query to generate the Building Schedule? I'm not sure how to format my
>> > calculated field with DateDiff (if that is the right one). I apologize
>> > if
>> > there is a message for this already. Please direct me to it if there
>> > is.
>> > Any
>> > help would be appreciated.

"Pauline B." wrote:
> Hope someone can help me with this. I work for a community college where
> we
> generate a building schedule from a list of active classes in a table
> called
> Active Class Schedule. My co-worker currently does this manually by
> exporting
> that table to Excel and inserting the class in a spreadsheet for the
> number
> of times the class meets. Some of our classes may only meet on a certain
> day
> and others may meet on multiple days, such as MTW.
>
> The fields that are used are as follows:
> StartDate
> EndDate
> Days (which has info such as MTWRFS)
>
> Is there a way to create a query to generate multiple line items for a
> class, showing the date that the class is meeting and use the Make Table
> Query to generate the Building Schedule? I'm not sure how to format my
> calculated field with DateDiff (if that is the right one). I apologize if
> there is a message for this already. Please direct me to it if there is.
> Any
> help would be appreciated.

Hi Pauline,

PMFBI

another method would involve a calendar table
(say "tblCal") where you use Excel to "autocomplete"
a column of dates by "dragging down" after completing
the first few dates, stopping the drag "way in the future,"
then import into Access table.

make sure your new table's field (say "CalDate") is
type Date/Time

then add 1 more (text) field to "tblCal" of length 1
(say "Dy")

then run update query

UPDATE tblCal SET tblCal.Dy =
Choose(Weekday([CalDate]),"X","M","T","W","R","F","S");

used "X" for Sunday, but you may want to change to ?...

verify your values are correct for "Dy"

just as a test i created example data

ID Class StartDate EndDate Days
1 mwf 9/1/2008 9/26/2008 MWF
2 tr 9/2/2008 9/18/2008 TR
3 mtwrf 9/1/2008 9/26/2008 MTWRF

then ran query

SELECT
ACS.Class,
ACS.StartDate,
ACS.EndDate,
ACS.Days,
C.CalDate AS ClassDate,
C.Dy
FROM
[Active Class Schedule] AS ACS, tblCal AS C
WHERE
(C.CalDate
Between
ACS.StartDate
AND
ACS.EndDate)
AND
(InStr(ACS.Days, C.Dy) > 0)
ORDER BY
ACS.Class,
C.CalDate;

and got enumeration like i *think* you wanted:

Class StartDate EndDate Days ClassDate Dy
mtwrf 9/1/2008 9/26/2008 MTWRF 9/1/2008 M
mtwrf 9/1/2008 9/26/2008 MTWRF 9/2/2008 T
mtwrf 9/1/2008 9/26/2008 MTWRF 9/3/2008 W
mtwrf 9/1/2008 9/26/2008 MTWRF 9/4/2008 R
mtwrf 9/1/2008 9/26/2008 MTWRF 9/5/2008 F
mtwrf 9/1/2008 9/26/2008 MTWRF 9/8/2008 M
mtwrf 9/1/2008 9/26/2008 MTWRF 9/9/2008 T
mtwrf 9/1/2008 9/26/2008 MTWRF 9/10/2008 W
mtwrf 9/1/2008 9/26/2008 MTWRF 9/11/2008 R
mtwrf 9/1/2008 9/26/2008 MTWRF 9/12/2008 F
mtwrf 9/1/2008 9/26/2008 MTWRF 9/15/2008 M
mtwrf 9/1/2008 9/26/2008 MTWRF 9/16/2008 T
mtwrf 9/1/2008 9/26/2008 MTWRF 9/17/2008 W
mtwrf 9/1/2008 9/26/2008 MTWRF 9/18/2008 R
mtwrf 9/1/2008 9/26/2008 MTWRF 9/19/2008 F
mtwrf 9/1/2008 9/26/2008 MTWRF 9/22/2008 M
mtwrf 9/1/2008 9/26/2008 MTWRF 9/23/2008 T
mtwrf 9/1/2008 9/26/2008 MTWRF 9/24/2008 W
mtwrf 9/1/2008 9/26/2008 MTWRF 9/25/2008 R
mtwrf 9/1/2008 9/26/2008 MTWRF 9/26/2008 F
mwf 9/1/2008 9/26/2008 MWF 9/1/2008 M
mwf 9/1/2008 9/26/2008 MWF 9/3/2008 W
mwf 9/1/2008 9/26/2008 MWF 9/5/2008 F
mwf 9/1/2008 9/26/2008 MWF 9/8/2008 M
mwf 9/1/2008 9/26/2008 MWF 9/10/2008 W
mwf 9/1/2008 9/26/2008 MWF 9/12/2008 F
mwf 9/1/2008 9/26/2008 MWF 9/15/2008 M
mwf 9/1/2008 9/26/2008 MWF 9/17/2008 W
mwf 9/1/2008 9/26/2008 MWF 9/19/2008 F
mwf 9/1/2008 9/26/2008 MWF 9/22/2008 M
mwf 9/1/2008 9/26/2008 MWF 9/24/2008 W
mwf 9/1/2008 9/26/2008 MWF 9/26/2008 F
tr 9/2/2008 9/18/2008 TR 9/2/2008 T
tr 9/2/2008 9/18/2008 TR 9/4/2008 R
tr 9/2/2008 9/18/2008 TR 9/9/2008 T
tr 9/2/2008 9/18/2008 TR 9/11/2008 R
tr 9/2/2008 9/18/2008 TR 9/16/2008 T
tr 9/2/2008 9/18/2008 TR 9/18/2008 R

of course you may want to adjust query for specific
fields you want from the 2 tables...plus change to
append or make-table...

sorry if explanation is brief but 1-hand typing at moment...

good luck,

gary

Thank you so much for your help Allen. You have helped me in the past without
even knowing it and I finally have the chance to say thank you. My co-worker
is going to be so thrilled with this. It would take him days to build the
schedule using cut and paste. Now he will have it done within a matter of
minutes. I knew it could be done but just wasn't sure how to go about it.
Thanks again.

"Allen Browne" wrote:

> Use Replace() in an update query to replace the existing characters with
> numbers.
>
> Back up the database before you run updates (just in case something you
> enter something wrong.)
>
> 1. Create a query using this table.
>
> 2. Change it to an Update query (Update on query menu.)
> Access adds an update row to the grid.
>
> 3. In the Update row under the Days field, enter:
> Replace([Days], "M", 2)
> Run the query.
>
> 4. Change the expression above to:
> Replace([Days], "T", 3)
> Run the query.
>
> 5. Repeat for the other day names.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Pauline B." <(E-Mail Removed)> wrote in message
> news:A82F4CF2-D20F-4434-948C-(E-Mail Removed)...
> > Hi Allen,
> >
> > Thank you so much for the information. I will try it and let you know. One
> > question - Is there a way I can update the Days field to a number (as you
> > suggest) and still keep the information intact? I'm trying to use what I
> > already have. Would I be able to specify each occurrence in the criteria
> > row
> > or is that asking too much of a query? Most are pretty consistent but
> > there
> > are a few exceptions.
> >
> >
> > "Allen Browne" wrote:
> >
> >> The dates have to come from somewhere, so create a counting table. It has
> >> one field of type Number, named (say) CountID, marked as primary key.
> >> Save
> >> the table as tblCount. Now enter a record for each number from 0 to say
> >> 4000. You can use the code in this link to create the data:
> >> http://allenbrowne.com/ser-39.html
> >>
> >> Now create a query using both tables. There must be no join between the 2
> >> tables in the upper pane of query design. Type this expression into the
> >> Field row:
> >> TheDate: [StartDate] + CountID
> >> In the Criteria row under this, enter:
> >> <= [EndDate]
> >> This query yields all dates between the starting date and the ending
> >> date.
> >>
> >> Now you need to filter that to the right days of the week. It would be
> >> easiest if your Days field contains a number between 1 (Sunday) and 7
> >> (Saturday), so a class that met on Monday Tuesday and Wednesday would be
> >> number 234.
> >>
> >> Add the Days field to the query grid.
> >> In the Criteria row, enter:
> >> Like "*" & Weekday([StartDate] + CountID) & "*"
> >> This restricts the dates to the right days of the week.
> >>
> >> In the end, you can use this as is, or you can turn it into an Append
> >> query
> >> (Append on Query menu) to add these dates for the class to the table you
> >> created for this purpose.
> >>
> >> "Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
> >> news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
> >> > Hope someone can help me with this. I work for a community college
> >> > where
> >> > we
> >> > generate a building schedule from a list of active classes in a table
> >> > called
> >> > Active Class Schedule. My co-worker currently does this manually by
> >> > exporting
> >> > that table to Excel and inserting the class in a spreadsheet for the
> >> > number
> >> > of times the class meets. Some of our classes may only meet on a
> >> > certain
> >> > day
> >> > and others may meet on multiple days, such as MTW.
> >> >
> >> > The fields that are used are as follows:
> >> > StartDate
> >> > EndDate
> >> > Days (which has info such as MTWRFS)
> >> >
> >> > Is there a way to create a query to generate multiple line items for a
> >> > class, showing the date that the class is meeting and use the Make
> >> > Table
> >> > Query to generate the Building Schedule? I'm not sure how to format my
> >> > calculated field with DateDiff (if that is the right one). I apologize
> >> > if
> >> > there is a message for this already. Please direct me to it if there
> >> > is.
> >> > Any
> >> > help would be appreciated.
>
>

Gary,

Thank you for the info. I will try your method as well because I'm still
learning and being able to do this within minutes is really incredible. My
co-worker has been manually doing this for 10 years because he doesn't know
the nuts and bolts of Access and VBA code. Your input is really appreciated.

"Gary Walter" wrote:

>
> "Pauline B." wrote:
> > Hope someone can help me with this. I work for a community college where
> > we
> > generate a building schedule from a list of active classes in a table
> > called
> > Active Class Schedule. My co-worker currently does this manually by
> > exporting
> > that table to Excel and inserting the class in a spreadsheet for the
> > number
> > of times the class meets. Some of our classes may only meet on a certain
> > day
> > and others may meet on multiple days, such as MTW.
> >
> > The fields that are used are as follows:
> > StartDate
> > EndDate
> > Days (which has info such as MTWRFS)
> >
> > Is there a way to create a query to generate multiple line items for a
> > class, showing the date that the class is meeting and use the Make Table
> > Query to generate the Building Schedule? I'm not sure how to format my
> > calculated field with DateDiff (if that is the right one). I apologize if
> > there is a message for this already. Please direct me to it if there is.
> > Any
> > help would be appreciated.
>
> Hi Pauline,
>
> PMFBI
>
> another method would involve a calendar table
> (say "tblCal") where you use Excel to "autocomplete"
> a column of dates by "dragging down" after completing
> the first few dates, stopping the drag "way in the future,"
> then import into Access table.
>
> make sure your new table's field (say "CalDate") is
> type Date/Time
>
> then add 1 more (text) field to "tblCal" of length 1
> (say "Dy")
>
> then run update query
>
> UPDATE tblCal SET tblCal.Dy =
> Choose(Weekday([CalDate]),"X","M","T","W","R","F","S");
>
> used "X" for Sunday, but you may want to change to ?...
>
> verify your values are correct for "Dy"
>
> just as a test i created example data
>
> ID Class StartDate EndDate Days
> 1 mwf 9/1/2008 9/26/2008 MWF
> 2 tr 9/2/2008 9/18/2008 TR
> 3 mtwrf 9/1/2008 9/26/2008 MTWRF
>
> then ran query
>
> SELECT
> ACS.Class,
> ACS.StartDate,
> ACS.EndDate,
> ACS.Days,
> C.CalDate AS ClassDate,
> C.Dy
> FROM
> [Active Class Schedule] AS ACS, tblCal AS C
> WHERE
> (C.CalDate
> Between
> ACS.StartDate
> AND
> ACS.EndDate)
> AND
> (InStr(ACS.Days, C.Dy) > 0)
> ORDER BY
> ACS.Class,
> C.CalDate;
>
> and got enumeration like i *think* you wanted:
>
> Class StartDate EndDate Days ClassDate Dy
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/1/2008 M
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/2/2008 T
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/3/2008 W
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/4/2008 R
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/5/2008 F
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/8/2008 M
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/9/2008 T
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/10/2008 W
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/11/2008 R
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/12/2008 F
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/15/2008 M
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/16/2008 T
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/17/2008 W
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/18/2008 R
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/19/2008 F
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/22/2008 M
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/23/2008 T
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/24/2008 W
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/25/2008 R
> mtwrf 9/1/2008 9/26/2008 MTWRF 9/26/2008 F
> mwf 9/1/2008 9/26/2008 MWF 9/1/2008 M
> mwf 9/1/2008 9/26/2008 MWF 9/3/2008 W
> mwf 9/1/2008 9/26/2008 MWF 9/5/2008 F
> mwf 9/1/2008 9/26/2008 MWF 9/8/2008 M
> mwf 9/1/2008 9/26/2008 MWF 9/10/2008 W
> mwf 9/1/2008 9/26/2008 MWF 9/12/2008 F
> mwf 9/1/2008 9/26/2008 MWF 9/15/2008 M
> mwf 9/1/2008 9/26/2008 MWF 9/17/2008 W
> mwf 9/1/2008 9/26/2008 MWF 9/19/2008 F
> mwf 9/1/2008 9/26/2008 MWF 9/22/2008 M
> mwf 9/1/2008 9/26/2008 MWF 9/24/2008 W
> mwf 9/1/2008 9/26/2008 MWF 9/26/2008 F
> tr 9/2/2008 9/18/2008 TR 9/2/2008 T
> tr 9/2/2008 9/18/2008 TR 9/4/2008 R
> tr 9/2/2008 9/18/2008 TR 9/9/2008 T
> tr 9/2/2008 9/18/2008 TR 9/11/2008 R
> tr 9/2/2008 9/18/2008 TR 9/16/2008 T
> tr 9/2/2008 9/18/2008 TR 9/18/2008 R
>
> of course you may want to adjust query for specific
> fields you want from the 2 tables...plus change to
> append or make-table...
>
> sorry if explanation is brief but 1-hand typing at moment...
>
> good luck,
>
> gary
>
>
>
>
>
>
>
>
>

Everything worked Allen. You have no idea how much time you have saved my
co-worker. He will be so surprised. One thing I noticed - the StartDate is
not included on the schedule when I run the query. I've tried putting the >=
sign in there to include the date but I must be placing it in the wrong spot
because nothing is happening. I'm still trying to figure it out but any help
would be so appreciated.

"Allen Browne" wrote:

> Use Replace() in an update query to replace the existing characters with
> numbers.
>
> Back up the database before you run updates (just in case something you
> enter something wrong.)
>
> 1. Create a query using this table.
>
> 2. Change it to an Update query (Update on query menu.)
> Access adds an update row to the grid.
>
> 3. In the Update row under the Days field, enter:
> Replace([Days], "M", 2)
> Run the query.
>
> 4. Change the expression above to:
> Replace([Days], "T", 3)
> Run the query.
>
> 5. Repeat for the other day names.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Pauline B." <(E-Mail Removed)> wrote in message
> news:A82F4CF2-D20F-4434-948C-(E-Mail Removed)...
> > Hi Allen,
> >
> > Thank you so much for the information. I will try it and let you know. One
> > question - Is there a way I can update the Days field to a number (as you
> > suggest) and still keep the information intact? I'm trying to use what I
> > already have. Would I be able to specify each occurrence in the criteria
> > row
> > or is that asking too much of a query? Most are pretty consistent but
> > there
> > are a few exceptions.
> >
> >
> > "Allen Browne" wrote:
> >
> >> The dates have to come from somewhere, so create a counting table. It has
> >> one field of type Number, named (say) CountID, marked as primary key.
> >> Save
> >> the table as tblCount. Now enter a record for each number from 0 to say
> >> 4000. You can use the code in this link to create the data:
> >> http://allenbrowne.com/ser-39.html
> >>
> >> Now create a query using both tables. There must be no join between the 2
> >> tables in the upper pane of query design. Type this expression into the
> >> Field row:
> >> TheDate: [StartDate] + CountID
> >> In the Criteria row under this, enter:
> >> <= [EndDate]
> >> This query yields all dates between the starting date and the ending
> >> date.
> >>
> >> Now you need to filter that to the right days of the week. It would be
> >> easiest if your Days field contains a number between 1 (Sunday) and 7
> >> (Saturday), so a class that met on Monday Tuesday and Wednesday would be
> >> number 234.
> >>
> >> Add the Days field to the query grid.
> >> In the Criteria row, enter:
> >> Like "*" & Weekday([StartDate] + CountID) & "*"
> >> This restricts the dates to the right days of the week.
> >>
> >> In the end, you can use this as is, or you can turn it into an Append
> >> query
> >> (Append on Query menu) to add these dates for the class to the table you
> >> created for this purpose.
> >>
> >> "Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
> >> news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
> >> > Hope someone can help me with this. I work for a community college
> >> > where
> >> > we
> >> > generate a building schedule from a list of active classes in a table
> >> > called
> >> > Active Class Schedule. My co-worker currently does this manually by
> >> > exporting
> >> > that table to Excel and inserting the class in a spreadsheet for the
> >> > number
> >> > of times the class meets. Some of our classes may only meet on a
> >> > certain
> >> > day
> >> > and others may meet on multiple days, such as MTW.
> >> >
> >> > The fields that are used are as follows:
> >> > StartDate
> >> > EndDate
> >> > Days (which has info such as MTWRFS)
> >> >
> >> > Is there a way to create a query to generate multiple line items for a
> >> > class, showing the date that the class is meeting and use the Make
> >> > Table
> >> > Query to generate the Building Schedule? I'm not sure how to format my
> >> > calculated field with DateDiff (if that is the right one). I apologize
> >> > if
> >> > there is a message for this already. Please direct me to it if there
> >> > is.
> >> > Any
> >> > help would be appreciated.
>
>

All the other line items for the date range are showing except when the class
begins. And I've tried every combination I can think of to get the StartDate
included but with no luck. I've tried putting in the TheDate criteria
Between...And and then I tried >=([StartDate]+[CountID]) And <=[EndDate] but
I got back some crazy number of 200,000+ classes. What am I doing wrong? I
know it is a simple fix but boy am I missing it. I must be looking at this
too much.

"Pauline B." wrote:

> Everything worked Allen. You have no idea how much time you have saved my
> co-worker. He will be so surprised. One thing I noticed - the StartDate is
> not included on the schedule when I run the query. I've tried putting the >=
> sign in there to include the date but I must be placing it in the wrong spot
> because nothing is happening. I'm still trying to figure it out but any help
> would be so appreciated.
>
> "Allen Browne" wrote:
>
> > Use Replace() in an update query to replace the existing characters with
> > numbers.
> >
> > Back up the database before you run updates (just in case something you
> > enter something wrong.)
> >
> > 1. Create a query using this table.
> >
> > 2. Change it to an Update query (Update on query menu.)
> > Access adds an update row to the grid.
> >
> > 3. In the Update row under the Days field, enter:
> > Replace([Days], "M", 2)
> > Run the query.
> >
> > 4. Change the expression above to:
> > Replace([Days], "T", 3)
> > Run the query.
> >
> > 5. Repeat for the other day names.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Pauline B." <(E-Mail Removed)> wrote in message
> > news:A82F4CF2-D20F-4434-948C-(E-Mail Removed)...
> > > Hi Allen,
> > >
> > > Thank you so much for the information. I will try it and let you know. One
> > > question - Is there a way I can update the Days field to a number (as you
> > > suggest) and still keep the information intact? I'm trying to use what I
> > > already have. Would I be able to specify each occurrence in the criteria
> > > row
> > > or is that asking too much of a query? Most are pretty consistent but
> > > there
> > > are a few exceptions.
> > >
> > >
> > > "Allen Browne" wrote:
> > >
> > >> The dates have to come from somewhere, so create a counting table. It has
> > >> one field of type Number, named (say) CountID, marked as primary key.
> > >> Save
> > >> the table as tblCount. Now enter a record for each number from 0 to say
> > >> 4000. You can use the code in this link to create the data:
> > >> http://allenbrowne.com/ser-39.html
> > >>
> > >> Now create a query using both tables. There must be no join between the 2
> > >> tables in the upper pane of query design. Type this expression into the
> > >> Field row:
> > >> TheDate: [StartDate] + CountID
> > >> In the Criteria row under this, enter:
> > >> <= [EndDate]
> > >> This query yields all dates between the starting date and the ending
> > >> date.
> > >>
> > >> Now you need to filter that to the right days of the week. It would be
> > >> easiest if your Days field contains a number between 1 (Sunday) and 7
> > >> (Saturday), so a class that met on Monday Tuesday and Wednesday would be
> > >> number 234.
> > >>
> > >> Add the Days field to the query grid.
> > >> In the Criteria row, enter:
> > >> Like "*" & Weekday([StartDate] + CountID) & "*"
> > >> This restricts the dates to the right days of the week.
> > >>
> > >> In the end, you can use this as is, or you can turn it into an Append
> > >> query
> > >> (Append on Query menu) to add these dates for the class to the table you
> > >> created for this purpose.
> > >>
> > >> "Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
> > >> news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
> > >> > Hope someone can help me with this. I work for a community college
> > >> > where
> > >> > we
> > >> > generate a building schedule from a list of active classes in a table
> > >> > called
> > >> > Active Class Schedule. My co-worker currently does this manually by
> > >> > exporting
> > >> > that table to Excel and inserting the class in a spreadsheet for the
> > >> > number
> > >> > of times the class meets. Some of our classes may only meet on a
> > >> > certain
> > >> > day
> > >> > and others may meet on multiple days, such as MTW.
> > >> >
> > >> > The fields that are used are as follows:
> > >> > StartDate
> > >> > EndDate
> > >> > Days (which has info such as MTWRFS)
> > >> >
> > >> > Is there a way to create a query to generate multiple line items for a
> > >> > class, showing the date that the class is meeting and use the Make
> > >> > Table
> > >> > Query to generate the Building Schedule? I'm not sure how to format my
> > >> > calculated field with DateDiff (if that is the right one). I apologize
> > >> > if
> > >> > there is a message for this already. Please direct me to it if there
> > >> > is.
> > >> > Any
> > >> > help would be appreciated.
> >
> >

I don't know what's wrong, Pauline.
Are you sure this criteria is under a date/time field?

Post the SQL statement if you are still stuck (i.e. switch the query to SQL
View.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pauline B." <(E-Mail Removed)> wrote in message
news:A5929057-CDCC-44A2-B4C7-(E-Mail Removed)...
> All the other line items for the date range are showing except when the
> class
> begins. And I've tried every combination I can think of to get the
> StartDate
> included but with no luck. I've tried putting in the TheDate criteria
> Between...And and then I tried >=([StartDate]+[CountID]) And <=[EndDate]
> but
> I got back some crazy number of 200,000+ classes. What am I doing wrong? I
> know it is a simple fix but boy am I missing it. I must be looking at this
> too much.
>
> "Pauline B." wrote:
>
>> Everything worked Allen. You have no idea how much time you have saved my
>> co-worker. He will be so surprised. One thing I noticed - the StartDate
>> is
>> not included on the schedule when I run the query. I've tried putting the
>> >=
>> sign in there to include the date but I must be placing it in the wrong
>> spot
>> because nothing is happening. I'm still trying to figure it out but any
>> help
>> would be so appreciated.
>>
>> "Allen Browne" wrote:
>>
>> > Use Replace() in an update query to replace the existing characters
>> > with
>> > numbers.
>> >
>> > Back up the database before you run updates (just in case something you
>> > enter something wrong.)
>> >
>> > 1. Create a query using this table.
>> >
>> > 2. Change it to an Update query (Update on query menu.)
>> > Access adds an update row to the grid.
>> >
>> > 3. In the Update row under the Days field, enter:
>> > Replace([Days], "M", 2)
>> > Run the query.
>> >
>> > 4. Change the expression above to:
>> > Replace([Days], "T", 3)
>> > Run the query.
>> >
>> > 5. Repeat for the other day names.
>> >
>> > --
>> > Allen Browne - Microsoft MVP. Perth, Western Australia
>> > Tips for Access users - http://allenbrowne.com/tips.html
>> > Reply to group, rather than allenbrowne at mvps dot org.
>> >
>> > "Pauline B." <(E-Mail Removed)> wrote in message
>> > news:A82F4CF2-D20F-4434-948C-(E-Mail Removed)...
>> > > Hi Allen,
>> > >
>> > > Thank you so much for the information. I will try it and let you
>> > > know. One
>> > > question - Is there a way I can update the Days field to a number (as
>> > > you
>> > > suggest) and still keep the information intact? I'm trying to use
>> > > what I
>> > > already have. Would I be able to specify each occurrence in the
>> > > criteria
>> > > row
>> > > or is that asking too much of a query? Most are pretty consistent but
>> > > there
>> > > are a few exceptions.
>> > >
>> > >
>> > > "Allen Browne" wrote:
>> > >
>> > >> The dates have to come from somewhere, so create a counting table.
>> > >> It has
>> > >> one field of type Number, named (say) CountID, marked as primary
>> > >> key.
>> > >> Save
>> > >> the table as tblCount. Now enter a record for each number from 0 to
>> > >> say
>> > >> 4000. You can use the code in this link to create the data:
>> > >> http://allenbrowne.com/ser-39.html
>> > >>
>> > >> Now create a query using both tables. There must be no join between
>> > >> the 2
>> > >> tables in the upper pane of query design. Type this expression into
>> > >> the
>> > >> Field row:
>> > >> TheDate: [StartDate] + CountID
>> > >> In the Criteria row under this, enter:
>> > >> <= [EndDate]
>> > >> This query yields all dates between the starting date and the ending
>> > >> date.
>> > >>
>> > >> Now you need to filter that to the right days of the week. It would
>> > >> be
>> > >> easiest if your Days field contains a number between 1 (Sunday) and
>> > >> 7
>> > >> (Saturday), so a class that met on Monday Tuesday and Wednesday
>> > >> would be
>> > >> number 234.
>> > >>
>> > >> Add the Days field to the query grid.
>> > >> In the Criteria row, enter:
>> > >> Like "*" & Weekday([StartDate] + CountID) & "*"
>> > >> This restricts the dates to the right days of the week.
>> > >>
>> > >> In the end, you can use this as is, or you can turn it into an
>> > >> Append
>> > >> query
>> > >> (Append on Query menu) to add these dates for the class to the table
>> > >> you
>> > >> created for this purpose.
>> > >>
>> > >> "Pauline B." <Pauline B.@discussions.microsoft.com> wrote in message
>> > >> news:C7C89C98-1EE2-488B-AB41-(E-Mail Removed)...
>> > >> > Hope someone can help me with this. I work for a community college
>> > >> > where
>> > >> > we
>> > >> > generate a building schedule from a list of active classes in a
>> > >> > table
>> > >> > called
>> > >> > Active Class Schedule. My co-worker currently does this manually
>> > >> > by
>> > >> > exporting
>> > >> > that table to Excel and inserting the class in a spreadsheet for
>> > >> > the
>> > >> > number
>> > >> > of times the class meets. Some of our classes may only meet on a
>> > >> > certain
>> > >> > day
>> > >> > and others may meet on multiple days, such as MTW.
>> > >> >
>> > >> > The fields that are used are as follows:
>> > >> > StartDate
>> > >> > EndDate
>> > >> > Days (which has info such as MTWRFS)
>> > >> >
>> > >> > Is there a way to create a query to generate multiple line items
>> > >> > for a
>> > >> > class, showing the date that the class is meeting and use the Make
>> > >> > Table
>> > >> > Query to generate the Building Schedule? I'm not sure how to
>> > >> > format my
>> > >> > calculated field with DateDiff (if that is the right one). I
>> > >> > apologize
>> > >> > if
>> > >> > there is a message for this already. Please direct me to it if
>> > >> > there
>> > >> > is.
>> > >> > Any
>> > >> > help would be appreciated.
>> >
>> >