Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Procurement data model

    I'm returning to a IT financial management database I was working on last year, but this time going from a fresh data model and finding much more efficiency. I'm stuck on the best way to execute one portion of the model, and hoping for some guidance. Let me start by saying I've searched and reviewed hundreds of data models for procurement cycles, but have yet to find one that addresses my specific requirement.

    Here's the workflow we're trying to achieve:
    1) We place an order (contract/one-time purchase) from a single vendor.
    2) The order has products/services associated with it, some of which are resold from other vendors.


    3) When the order is placed, we earmark to which departments and G/L subaccounts the order should be charged. Sometimes it is all charged to the same; others, they are charged to multiple different departments/subaccounts.
    4) When the order is placed, we allocate the cost to various different allocation categories (service catalog, type of spend, etc.) but using percentages, not dollar figures.
    5) When the invoice is received, it is matched to the order(s) for which it was generated.

    Here are some conditions that occur:
    1) Often, an order has multiple invoices (either because of partial invoicing or because of recurring charges under a contract).
    2) Occasionally an invoice covers multiple orders.
    3) Occasionally an order and an invoice do not match up (different quantity, price, etc.)
    4) Orders, specifically contracts, are created well in advance for budgeting purposes (separate part of the database).

    Here's the rub:
    1) Allocations need to be entered at order creation for budget purposes (forward-looking), but I need to discriminate between allocations fulfilled and not fulfilled (so as not to skew backward-looking (real) allocations based on an unfulfilled order).
    2) Data entry (forms) get REALLY complicated with this kind of stuff. Am looking for a data model that makes the data entry piece as easy as possible, and also looking for any suggestions on how to handle data entry.
    3) Am looking for one specific answer: I don't currently have the Invoice table tied directly to the order table, because they are in a many-to-relationship via the products actually ordered. Is that wrong? Would it be beneficial to have some other relationship?

    Attached is the relationships for this portion of the database.

    Procurement cycle.pdf

    Appreciate whatever help you can offer.
    Phil

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    934
    This is an interesting post and I've kept my mouth shut all morning hoping those more qualified would chime in. Hopefully this reply will serve to *bump* your post

    Quote Originally Posted by pncbiz View Post
    Here's the rub:
    1) Allocations need to be entered at order creation for budget purposes (forward-looking), but I need to discriminate between allocations fulfilled and not fulfilled (so as not to skew backward-looking (real) allocations based on an unfulfilled order).
    What's your question? And probably elaborate on what you mean by allocations, money/products/category/department/etc.?

    2) Data entry (forms) get REALLY complicated with this kind of stuff. Am looking for a data model that makes the data entry piece as easy as possible, and also looking for any suggestions on how to handle data entry.
    That's a tough one and a broad question, we don't know your business. How is it currently being done?

    3) Am looking for one specific answer: I don't currently have the Invoice table tied directly to the order table, because they are in a many-to-relationship via the products actually ordered. Is that wrong? Would it be beneficial to have some other relationship?
    Your current relationship as I understand it makes sense to me. It might be helpful to see the rest of your table relationships as well.

    By the way it seems that your storing calculated fields? if so that's a no-no. For example doesn't
    Order.ContractTotal = Sum(OrderProduct.Quantity * OrderProduct.UnitCost)
    and
    Order.InvoiceAmount = Sum(OrderInvoice.QuantityInvoiced * UnitCostInvoiced)

  3. #3
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Thank you for replying.
    On #1, the question is (I guess) what's the most efficient way to maintain the allocation relationships? By allocation, I mean allocation of cost in various ways (IT service catalog, department for which the cost was incurred, etc.)
    On #2, I'm building this from scratch. Right now, we do all of this on paper. I'm the CIO at a hospital; the industry is healthcare but the business is IT. It seems there's a ton of advice/information out there about how to model the data, and how to extract reports from the data, but I personally find the resources out there on building efficient forms for data entry is sparse perhaps because so much data entry is automated. I'm looking for ideas on how to make the data entry forms work without running up against performance problems in Access.
    On #3, I can share the whole relationships piece but I'm really trying to focus on just the procurement cycle (purchase req/purchase order/invoice/invoice reconciliation) with some tangential constraints related to budget and allocation. As for storing calculated data, you got me; I'm definitely doing that for a couple of reasons. First, following the "normalize til it hurts, denormalize til it works" rule, for budget comparison purposes it's just a lot easier to query the Order table for contract totals than to query the entire OrderProduct table. Second, I want to create a checksum, if you will, to verify that all the product entry info (quantity, unit cost) is valid. If the contract total should be $10k, but the calculated total is $11k, somebody fat-fingered something.
    Appreciate your interest - all feedback is welcome.
    Thanks,
    Phil

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    You said all feedback so...
    2) Occasionally an invoice covers multiple orders.
    I see that as a major part of the problem. Multiple invoices per order sure, but not one invoice for multiple orders. No wonder things sometimes don't match (#3). IMO there should only ever be a 1 to 1 or many to 1 (invoice to order) relationship but I'm no accountant.

    To allocate AND status order parts I think you could solve that with order line item records. Then you could assign the line item to a cost center and status it as well as edit the status or reallocate portions of the cost. However, if you need to split a line item among cost centers you could not split a line item with a straight 1 to 1 line items table, you'd need a 1 to many design. However, I only got that you want to split orders, not anything like line items.

    This all reminds me of a blanket order release system I worked in. One "order" tied to one PO which is tied to one cost center and has a dollar ceiling. Each release is like a new number (a release is like a line item on a PO). I thought it was a great system but it wouldn't support your current business model.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  5. #5
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    You and me both! Unfortunately, that's how the vendors sometimes work. Software vendors are notorious for writing a new contract for every single additional feature (with different start/stop dates, terms, etc.) but then billing it all on one monthly invoice. While it might seem pedantic (indeed anal) to track those as separate orders, it becomes really important when you get to cancellation terms, budget projections and allocations.

    I was thinking about building a status field in the OrderProduct table, so I could note whether or not it had been fulfilled. That would probably solve the budget projection/real execution piece (#1 above). I don't need to assign the cost of a line item product to more than one department/G/L code, just the allocation (which is easy enough with a linked table).

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    All I can add to that is to incorporate a status date as well. Perhaps
    Status | StatusDate

    Sounds to me like if you cancel/reject something you ought to have a record of when for your own protection.
    Good luck! Your business is not something I'm familiar with, so I will likely have to sit on the sidelines here.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  7. #7
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,458
    I'm looking for ideas on how to make the data entry forms work without running up against performance problems in Access.
    designed properly (and excluding network performance) you shouldn't have performance issues. Ensure forms work like a web page - minimum data which typically means design forms based on queries that will only return one or two records. i.e. don't filter, determine the (search) criteria before you open the form. Do not base on tables as the recordsource.

    @micron
    but not one invoice for multiple orders
    sorry disagree. It is not unknown for suppliers to invoice monthly for all orders delivered during the month. May not quite be relevant to this scenario but utilities/telecoms will invoice in this way (customer has 20 mobile phones spread over 5 departments, they get one monthly invoice from the provider for all the phones)

    @pncbiz the 'direction' of your terminology can be a bit confusing.
    We place an order (contract/one-time purchase) from a single vendor.
    Do you mean 'with a single vendor'?
    some of which are resold from other vendors.
    who's doing the selling? your vendor or you?

  8. #8
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Thanks Ajax.
    I appreciate the advice about the forms. So the form would rerun the query every time the user advances to another record? I'd probably have to use the header to include some unbound search fields for the user, I guess.

    I'll clear up the vendor stuff: We sign a contract with our technology reseller (Company A) to provide products built and sold by other technology vendors (Companies B, C and D). It's important to track not only the reseller (as the company with whom we negotiated the contract) but also the vendors who build the products (to make invoice reconciliation and budgeting easier). That part isn't a challenge. The challenge stems more from the different types of products because that drives different allocations and G/L cost codes (software vs. hardware, for example). In short, we're on the "buy" end of all those vendors... What we sell is positive patient outcomes!

  9. #9
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Another quick one: I've always had challenges when building forms because of the difference between editing and adding data. Is it fairly standard to always make those separate forms, or try to keep it one form for user simplicity?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    customer has 20 mobile phones spread over 5 departments, they get one monthly invoice from the provider for all the phones
    That's one invoice for one service? However, glad you chimed in as I think your experience is better suited to the requirements.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    Is it fairly standard to always make those separate forms, or try to keep it one form for user simplicity?
    One form can be opened several ways. I only ever create one of an object whenever possible. If I had to add a control I'd rather add it once, not 2 or 3 times.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  12. #12
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Quote Originally Posted by Micron View Post
    One form can be opened several ways. I only ever create one of an object whenever possible. If I had to add a control I'd rather add it once, not 2 or 3 times.
    I'm intrigued. Can you elaborate? Do you mean hiding/exposing controls, etc.? Changing the add/edit property?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    Yes, showing/hiding controls, filtering recordsets, changing the recordsouce to a snapshot recordset type if I want to lock all records or disabling controls - whatever is required. To facilitate this I have used form OpenArgs to determine which blocks of setup code to run. You don't have edit rights? Form OpenArgs is passed a value to control this.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.

    Micron

  14. #14
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,458
    I'd probably have to use the header to include some unbound search fields for the user, I guess.
    you could - in which case set the form recordsource to not return any records until a search is required. e.g.

    rather than using something like

    recordsource: myTable

    code on search button
    me.filter="ID=" & cboSupplier
    me.filteron=true

    do something like

    recordsource: "SELECT * FROM myTable WHERE False"

    code when user clicks the search button

    me.recordsource=""SELECT * FROM myTable WHERE ID=" & cboSupplier


    for performance considerations, take a look at this thread on another forum. It's about thinking about upsizing to sql server but the considerations around performance could be of interest
    https://www.access-programmers.co.uk...issues.291269/


    try to keep it one form for user simplicity?
    definitely one form

  15. #15
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    934
    Quote Originally Posted by pncbiz View Post
    2) ... and also looking for any suggestions on how to handle data entry.
    Here's a quick and *dirty* example of how I'm imagining invoice data entry. I don't know if this is helpful or of you're already way passed this...

    Main form based off the Invoice table and a subform for the OrderInvoice table. Then Just use a some unbound 'cascading' comboboxes/listboxes to quickly search for [OrderProduct]s to apply to the vendor's invoice. I'm guessing you'll need some careful vba to enforce business rules.

    Click image for larger version. 

Name:	Untitled.png 
Views:	24 
Size:	22.0 KB 
ID:	44031

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Data Subset - database model
    By jaryszek in forum Access
    Replies: 1
    Last Post: 05-02-2018, 03:07 AM
  2. Help with data model/er diagram
    By Beanie_d83 in forum Database Design
    Replies: 4
    Last Post: 06-22-2016, 07:25 AM
  3. Bill of Materials Data Model
    By uaguy3005 in forum Database Design
    Replies: 3
    Last Post: 12-21-2015, 02:56 PM
  4. Custom form - complex model of data
    By josnow in forum Forms
    Replies: 1
    Last Post: 11-28-2012, 01:25 PM
  5. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 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