Design a Microsoft Access Database for Oil and Gas Royalty Checks
Question details
The user needs a relational database design for tracking oil and gas royalty payments and their supporting details.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Organize producers, properties, wells, received checks, deductions, and production volumes in a database.
- Observed behavior
- The desired database should connect each received check to multiple detail records and support accurate data entry and reporting.
Review several royalty statements and list the fields provided at the check, well, production period, and deduction levels. Identify whether a well can be associated with more than one property in your records before choosing the relationship structure.
Build related tables and entry forms in Microsoft Access
Use normalized tables to track each check and its line items without repeating check information on every detail record.
Give each entity its own primary key and use foreign keys to connect related records. Store facts at the level where they occur; for example, a deduction tied to a check line belongs with that line rather than on the check header.
Avoid table-level lookup fields. Use forms and combo boxes to display readable names while storing the underlying foreign keys.
In Access, select Create > Table Design. Create Producers, Properties, Wells, ChecksReceived, and CheckDetails tables, each with a primary key such as ProducerID or CheckID. Add identifying fields such as producer name, property name, well identifier, check number, and check date.
In ChecksReceived, add ProducerID as a foreign key and fields for the received check amount and date. In CheckDetails, add CheckID as a foreign key, along with the relevant well or property key, production period, product, volume, and line amounts. This allows one check to have multiple detail records.
Create a Deductions table with its own primary key, a CheckDetailID foreign key, deduction type, and amount when a line can have multiple deductions. Use Database Tools > Relationships to connect the primary and foreign keys and select Enforce Referential Integrity where appropriate.
In Table Design, add a PropertyID foreign key to Wells if each well belongs to one property. If wells and properties have a many-to-many relationship in your records, create a junction table with WellID and PropertyID instead.
Use Create > Form Wizard to create a check form and a linked CheckDetails subform. Add combo boxes to select producers, wells, or properties by name while saving their IDs; use a separate form or subform for deductions if needed.
Use Create > Query Design to calculate line totals and sum deductions, then compare the resulting check total with the amount recorded in ChecksReceived. Use Currency fields for monetary amounts and calculate derived values in queries when possible.

Organize royalty statement data in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with royalty statement spreadsheets in a familiar interface. Open compatible Excel files to move existing tabular records into WPS, though it does not replace an Access relational database.

Frequently Asked Questions
How can one royalty check have multiple detail lines in Access?
Give each check a CheckID primary key in ChecksReceived and add CheckID as a foreign key in CheckDetails. Connect the fields under Database Tools > Relationships.
Where should I store deductions on a royalty statement?
If a detail line can have multiple deductions, create a Deductions table with a CheckDetailID foreign key, deduction type, and amount. Record check-level deductions separately if they do not belong to a specific line.
Should I store calculated royalty totals in a table?
Generally, calculate derived totals in a query using Create > Query Design. Retain the actual check amount from the statement so you can compare it with the calculated total.
Can WPS Office replace Microsoft Access for this database?
WPS Spreadsheets can help organize and review royalty data in compatible Excel files, but it does not replace an Access relational database with linked tables, forms, and referential integrity.




