Access formula for calculating part round-trip part one-way mileage

Question

Access formula for calculating part round-trip part one-way mileage

Robbie Doo 65

I need help with a formula to calculate the rate for part one-way and part round-trip travel.

For example; I have a group of 4 riders traveling from A to B and B to A, however, one of the riders decided not to take the return route. So there were 4 going and 3 coming back.

The formula I have calculates more rate for the one way rider than the rest of them. Why's that?

Help is appreciated.

0 comments

Answer accepted by question author

George Hepworth 23,120 Volunteer Moderator

My previous suggestion applies. Add TravelType to the WHERE clause to segregate the calculations.

It's not clear whether you have a properly designed table with a Foreign Key for TravelType, or if you used a MultiValue field with those three values in it for TravelType. Either way, the parameter form will have to provide a control that allows you to indicate which one you want to calculate on.

Another option could be to add it to the query's SELECT and GROUP BY clauses. That would return all three.

EDIT: I made a mistake. I think this is the right syntax.


SELECT DailyCarPoolers.PU_Dt, DailyCarPoolers.TravelType, Count(*) AS DailyCarPoolerCount
FROM (SELECT DISTINCT PU_Dt, Carpooler, TravelType
FROM tblTravelRoute 
WHERE PU_Dt BETWEEN Forms!frmParameter01a!txtStartDt AND Forms!frmParameter01a!txtEndDt  AND PU_Loc = 2)  AS DailyCarPoolers
GROUP BY DailyCarPoolers.PU_Dt, DailyCarPoolers.TravelType;

Robbie Doo 65 Reputation points

2026-08-30T23:42:55.0333333+00:00

Thanks, George, for your suggestion, however, I was able to modify it and make it work. Thanks!

2 additional answers

Your answer

Robbie Doo 65 Reputation points

2026-08-30T23:42:55.0333333+00:00

Thanks, George, for your suggestion, however, I was able to modify it and make it work. Thanks!

Answer 1

Roebe Ta 1,025 Independent Advisor

Hello @Robbie Doo,

Good day! I hope you are doing well.

To help provide the most accurate and effective solution for your issue, could you share a bit more detail about your setup:

What version of Microsoft Access are you using?
Is the calculation being performed in a table, query, form, report, or VBA code?
Could you provide the exact formula or expression that is calculating the mileage rate?
What are the names of the relevant tables and fields involved in the calculation?
Could you share a few sample records (with any sensitive information removed) that demonstrate the issue?
How are you determining which riders are round-trip and which are one-way?
What result is the formula currently returning, and what result were you expecting instead?

If possible, please include screenshots of the table design, query design, or the SQL statement used for the calculation.

This additional context will help us better understand how the data is structured and allow us to recreate the scenario on our side to identify why the one-way rider is being charged a higher rate than expected.

Looking forward to your response so we can get this sorted out for you together.

Robbie Doo 65 Reputation points

2026-08-27T17:09:22.0866667+00:00

Hi Roebe, I'm using the 365 Office version. And the informatioin I'm pulling is from the underlying query for the report. The travel type is a drop-down (Roundtrip, Workbound & Homebound), when Roundtrip is used all the way the amounts are calculated correctly for everyone, but when other travel types are used, it calculates more for that person than anyone else. That's where I get confused, as to why this person traveling less is paying more.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-28T04:25:25.0666667+00:00

And your calculations are ... ? I'm not sure how you expect assistance with code/expressions/values/fields we can't see.
Roebe Ta 1,025 Reputation points Independent Advisor

2026-08-28T10:43:27.17+00:00
Thank you for the additional information, @Robbie Doo .

Since you mentioned that the same result occurs in both Access and Excel, this may indicate that the calculation method itself is causing the unexpected result rather than an issue with Access.

However, I'm currently having difficulty reproducing the issue on my side. To help me test the behavior correctly and better understand the scenario, could you kindly provide some additional information:

What is the actual formula or expression being used to calculate the mileage charge?

How is the total trip cost determined? Is it based on distance, a mileage rate, a flat fee, or another method?

Are the round-trip riders and one-way riders supposed to split the total cost equally, or proportionally based on the distance traveled?

Could you provide a simple example showing:

Total round-trip mileage

Number of riders going out and returning

The amount each rider is currently being charged

The amount you expect each rider to be charged

If possible, please share the relevant query SQL, calculated field expression, or screenshots of the calculation fields (with any sensitive information removed).

For example, if 4 riders travel from A to B and only 3 riders return from B to A, there are several valid ways to allocate the cost. The expected charge for the one-way rider depends on the business rule being used, so seeing the formula and a sample calculation will help determine whether the result is mathematically correct or if the expression needs to be adjusted.

I look forward to your reply.
Robbie Doo 65 Reputation points

2026-08-28T16:45:12.6933333+00:00

Hi Roebe, there is no special code or function. I just multiply the total miles with gas rate and divide it by number of people. It works fine as long as everyone is riding round-trip. However, when one took one way only, it threw off the calculation.
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-08-28T18:22:45.92+00:00

Unfortunately, "no special code" tells us nothing useful.

Show us your actual calculations in the SQL or wherever it is done. That's the only way we can help.

