Auto-populate table field from calculated field in another table? (1 Viewer)

Hey Lucy

Registered User.
Local time
Today, 16:17
Joined
Jan 20, 2012
Messages
124
Okay, I'm back, though it's been a while, so hello to everyone, especially all of you who have helped me in the past. Retired, got out of the biz & haven't fooled w/Access for about a year, so it's like learning it all over again.

I know I've done this before but lost the database I did it on so can't look to see what I did. Here is my scenario:

I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)

Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.

Any ideas here? Thank you so much!
 

plog

Banishment Pending
Local time
Today, 18:17
Joined
May 11, 2011
Messages
11,613
This shouldn't be done at the table level, nor should the current field you have [Total Miles]. Calculated values should be calculated when needed. Often, and in this instance, that means in a query.

To get the total miles you would use an aggregate query. It would look something like this:


Code:
SELECT Vendors.VendorID, SUM([supplies].[Mileage]*2) AS TotalMileage
FROM Vendors
LEFT JOIN supplies ON supplies.VendorID = Vendors.VendorID
GROUP BY Vendors.VendorID;
 

Hey Lucy

Registered User.
Local time
Today, 16:17
Joined
Jan 20, 2012
Messages
124
Thanks for your response Plog. I'm afraid I don't know a lot of the programming and you lost me at aggregate...lol. I see what you're saying about it not being done at the table level. That makes sense. Like I said, haven't worked with this stuff in quite a while (and hope I never have to again once I get this done for my art biz purposes for this past year!).

I am having to look up the miles from my home to each vendor location every time I add a purchase (for supplies, tools, etc) and was just looking for a way to save that info without having to find it again and again.

I don't really know code very well, so not sure I can accomplish this the way you suggested, but I do appreciate your trying to help! I may can figure out a way in queries or forms. The end result will have to be exported to Excel for my tax lady, but I don't like working in Excel as much as I do Access, so there's the reason for doing it this way.

Anyway, thanks again!
 

Users who are viewing this thread

Top Bottom