Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Schedule report (A2000)

    I have a table that looks like this:
    Employee
    Date
    Start
    Finish

    I'm trying to get a report that looks like this:
    <pre> Sun Mon Tue Wed Thu Fri Sat
    Bob 9-5 9-5 9-5 9-5 9-5 off off
    Sue off 9-5 off 5-7 7-9 off off</pre>



    Date holds a full date like 11/1/1972. Start & Stop hold times like 11:30 or 22:00

    I tried a crosstab, but I cant get start & finish in the same field.

    Any suggestions?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Schedule report (A2000)

    You can create a query to return either "off" or "9-5". To get a report in the form you want, you need a crosstab query. With a table named tblTimes, the SQL for such a query could be

    TRANSFORM First(IIf(IsNull([Start]),"Off",Format([Start],"Short Time") & "-" & Format([Finish],"Short Time"))) AS Worked
    SELECT tblTimes.Employee
    FROM tblTimes
    GROUP BY tblTimes.Employee
    PIVOT Format([Date],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

    Note: using Date as a field name is not a good idea, since Date is also a VBA function.

    You can use this query as record source for a report. You will have to restrict the query to the appropriate week.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schedule report (A2000)

    Thank you, Hans!

    This is much cleaner than what I came up with and it works 100%, unlike what I came up with. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks for the reminder about Date. Can't believe I did that. That's one of those things I chastise others for. Oops!!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schedule report (A2000)

    It's been a while since I've visited this project.

    I just noticed that I do not get the "Off" that I am expecting, rather just a blank entry.

    I'm assuming it is because the [Start] is not null, rather zero-length.

    Any ideas on how to address that?
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Schedule report (A2000)

    That would mean that Start is a text field, which seems improbable. Could you attach a strippped down database with just the relevant table or tables? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schedule report (A2000)

    I could not get it small enough, so I just put it on my website...
    www.teamtj.net/off.zip

    You'll need to re-link the tables since it is in 2 parts. The button on the main form will do it.

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schedule report (A2000)

    I couldn't achieve this in the crosstab query.
    Change the expression Worked in the query qryWeeklySchedule to
    Worked: First(Format([Start],"Short Time") & "-" & Format([Finish],"Short Time"))
    then in the report rptWeeklySchedule change the control source of each day to :
    =IIf(IsNull([Sun]),"off",[Sun])
    =IIf(IsNull([Mon]),"off",[Mon])
    ...
    This should do it.
    Francois

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Schedule report (A2000)

    The problem is that the values for "missing data" is not null, they are simply nonexistent. Try setting the Format property of the text boxes Sun, Mon etc. on the report to

    <code>@;"off"</code>

  9. #9
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schedule report (A2000)

    That worked perfectly, Hans, thank you again!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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