Results 1 to 12 of 12
  1. #1
    Chadsterman is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    7

    Royalty payment distribution


    I am pretty new to access, but I am working on an oil and gas royalty distribution database. The quick background is that royalty revenue is recieved from production on a well. Royalty payments are then owed to the royalty owners. I have the recieving payments and the royalty owners established and expressions built to show their respective payments they are owed. I need some help on what it takes to record the payments to the royalty owners and also a way to look up unpaid balances. If you have any suggestions or avenues to look down that would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Create records for payment transactions. Either manual data entry or some complex VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Maybe if you post a sample file, with a minimum of data and modifying sensitive ones, it would be easier to help you.
    The solution of course is there and it is not difficult to achieve either.

  4. #4
    Chadsterman is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    7

    Sample database

    Ok, thanks for offering to help. Hopefully, the zip file will work. If so, you can rummage through it and if you end up on the report. This shows of the numerous leases, owner XX needs to be paid for each lease they are in. I know what the total amount of check would need to be, but then how does that one check come back into the database to show each one of those leases are paid?
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So you want to issue one check to owner for all their leases? Possibly need 2 tables:

    tblChecks
    CheckNum_PK
    PayDate
    Owner

    tblCheckDetails
    CheckNum_FK
    Amount
    Lease
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Chadsterman is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    7
    CarlettoFed - you should see an attachment of the db. Just looking for ideas/strategies on how to show that each individual payment is marked as being paid. As you can see several owners have multiple leases/wells they will be paid on but there will only be one check generated to pay for them all. How does that one check get recorded to all of the leases/wells.
    Attached Files Attached Files

  7. #7
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Sorry for the delay, but there were some problems, I would like to know what the NRI field means because, as shown in the image, it allows you to create two records in the Revenue Deck table - entire

    Click image for larger version. 

Name:	Dati.png 
Views:	20 
Size:	7.9 KB 
ID:	43368

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you see post 5?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Chadsterman is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    7
    Ok, so for those that still might be following this. Tax season got in the way. Still a novice. Now where were we..

    So as the current format stands, I am running the calculations in excel and then bringing them into access to assemble.

    The calculations are that a particular well has 50 owners. A particular oil well gets payment and needs to disburse these funds to the owners. The owners carry a NRI (Net Royalty Interest) for each well. At day's end, the total NRI equals 100%. i.e. if a well gets a payment of $100.00, then all 50 owners will get their NRI portion of the payment. The reason I am calculating in excel and then bringing over is because I need the data to be static once it is in the database. (or so I think). The reason for this is because if Bob has an NRI in the well of 2.00% and then he sells to Mary, I need to be able to have the history of what Bob has been paid as of a certain period and then Mary will start getting paid the 2.00%. To eliminate the excel, I need like a paste special as values or a "posted" feature to where it will break the formulas and turn them into values. Any ideas? Remember still a novice. And honestly, I have looked for similar databases to steal ideas and have come up short.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Well, the calculations can be replicated in Access but first need to get data structure set up to hold data. Did you consider suggestion in post #5?

    Automating creation of records will involve a lot of VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Chadsterman is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    7
    The way I am reading post #5 is something to capture the payments. The problem I am trying to solve is that Bob is not going to be paid until he reaches a $100.00 threshold or once a year. So he could have 6 months of payments that total $60.00 and then sell his interest in August to Mary. Bob wouldn't get paid until EOY. Mary has now acquired Bob's interest as of August. Mary on the other hand was an owner in 2 other wells, so she will get a payment each month because her payments will be over $100.00 each month. The records still need to reflect that Bob will get paid at EOY.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, sounds like you need a table that will show these accrued obligations with a field to include CheckNumber which will be entered later when payment is processed. Basically the tables I showed but without relational integrity enforced. Records will exist in Details that don't yet have a related Check.

    Code creates Details records when well funds are received. Another process later associates Details records with check numbers for payments to owners.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. distribution issue
    By Jen0dorf in forum Access
    Replies: 5
    Last Post: 12-02-2015, 11:28 AM
  2. Replies: 11
    Last Post: 11-28-2015, 10:14 PM
  3. distribution of database
    By Jen0dorf in forum Access
    Replies: 2
    Last Post: 11-27-2015, 01:51 AM
  4. Application distribution
    By paddymx in forum Programming
    Replies: 6
    Last Post: 08-18-2013, 04:38 PM
  5. Compiling for distribution
    By wthoffman in forum Access
    Replies: 1
    Last Post: 02-03-2011, 09:34 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