How to track premium payments in Microsoft Access?

Question

How to track premium payments in Microsoft Access?

Jenine Sparacino 0

Hello!

I'm a bit new to Access and I've hit a speedbump.

Background: I'm essentially trying to replace our current system with Access. I've gotten pretty far, just need to finish up a premium tracker and claim payment tracker.

The goal: right now, I'm working on tracking premium payments we have received on several groups. Each group has several tiers under it, each with different prices. I would like to have a premium form where I can enter the check amount, group name, and how many units of each tier and have the system calculate the price.

Right now, I have a table that lists all groups, and a table for each group that lists the tiers within. I have thought of creating a query that aggregates all prices into one comprehensive table.

I can create the form itself, but I don't know how to tell if to look for the group name and corresponding tier and calculate the price.

Any help is appreciated!

3 answers

Your answer

Answer 1

DBG 11,456 Volunteer Moderator

Maybe we can start by looking at your table structure. Can you maybe post a screenshot of your Table Relationships window?

Jenine Sparacino 0 Reputation points

2025-08-28T20:24:51.0533333+00:00

Absolutely, essentially my setup is: Group Data > Effective Date > Sold Rates

In the Sold Rates table, there will be 2-4 tiers, each with a different rate. I would love to enter: Group Name, Effective Date, and number of units of each tier and have it locate the appropriate rates
DBG 11,456 Reputation points Volunteer Moderator

2025-08-28T20:31:28.3766667+00:00

Thanks! Have you tried using DLookup()? If you have different tiers, I might consider storing them in a child table, so you can JOIN it with your entered data based on a foreign key of each tier. Just a thought...
Jenine Sparacino 0 Reputation points

2025-08-28T20:35:51.88+00:00

I'm very new to Access and have just been learning via online tutorials. I don't know what a child table is yet, but it seems helpful! I'll look into that tomorrow

Answer 2

Tom van Stiphout 39,986 MVP Volunteer Moderator

I'd like to understand your business a bit more. You are an underwriter so this is likely an insurance company scenario.

Such company has customers, and each customer insures several things (maybe Auto, Life, Home). How do Groups come into play? Is there perhaps a pool of vehicle owners that form a Group to get a better rate? If you explain the business model a bit more, it will make it easier for us to advise you on the table structure. We're looking forward to you posting the diagram.

Jenine Sparacino 0 Reputation points

2025-08-28T21:35:15.49+00:00

I am indeed an Underwriter! I work for a medical insurance company.

We issue policies to individual companies. Each year, the policy expires and we issue a new one (or they move carriers).

My overall goal is:

To keep track of each company ("group's") information - address, etc.

That would then breakdown into each effective year / policy year.

Within each policy, I need to track the specific terms of the policy including rates (there are generally 2-4 tiers, all of which have different rates)

I need to track premium received - ideally the system could calculate this based on the group, effective date and number of units per tier

I also need to keep track of claims payments - I haven't gotten to this part, yet
Similarly, I would need to track each claimant (individual's) information.
That would then drill down into several claim submissions, each of which would include particular information

Hopefully this is helpful -

Answer 3

Hi Jenine Sparacino

Thank you for posting on the Q&A Forum. I also want to express my appreciation to DBG and Tom van Stiphout for their initial helpful input, which made things much clearer.

Since I’m not an expert in this field, I’d like to share an theoretical suggestion alongside DBG’s recommendation and the diagram you provided to Tom: using a Main Form with a Subform.

The concept is as follows:

Main Form: Displays the primary information at the top, such as Customer Name, Invoice Date, and Invoice ID.
Subform: Shows a list of individual line items below in a grid-like format (similar to a spreadsheet), including Product, Quantity, Price, and Subtotal.

For your specific case:

The Main Form would display overall payment details like Group Name, Policy/Effective Date, Check Number, and Payment Date.
The Subform would allow you to add multiple tier entries for that payment. Each row in the subform would include: Tier Name (from a dropdown list) // Number of Units (entered manually) // Rate (auto-populated when you select the tier) // Subtotal (calculated automatically)

Just a thought I wanted to share in case it’s helpful for your project.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How to track premium payments in Microsoft Access?

3 answers

Your answer