Extract number of times a class meets & write to a table

  • Thread starter Thread starter Pauline B.
  • Start date Start date
P

Pauline B.

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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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.
Click to expand...
Click to expand...
 
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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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 said:
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. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Pauline B. said:
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.
Click to expand...

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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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 said:
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Pauline B. said:
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.
Click to expand...

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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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 said:
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.

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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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.


:

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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. said:
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
=
Click to expand...
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.

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.


:

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.

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.
Click to expand...
Click to expand...
Click to expand...
 
Hey Allen thanks for getting back with me. I have tried every combo I can
think to include a line item for the start date of a class but nothing is
happening.

This is a sample of what it prints for a class. All the dates are accounted
for except when the class actually begins. There is no line item for 4/11/07.
That is how I noticed it.

TheDate Course # Sec Class Title StartDate
EndDate
4/18/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
4/25/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/2/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/9/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/16/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/23/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/30/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07

Here are the SQL statements.

SELECT [Converted Schedule].DatatelID, [StartDate]+[CountID] AS TheDate,
[Converted Schedule].PreNum, [Converted Schedule].Sec, [Converted
Schedule].Class, [Converted Schedule].SecMax, [Converted Schedule].Days,
[Converted Schedule].StartDate, [Converted Schedule].EndDate, [Converted
Schedule].StartTime, [Converted Schedule].EndTime, [Converted
Schedule].SessTime, [Converted Schedule].Campus, [Converted Schedule].Room,
[Converted Schedule].Instructor
FROM tblCount, [Converted Schedule]
WHERE ((([StartDate]+[CountID])<=[EndDate]) AND (([Converted
Schedule].PreNum) Not Like "CEWWW-*") AND (([Converted Schedule].Days) Like
"*" & Weekday([StartDate]+[CountID]) & "*"))
ORDER BY [Converted Schedule].DatatelID, [StartDate]+[CountID], [Converted
Schedule].PreNum, [Converted Schedule].Sec;

I know I'll be knocking my head later because it is something really simple,
but I've tried putting criteria in different fields with no success. Thanks
again for taking the time Allen.


Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
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. said:
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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.

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.


:

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.

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.
Click to expand...
Click to expand...
Click to expand...
 
Do you have a zero in the counting table, as suggested?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
Hey Allen thanks for getting back with me. I have tried every combo I can
think to include a line item for the start date of a class but nothing is
happening.

This is a sample of what it prints for a class. All the dates are
accounted
for except when the class actually begins. There is no line item for
4/11/07.
That is how I noticed it.

TheDate Course # Sec Class Title
StartDate EndDate
4/18/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
4/25/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/2/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07
05/30/07
5/9/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07
05/30/07
5/16/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/23/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/30/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07

Here are the SQL statements.
Click to expand...
SELECT [Converted Schedule].DatatelID,
[StartDate]+[CountID] AS TheDate,
[Converted Schedule].PreNum,
[Converted Schedule].Sec,
[Converted Schedule].Class,
[Converted Schedule].SecMax,
[Converted Schedule].Days,
[Converted Schedule].StartDate,
[Converted Schedule].EndDate,
[Converted Schedule].StartTime,
[Converted Schedule].EndTime,
[Converted Schedule].SessTime,
[Converted Schedule].Campus,
[Converted Schedule].Room,
[Converted Schedule].Instructor
FROM tblCount, [Converted Schedule]
WHERE (([StartDate]+[CountID] <= [EndDate])
AND ([Converted Schedule].PreNum Not Like "CEWWW-*")
AND ([Converted Schedule].Days Like "*" & Weekday([StartDate]+[CountID]) &
"*"))
ORDER BY [Converted Schedule].DatatelID,
[StartDate]+[CountID],
[Converted Schedule].PreNum,
[Converted Schedule].Sec;
 
See I knew it was going to be something simple that I missed. Works great
now. My thanks again for all your help Allen. Have a great day.

Allen Browne said:
Do you have a zero in the counting table, as suggested?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pauline B. said:
Hey Allen thanks for getting back with me. I have tried every combo I can
think to include a line item for the start date of a class but nothing is
happening.

This is a sample of what it prints for a class. All the dates are
accounted
for except when the class actually begins. There is no line item for
4/11/07.
That is how I noticed it.

TheDate Course # Sec Class Title
StartDate EndDate
4/18/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
4/25/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/2/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07
05/30/07
5/9/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07
05/30/07
5/16/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/23/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07
5/30/2007 CEART-1101 01 Ballroom Boot Camp 04/11/07 05/30/07

Here are the SQL statements.
Click to expand...
SELECT [Converted Schedule].DatatelID,
[StartDate]+[CountID] AS TheDate,
[Converted Schedule].PreNum,
[Converted Schedule].Sec,
[Converted Schedule].Class,
[Converted Schedule].SecMax,
[Converted Schedule].Days,
[Converted Schedule].StartDate,
[Converted Schedule].EndDate,
[Converted Schedule].StartTime,
[Converted Schedule].EndTime,
[Converted Schedule].SessTime,
[Converted Schedule].Campus,
[Converted Schedule].Room,
[Converted Schedule].Instructor
FROM tblCount, [Converted Schedule]
WHERE (([StartDate]+[CountID] <= [EndDate])
AND ([Converted Schedule].PreNum Not Like "CEWWW-*")
AND ([Converted Schedule].Days Like "*" & Weekday([StartDate]+[CountID]) &
"*"))
ORDER BY [Converted Schedule].DatatelID,
[StartDate]+[CountID],
[Converted Schedule].PreNum,
[Converted Schedule].Sec;
I know I'll be knocking my head later because it is something really
simple,
but I've tried putting criteria in different fields with no success.
Thanks
again for taking the time Allen.
Click to expand...
Click to expand...
 
Back
Top