In addtiion, and this is also very important, show us the actual table(s) and fields involved. A screen shot of the table(s) in design view is good. Add a screenshot showing some sample data as well.

The more detail you provide, the more likely it will be that someone can spot the problem in the code or the SQL or in the data.

Thank you.
Robbie Doo 65 Reputation points

2026-08-30T16:58:52.7466667+00:00
Hi George, I mentioned earlier that I had to separate the round-trip rides into go & back. However, my SQL is missing the "Travel Type" in order to calculate the number of riders correctly.

My SQL is below, and my travel types are 1-Roundtrip, 2-Workbound, 3-Homebound

SELECT DailyCarPoolers.PU_Dt, Count(*) AS DailyCarPoolerCount FROM (SELECT DISTINCT PU_Dt, Carpooler FROM tblTravelRoute WHERE PU_Dt BETWEEN Forms!frmParameter01a!txtStartDt AND Forms!frmParameter01a!txtEndDt AND PU_Loc = 2) AS DailyCarPoolers GROUP BY DailyCarPoolers.PU_Dt;
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-08-30T17:28:57.5433333+00:00
Thanks, that helps, but it's still just the SQL without the tables.

I can take a guess, though.
This assumes that the parameter form has a combo box offering the three traveltypes and the table, tblTravelRoute has a field called TravelTypeID, which is a Foreign Key to a lookup table with your three travel types. It would return a count of carpoolers for the selected travel type.

If my guess about the field in the table is wrong, we'll need to adjust for that.

SELECT DailyCarPoolers.PU_Dt, Count(*) AS DailyCarPoolerCount FROM (SELECT DISTINCT PU_Dt, Carpooler FROM tblTravelRoute WHERE PU_Dt BETWEEN Forms!frmParameter01a!txtStartDt AND Forms!frmParameter01a!txtEndDt AND TravelTypeID = Forms!frmParameter01a!cboTravelType AND PU_Loc = 2) AS DailyCarPoolers GROUP BY DailyCarPoolers.PU_Dt;
Robbie Doo 65 Reputation points

2026-08-30T17:48:30.45+00:00

The Travel Type is not included in the parameter call, it's just a type in the table that is selected by the user depending on the type of ride
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-08-30T21:14:28.4066667+00:00

Addressing that statement requires, as has been requested multiple times, complete information about the table. A screenshot of the table in design view showing all of the fields, including TravelType, will help. Sample data will also help.

To answer the question you want to answer, TravelType will have to be included in the query parameter. That's how SQL works. You want to know about the different travel types, so that has to be a factor in the SQL. It needs to be in the WHERE clause.

Because we can't see the table, we can only make informed guesses about it, though.
Robbie Doo 65 Reputation points

2026-08-30T21:36:22.0933333+00:00

Hi George, here is a picture of a sample table. As you can see, Nano rode to work but didn't come back.
Roebe Ta 1,025 Reputation points Independent Advisor

2026-08-31T16:04:17.9+00:00

Hi @Robbie Doo,
Based on the information you provided, I suggest that the issue may be related to how the rider count is being calculated rather than the mileage formula itself.

In your example, there are 3 Workbound riders (Jane, Sam, and Nano) but only 2 Homebound riders (Jane and Sam). However, your current query counts distinct riders by date only.

Since TravelType is not included, the query cannot distinguish between Workbound and Homebound trips, which may lead to an incorrect rider count when calculating the cost per rider.

As a test, you may want to modify the query to include TravelType and calculate the rider count separately for each travel type. This should help ensure that the Workbound and Homebound portions of the trip are calculated independently before combining the results for round-trip riders.

Could you test this and let me know how it works? If the issue persists, please share the updated query and the results you receive, and I'll be happy to take a closer look.
Robbie Doo 65 Reputation points

2026-08-31T17:51:52.8566667+00:00

Thank you, Roebe, I was able to adjust the SQL per George's suggestion and it worked fine. Thank you all for your help.
Roebe Ta 1,025 Reputation points Independent Advisor

2026-09-01T07:10:54.7266667+00:00

Thanks for your confirmation, I'm glad your issue is resolved!

If you run into any new trouble, please don't hesitate to create another thread.

Wishing you a successful day!

Answer 2

Duane Hookom 26,935 Volunteer Moderator

It would help if you provided the formula(s) you are using. If they get complex, I would create a user-defined function that would perform the calculations, allow comments, and enable stepping through when testing.

Robbie Doo 65 Reputation points

2026-08-27T17:05:37.5866667+00:00

Hi Duane, thanks for you response.

I created separate textboxes for different miles. Say; btw 1 - 15, btw 15-40, and so on. As long as I'm using the full round-trip miles, the calculation comes up correctly. However, if I use half the mileage for one of the riders, for some reason that person gets more mileage amount to pay than the round-tip people. I tried the same method in Excel, and it comes up the same. Maybe it's the concept of the calculation that I'm having trouble with?
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-28T22:22:19.4633333+00:00

It's still not clear how your tables, forms, or calculations are performed. If you really want some help, please provide more information with your specifications and current setup.
Robbie Doo 65 Reputation points

2026-08-29T02:02:30.9733333+00:00

It's OK Duane, thanks for trying to help.

Instead of using roundtrip, I separated them to go and back and the calculations worked fine.