• Schedule report (A2000)

    Author
    Topic
    #413256

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

    I’m trying to get a report that looks like this:

          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

    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!

    Viewing 1 reply thread
    Author
    Replies
    • #910233

      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.

      • #911139

        Thank you, Hans!

        This is much cleaner than what I came up with and it works 100%, unlike what I came up with. blush

        Thanks for the reminder about Date. Can’t believe I did that. That’s one of those things I chastise others for. Oops!!

      • #911140

        Thank you, Hans!

        This is much cleaner than what I came up with and it works 100%, unlike what I came up with. blush

        Thanks for the reminder about Date. Can’t believe I did that. That’s one of those things I chastise others for. Oops!!

      • #919052

        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?

        • #919152

          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 for instructions.

          • #920237

            I could not get it small enough, so I just put it on my website…
            http://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!

            • #920268

              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.

            • #920269

              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.

            • #920274

              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

              @;"off"

            • #920276

              That worked perfectly, Hans, thank you again!

            • #920277

              That worked perfectly, Hans, thank you again!

            • #920275

              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

              @;"off"

          • #920238

            I could not get it small enough, so I just put it on my website…
            http://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!

        • #919153

          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 for instructions.

      • #919053

        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?

    • #910234

      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.

    Viewing 1 reply thread
    Reply To: Schedule report (A2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: