Solved

Another calculation for Tree database: getting the figures from another form

Posted on 2013-11-21
9
230 Views
Last Modified: 2013-11-22
As long as this site keeps giving points to the person who answers me, I can keep asking right?  :)  

Again, I tried some things but couldn't get anywhere on this one.  On a positive note I still continue to add things myself by figuring them out.

On frmLogs I added the column "Gross".  This calculation works and is Footage * Grade.
Footage * Grade
I need to add this Gross column up and put it on frmTrees.  Here:
frmTrees
I cannot figure out how to get access to the calculation of "=[txtFootage]*[cboGrades]" in frmTrees.  

Other Notes
In frmTrees, with your help, there is a dsum that adds the total log footage which is right and good.  I cannot just take that total and multiply it by something, because that something is different for each log.  See first picture, the multiplier is different.

Sample Result
For example.  Screenshot one has the gross of 320.0 and 23.8.  I need to add 320.0 and 23.8.  So that equals 343.8.  That number, 343.8, needs to go in  the Gross column for Tree # 4 in frmTrees.  Like so:
Example
Hopefully I have gave all the information needed.  Please let me know if you need anymore information.

and Thank you!
Database1683--2-.accdb
0
Comment
Question by:getwidth28
  • 5
  • 4
9 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39666728
Try this as the control source, including the  = .

=DSum("Grade * Footage","tblLogs","TreeID = " & [TreeID])

Open in new window


Same principal.  You're summing the product of Grade and Footage for a given tree ID.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39666745
For the type of data you are trying to pull, again I highly recommend reading Jim Dettman's article:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

It focuses largely on DLookup, but the same principles also apply to all of the domain functions (DSum, DMax, DAvg, etc).
0
 

Author Comment

by:getwidth28
ID: 39667013
Shucks, I forgot to read that.  I will right now, then try your answer.
0
 

Author Comment

by:getwidth28
ID: 39667080
I understood the article fully.  What is a little confusing is how does Access know to use the Grade and Footage value from the frmLogs form vs. the tblLogs table.  There is not footage or grade column in the table.

??

BTW: It worked perfectly, again.  Thank you
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 61

Expert Comment

by:mbizup
ID: 39667111
DSum, DLookup, etc allow you to look at sources off of the form... in this case, the Trees form does not have the needed data, so you want to query the Logs table  -- which has Grade and Footage.

The DSum command lets you do that, and also allows you to use criteria from the form in the sum.
0
 

Author Comment

by:getwidth28
ID: 39667341
I was wrong.  It seemingly worked at first but then after I added another tree, the "Total Gross" stays the same and doesn't cycle through the other trees and add them.  

The total shown is simply the record selected.  If I select the top record (Tree #2) the "Total Gross becomes 67.1.  If I select the other record (Tree # 1) the "Total Gross" becomes 18.

12
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39667513
I thought this question was about populating txtGross in the continuous form as you showed with the red number in the image in your original post.  ?

Is this a new/follow up question, or was that not what you were looking for?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39667517
The Total Gross textbox was not in the sample you posted.
0
 

Author Comment

by:getwidth28
ID: 39669382
Your are right.  I got to working on another thing and got them confused.  I will close this out and get the answer marked.

I believe I know how to get the next total that I need using 2x SQL queries with the do until eof but I can't get it figured out with a DSUM.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now