Calculation Between Form & Subform

[TheGr8Ginger]

So this may have been answered in another post, but as I'm still learning Access (365), I may not know the correct terminology to how to best search for what I'm looking to attempt. So my apologies if this is a repeat answer. This was just a rough/quick build at work, while my other database I'm building (doing the same thing, but is a lot more extensive, is on my home computer)


I've built a form where I collect load information (Origin, Destination, Miles, Commodity, etc). I've put in a subform to collect prices from carriers that are linked the the LoadID. In the subform I have a field to collect the price each carrier is bidding. The next field is $/mi (cost per mile), that I'd like to be calculate when a price is inputted into the subform record. So I'd take the price (subform) and divide it by the main form's "Miles" field. I've tried building an expression (right click in subform's $/mi field > Build Event > Expression Builder = [Price] / [Loads]![Miles] . This doesn't seem to do calculate anything into the $/mi field of the subform, whether I create a new record, or update a previous one.




I don't know if the following will help or not, but figured I'd provide my table/form layout as of now. You can also see the attachment to see the form itself.


Tables:
Loads
Prices
Forms:
Loads
Prices subform


Any input/guidance you can provide would greatly be appreciated!

[orange]

Can you show us your work effort to identify the calculated cost/mile?

Sounds like you're dealing with

Bidders, Bids and LoadHaulage

[TheGr8Ginger]

Not quite sure what you mean by work effort.

Whenever one of our customers is looking to move some of their freight, we start a spreadsheet that we input all the data about the load, once that's done, we capture prices from carriers that either call us, or we reach out to, into the subform. This helps us see what the going rate should be for this move. Often times, depending on certain load requirements/weight, we price our customer more off of a rate per mile. So it's important for us to see the total price, so we can utilize the trendline and see what numbers we need to NOT worry about (usually the highest and lowest). The $/mi is important for us to keep track of, so when we need to send off a rough quote, we can quickly say we've been priced at this $/mi, based on this weight/dims/lane (state to state). I HOPE that helps? Lol...sometimes I overthink things...

[TheGr8Ginger]

I've attached a couple images of what we're currently working off of. The Quote Sheet is what we use daily, to keep track of everything. The $$$ Per Mile sheet is what originally I started to work on a year and a half, or so back. I had to manually input all the captured data (from the quote sheet), to different tabs of the sheet (whether we booked the load, lost the load, etc), where the main tab/page utilized formula's (see below this paragraph for an example) and displayed it how it worked for me. That spreadsheet was only over about the course of 3 months or so...it's over a year stale now. Moving to a databases will help me combine all of this (and more than, I'm trying to do), and not have to manually type all the data out multiple times.

Example Formula =iferror(averageifs('Misc Quotes'!F2:F,'Misc Quotes'!A2:A,"RGN",'Misc Quotes'!H2:H,">70000",'Misc Quotes'!H2:H,"<80001",'Misc Quotes'!D2,">250"),"")

[orange]

I was just asking to see your expression where the calculation was done.

For Access you should avoid field names that have embedded spaces or **special characters.

Special characters means anything that is not alphanumeric or underscore "_" -- leads to syntax issues.