Results 1 to 4 of 4
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Building an Allocation model

    This one is a little technical, but here it goes.



    I am building an IT financial management database for the IT department of a hospital. In that database, I want to allocate costs to various allocation categories based on the amount of IT service they consume. For most of the allocations, it's a fairly simple affair (Allocation relationships.pdf). There's an OrderProduct table (from the Procurement cycle) for which the user inputs the products associated with an order/purchase/contract, and the Allocation table is a many to many table combining the OrderProduct with an AllocationType and a percentage to allocate of that OrderProduct to the chose AllocationType. The AllocationType table is a hierarchical table, so you can nest categories of allocations (Service Catalog, IT Functional Spend, IT Asset Spend, etc.).

    Here's the challenge: the hospital has departments and service lines. A department is self-explanatory; I have a hierarchical Organization table that has all the departments in it. A service line is a service the hospital offers (say, Medical/Surgical Inpatient Services) that may or may not be comprised of contributions by multiple departments. For instance, Medical/Surgical Inpatient Services would include both the medical/surgical floor staff, as well as dietary, environmental and cardio/respiratory services. To build the service lines is easy enough; just replicate the allocation methodology to create percentages of departments contributing to a service line.

    However, the department/service line tables are necessarily separate and distinct from the AllocationType table. How can I include the departments and service lines with the allocation types in one list for the user on the front end? I could use a query, I guess, but I often have problems with the query being updateable. What are your thoughts?

    Thanks,
    Phil

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, use queries, macros.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm not quite following your scenario. Sounds like you're getting into recursion? FYI access doesn't play well with recursive relationships.

    To your question, I think it sounds like you need a union query. In regard to being updateable, you do or do not want the query to be updateable? A union query is not updateable, and you can set your queries or form recordsets as read only.

    Maybe this will help?: http://allenbrowne.com/ser-61.html

  4. #4
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Thanks everyone - I'm not there yet, but when I start building the query I might be back...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. implicit allocation
    By Anne_VW in forum Programming
    Replies: 2
    Last Post: 09-03-2019, 10:53 AM
  2. Replies: 1
    Last Post: 06-24-2016, 05:05 AM
  3. Oil well allocation
    By sean88 in forum Database Design
    Replies: 6
    Last Post: 03-02-2015, 11:08 AM
  4. Creating a Resource Allocation Table/Form
    By canderson in forum Access
    Replies: 7
    Last Post: 05-30-2014, 11:11 AM
  5. How to show monthly % allocation in database
    By Aneta in forum Database Design
    Replies: 11
    Last Post: 08-25-2011, 08:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums