Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula Nightmare (2002)

    Hi!

    I've been working at this mentally and with queries and I just can't get. It's a bear to explain so I'm also posting a picture to hopefully make it clear.

    I need to arrive at the total of unfilled hours. Each hour is assigned to a [visit_no] which in turn is linked to a [order_no]. For example, [order_no] 001 has 5 [visit_no] assigned each of which has a number or [hours] assigned to it. When a [visit_no] is canceled, it may or may not get reassigned. What I need to find out is total hours originally assigned per [order_no] - the [visit_no] canceled. Where the difficulty comes in is when a [visit_no] is canceled and the [visit_no] is recreated to be filled (which oftentimes happens) there are now 6 [visit_no] attached to the [order_no], one of which is the cancled [visit_no]. I need to arrive at the total lost [hours] due to cancellations without counting numbers twice. FYI, cancellations are coded in [visit_status] as C.

    I've attached a picture to try to clarify.

    Thanks,
    Leesha

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

    Re: Formula Nightmare (2002)

    Do you get the correct number of unfilled hours as follows?

    - Create a query based on the table.
    - Add the Order No, Visit Hours and Visit Status fields to the query design grid.
    - Enter C in the Criteria line under Visit Status.
    - Select View | Totals to change the query into a Totals query.
    - Leave the Total option for Order No as it is (Group By), change it for Visit Hours to Sum and for Visit Status to Where.
    - Switch to datasheet view to inspect the results.

    If not, please provide more detailed information.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Nightmare (2002)

    Hi Hans!

    This does give the total number of hours canceled so that works great. I need however to get the total numbers of hours that actually went unfilled as a client can cancel, the visit can get reassigned to another day (notice I didn't say "shift" today) and remain unfilled. The goal is not to count the unfilled hours twice > once as canceld and then once as unfilled if the office didn't fill it.

    Leesha

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

    Re: Formula Nightmare (2002)

    What are the criteria to determine that an hour is unfilled?

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Nightmare (2002)

    OMG, I can barely explain it to me.

    Total billable hours is made up of shifts are either confirmed , Closed (T) or verified(V).
    Lost hours is made up of Client cancellations (x), Employee cancellations(e), office cancellations (o), No shows (s), Can't fill (F) or not assigned (n).
    All clients have various order numbers [order_no] which are assigned one to multiple visits [visit_no]. Each [visit_no] is assigned a catagory as described above.
    There are hours [hours] associated with each [visit_no].
    A new [visit_no] with hours is assigned to an [order_no] any time one of the above catagories is assigned.

    My goal is to find the total [hours] that had a catagory of either N, X,E,O,S,F. My problem is that an X or "client cancled" may have ultimately been filled, therefore shouldn't be counted in the unfilled shifts.

    For example, Mrs. Jones has two 3 hour shifts each week. On 10/18 she canceled one 3 hour shift and rescheduled it for 10/19. This shift went unfilled. The computer shows the following

    [visit_no] [visi_status] [Hours]
    0001 C 3
    0004 X 3
    0005 N 3

    My problem is that there are only 6 hours originally scheduled for the week. If I sum X+N I get 6 when in reality 3 hours were filled and 3 hours were unfilled.

    Does this help?

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Nightmare (2002)

    I'm not sure if this spreadsheet will help or not.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Nightmare (2002)

    Hi Hans,

    I just wanted to let you know not to bother working on this. I've been at it all day and I simply don't see a way to be able to differentiate what the original number of scheduled hours would be in order to do the math to figure out the rest. It has however been a good learning experience so not a waste!

    Thanks,
    Leesha

Posting Permissions

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