Others

Design a Microsoft Access Database for Oil and Gas Royalty Checks

Nimra MalikNimra Malik Nov 22, 2026 868 views

Question details

The user needs a relational database design for tracking oil and gas royalty payments and their supporting details.

Design a Microsoft Access Database for Oil and Gas Royalty Checks
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the core tables

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.

2
Connect checks to their details

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.

3
Add deductions and relationships

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.

4
Model property and well associations

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.

5
Create entry forms

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.

6
Calculate and review totals

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.

Build related tables and entry forms in Microsoft Access
Match calculations to the statement: Royalty statements can use different deduction and payment conventions. Confirm the meaning of each amount before defining reconciliation formulas.
Free Microsoft Office alternative

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.

Review check amounts, production volumes, and deductions in spreadsheets.Open and edit compatible Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface when moving from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.