Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Post Unanswered: Display staff time table

    Hi,
    I'm trying to display a timetable from Access, at present I'm using a SQL query to display 7 days from system date this displays the next 7 days of the roster. This is not very elegant anyone got any better ideas???

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Display staff time table

    Originally posted by bigear
    Hi,
    I'm trying to display a timetable from Access, at present I'm using a SQL query to display 7 days from system date this displays the next 7 days of the roster. This is not very elegant anyone got any better ideas???
    What do you want the timetable to look like? What does your current query and its output look like?

  3. #3
    Join Date
    Feb 2003
    Posts
    8
    At present looks like
    http://mulliganspub.brinkster.net/pr...splaylastw.asp
    I'm using an Access DB

    I want juts one unique date like,
    22/2/03 pat smith 5-1 john 11-2 mark 12-2

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are querying the records in date order, so you should be able to use logic something like this:

    Code:
    oldDate = 01/01/2001 (i.e. a date before start of real date range)
    
    loop
      get record
      if record.date != oldDate then
        print record.date
      end if
      print record.person, record.start_time, record.end_time
    end loop
    Depending on the formatting you use, this could result in:

    22/2/03 pat smith 5-1 john 11-2 mark 12-2
    23/2/03 pat smith 5-1 john 11-2 mark 12-2
    ...

    or
    22/2/03
    pat smith 5-1
    john 11-2
    mark 12-2
    23/2/03
    pat smith 5-1
    john 11-2
    mark 12-2

  5. #5
    Join Date
    Feb 2003
    Posts
    8
    Originally posted by andrewst
    You are querying the records in date order, so you should be able to use logic something like this:

    Code:
    oldDate = 01/01/2001 (i.e. a date before start of real date range)
    
    loop
      get record
      if record.date != oldDate then
        print record.date
      end if
      print record.person, record.start_time, record.end_time
    end loop
    Depending on the formatting you use, this could result in:

    22/2/03 pat smith 5-1 john 11-2 mark 12-2
    23/2/03 pat smith 5-1 john 11-2 mark 12-2
    ...

    or
    22/2/03
    pat smith 5-1
    john 11-2
    mark 12-2
    23/2/03
    pat smith 5-1
    john 11-2
    mark 12-2
    This is what I have at the moment..

    sql="SELECT empshift.date, shifts.starttime, shifts.endtime, emp.first, emp.last FROM shifts INNER JOIN (emp INNER JOIN empshift ON emp.empid=empshift.empid) ON shifts.shiftid=empshift.shiftid WHERE (((empshift.date) Between (Now()-7) And Now()));"

    rs.Open sql, conn
    %>
    <table border="1" width="100%">
    <tr>
    <%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
    next%>
    </tr>
    <%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
    <td><%Response.Write(x.value)%> </td>
    <%next
    rs.MoveNext%>
    </tr>
    <%loop
    rs.close
    conn.close
    %>

    I'm not great at Recordset Parameters can I insert a display parameter ??

  6. #6
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Thumbs up THANK YOU VERY MUCH!

    I have been struggling (off and on) with this problem for more than a month. I had even posted here before, but hadn't gotten the provided solution to work correctly (probably my fault, not the forum authors' fault).

    I logged on tonight to re-post the code and question, and I find the perfect answer waiting for me! I can't tell you how happy I am right now. You guys are the best.

    I modified your code and included a varialbe (we want to display the last X days of info for each customer's account, setting it individually for each customer). Your code worked perfectly! I'm still amazed that my month-long headache went away in less than 10 minutes.

    Thanks again guys -- again, you're the best!

    Buzzter
    Houston, Texas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •