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

Discussion in 'Microsoft Access Queries' started by Pauline B., Sep 25, 2008.

  1. Pauline B.

    Pauline B. Guest

    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., Sep 25, 2008
    #1
    1. Advertisements

  2. Pauline B.

    Allen Browne Guest

    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 > wrote in message
    news:...
    > 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.
     
    Allen Browne, Sep 25, 2008
    #2
    1. Advertisements

  3. Pauline B.

    Pauline B. Guest

    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 > wrote in message
    > news:...
    > > 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., Sep 26, 2008
    #3
  4. Pauline B.

    Allen Browne Guest

    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." <> wrote in message
    news:...
    > 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 > wrote in message
    >> news:...
    >> > 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.
     
    Allen Browne, Sep 26, 2008
    #4
  5. Pauline B.

    Gary Walter Guest

    "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
     
    Gary Walter, Sep 27, 2008
    #5
  6. Pauline B.

    Pauline B. Guest

    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." <> wrote in message
    > news:...
    > > 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 > wrote in message
    > >> news:...
    > >> > 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., Sep 28, 2008
    #6
  7. Pauline B.

    Pauline B. Guest

    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
    >
    >
    >
    >
    >
    >
    >
    >
    >
     
    Pauline B., Sep 28, 2008
    #7
  8. Pauline B.

    Pauline B. Guest

    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." <> wrote in message
    > news:...
    > > 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 > wrote in message
    > >> news:...
    > >> > 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., Sep 29, 2008
    #8
  9. Pauline B.

    Pauline B. Guest

    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." <> wrote in message
    > > news:...
    > > > 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 > wrote in message
    > > >> news:...
    > > >> > 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., Sep 30, 2008
    #9
  10. Pauline B.

    Allen Browne Guest

    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." <> wrote in message
    news:...
    > 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." <> wrote in message
    >> > news:...
    >> > > 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 > wrote in message
    >> > >> news:...
    >> > >> > 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.
    >> >
    >> >
     
    Allen Browne, Sep 30, 2008
    #10
  11. Pauline B.

    Pauline B. Guest

    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" wrote:

    > 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." <> wrote in message
    > news:...
    > > 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." <> wrote in message
    > >> > news:...
    > >> > > 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 > wrote in message
    > >> > >> news:...
    > >> > >> > 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., Oct 1, 2008
    #11
  12. Pauline B.

    Allen Browne Guest

    Do you have a zero in the counting table, as suggested?

    --
    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." <> wrote in message
    news:...
    > 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, Oct 1, 2008
    #12
  13. Pauline B.

    Pauline B. Guest

    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" wrote:

    > Do you have a zero in the counting table, as suggested?
    >
    > --
    > 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." <> wrote in message
    > news:...
    > > 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.

    >
    >
     
    Pauline B., Oct 1, 2008
    #13
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lynne
    Replies:
    2
    Views:
    195
    Lynne
    Dec 12, 2003
  2. WP

    Find Max of date that also meets other criteria

    WP, Jan 6, 2005, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    289
  3. Randy
    Replies:
    11
    Views:
    987
    SteveS
    Feb 5, 2005
  4. Guest
    Replies:
    5
    Views:
    187
    Duane Hookom
    Mar 3, 2006
  5. headachewithExcel

    calcluating a value if the original value meets a condition

    headachewithExcel, Dec 25, 2007, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    150
    Allen Browne
    Dec 25, 2007
Loading...

Share This Page