Results 1 to 7 of 7

Thread: Calendar Report

  1. #1
    Join Date
    Feb 2004

    Unanswered: Calendar Report

    Hello everyone, I am trying to create a calendar type report to show a daily schedule for all of the trucks loading at our 5 loading docks.

    The data is pretty simple, just a table with a row for each order including:

    Ord_No Mill_No SchedDate SchedTime
    1001 1 3/1/2005 6:30 AM
    1002 2 3/1/2005 6:30 AM
    1003 3 3/1/2005 6:30 AM
    1004 4 3/1/2005 6:30 AM
    1005 5 3/1/2005 7:15 AM
    1006 1 3/1/2005 7:15 AM
    1007 2 3/1/2005 7:15 AM
    1008 4 3/1/2005 7:15 AM
    1009 3 3/1/2005 11:00 AM
    etc. etc. etc.

    what I would like the report to look like is to show 1 day for all 5 mills from 6 AM to 6 PM such as:

    Mill1 Mill2 Mill3 Mill4 Mill5
    6 1001 1002 1003 1004
    7 1006 1007 1008 1005
    11 1009

    Does anyone have any suggestions on how I can best structure my query and report?

    Should I create a query with a record for each time? (The load times a pretty structured every 45 minutes, i.e. 6:30 AM, 7:15 AM, 8:00 AM)

    Or should I group by Mill# and have multiple columns on the report?

    How do I create a query so that it handles the gaps so the report looks consistent even though there is no truck scheduled at a particular time?

    I have been messing around with this for about a week now, trying all sorts of things, but none of them seem very reliable. I even tried messing around with using an Outlook View Control on the form, but it's very complicated.

    I jsut want a calendar view of our loading scheduled based on the date and time being entered in a table.

  2. #2
    Join Date
    Feb 2005
    I would use a table with the 5 rows of your docking station and a field for the time.
    everytime you generate the report delete all records of that table (you can do this user-based with a field that contains the username if it's used by various ppl) and after that append the hole day.
    now create a recordset based on your table containing the data and loop through the records. now update the report-table with the data from the recordset (a small procedure can do that job).

    make a report with the 6 fields (1 time, 5 docking stations) in the detail-section. set the datasource and your ready.

  3. #3
    Join Date
    Sep 2003
    What I would do is run a loop that increments every 15 mins (thats a row) and query your DB table for any scheduled departures for the mills and write out the record. Your report is pretty easy then ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2004
    Thanks guys, I like both of those suggestions. I think I'll combine them and do the looping thing with 15 minute intervals to get more granularity and flexibility and create a record for each time slot with fields for each of the mills.

    Now then, any suggestions on where best to implement that procedure? Stored Procedure? ADO? I'm not 100% up to speed on where/how to create temp tables, but poitn me in the right direction and I'm all up for learning something new today.

    Thanks again.

  5. #5
    Join Date
    Feb 2005
    Blacksburg, Virginia
    Did you ever get this figured out??

    I've come up with something pretty simple if you still need suggestions. No temp tables or anything.

  6. #6
    Join Date
    Feb 2004
    Honestly, I haven't had any chance to work on it since yesterday (my boss came back from a business trip so I don't have time to do things like work).

    I would LOVE to hear a simple suggestion without temp tables.

    This application will generally be used by 1 person at a time, maybe 2, so it's not really worth putting a lot of effort into avoiding complications with locking or things being out of sync etc. but I would love to just avoid it all completely if possible.

    Thanks for your help.

  7. #7
    Join Date
    Feb 2005
    Blacksburg, Virginia

    Long, but simple

    The table you mentioned first with the delivery times, order numbers etc, I'll refer to as "Orders"

    Make a new table with one field, DeliveryTime with Date/Time as the type, and set its format to Medium Time. Set this field as the Primary Key. Then simply populate all your 15 minute delivery intervals in it. 6:30 AM, 7:15 AM, 8:00 AM, etc. I'll refer to this table as "Times" from now on.

    Now you need to set up a relationship between the two tables. Go to the Relationships screen and make sure both tables are added to the field list. Drag the DeliveryTime field from the Times table into the SchedTime field in the Orders Table. The window that pops up should already be correct, but make sure there is a one-to-many relationship from Times.DeliveryTime to Orders.SchedTime

    Now create a new report with the design wizard. Move the following into the Selected Fields box:
    Table: Times -> DeliveryTime
    Table: Orders-> Ord_No, Mill_No

    On the next page tell Access to order by Times. There's no need to add any grouping levels on the next page. On the following page, sort on Mill_No ascending. Pick whichever layouts you want, and select Modify the Report's Design before clicking Finish.

    This is real close, but you want to be able to enter a specific date. Add a textbox in the Report Header section, open its properties and type something like "ScheduleDate" in the Data tab's Control Source.

    Now pull up the Report's Properties. The Data tab's Control Source should read something like this:
    SELECT [Times].[DeliveryTime], [Orders].[Ord_No], [Orders].[Mill_No] FROM Times INNER JOIN Orders ON [Times].[DeliveryTime]=[Orders].[SchedTime];

    Before the semicolon at the end, append the following:
    WHERE [Orders].[SchedDate] = ScheduleDate

    Whenever you open the report, a prompt will come up asking for ScheduleDate. Type it in and you'll get all your delivery times for that day.

    You can modify this to read from another form, or look on a range, whatever. This should be enough to get you going however you want to design it.

    Hope it works!!

Posting Permissions

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