Solved

Adding a column of numbers and updating the database with that total

Posted on 2013-11-05
8
280 Views
Last Modified: 2013-11-15
This piggy backs off of my last question.  I was having some trouble getting it to ultimately work how I needed it to.  Probably because I needed to do it different anyway.  

For repeat people answering my questions, this will probably always have to do with this Logging database that I am hacking through.  With the said I will use the column names and such the way they are.

In my form I want to keep a running total: "Total Tree Footage" = "XXXXXX"
1. I added a column to the Tree table called "TotalTreeFootage".
2. In the log table there is a footage per log.  To get the tree footage total, I need to add all of the log totals.  Such as...
Tree 1
  Log 1 = 20
  Log 2 = 30
  Log 3 = 30
Total = 80
I need the form that I enter logs for to update the "TotalTreeFootage" column in the Tree database for the said Tree by ID.  Then have a requery of the "Total Tree Footage" to update based on the value in the tree database column "TotalTreeFootage"

Other notes.
To get to where I need to do this.
1. Open the database, select frmCustomers
2. Select a customer, hit View Projects
3. Select a project, hit View Trees
4. Select a Tree, hit View Logs
5. Type in a sample Length, sample Diameter
6. The Footage will autocalculate.  

After the footage autocaculates.....

Then I want the form to update tblTrees.TotalLogFootage to be the value of the said logs "Footage" figures added together. Then the form needs to requery tblTrees.TotalLogFootage and show that value in "Total Tree Footage" in the upper right hand corner.

Man that was a typing full.  I attached the database, seems like it would be much easier to get something that works.

Thank you!
Database1683.accdb
0
Comment
Question by:getwidth28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 84
ID: 39627021
I don't see a control named "Total Tree Footage" anywhere. I see "Tree Footage" and "Log Footage", but that's it.

In general you shouldn't store calculated values in your database, and this one is no exception. YOu have all the root elements, and a fairly simple calculation to produce the values, so just calculate it as you go.

I've added code to the Current event that does that for Tree Footage. I'll leave the Log Footage for you to work with.
Database1683.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39627373
Upload a sample database. Explain the steps to do and what to expect.
0
 

Author Comment

by:getwidth28
ID: 39627488
LSMConsulting.  That worked, except I was getting errors during a lot of things.  

First ErrorDebugger
I changed  this:
sql = "SELECT Diameter, Length FROM tblTrees WHERE ProjectID=" & Me.ProjectID
to
sql = "SELECT Diameter, Length FROM tblTrees WHERE ProjectID=" & Me.txtProjectID

and that took care of it.  The Text field was called txtProjectID.

I think that will do it for this.  I will test a little more then close it out.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:getwidth28
ID: 39627515
So the best practice in pretty much all calculations are better done one hand?  I assume the reason is that there are not any errors and some point in the data.  Like if I kept the total log footage in the database, then deleted a record but didn't have the code to recalculate the figure, that would lead to bad data.

I also thought that there was a practice to have it in the database for quick retrieval, server load, etc.?  Not that I have to worry about load with this database.
0
 
LVL 84
ID: 39629826
best practice in pretty much all calculations are better done one hand?
Best Practices dictate that you NOT store calculated values in the database, and instead calculate them as needed.

That said, there are valid reasons to store calculated values:

-- If you are dealing with extremely complex calculations, for example, which can take quite some time to perform, then you might consider storing calculated values in the database.
-- If you're creating an "archive" database (one where data will NEVER again be added).
-- If you have legitimate load concerns (which is very, very rare today).

There are others, of course, but in general you're far, far better off performing your calculations on the fly.
0
 

Author Comment

by:getwidth28
ID: 39634271
I thought I had commented my current issues a couple of days ago.  Shucks.  

After putting the code into frmLogs, I can't get it to work.  I tried for a few hours with different stuff.  Here are the screenshots.

Can you please see what I am doing wrong?

ErrorCodeDatabase1683.accdb
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39634607
If Diameter or Length can contain NULL values, then you'll need to compensate for that:

dTotal = dTotal + (Round((((Nz(rst("Diameter"),0) - 4) / 4) * Nz(rst("Length"), 0), 0))
0
 

Author Comment

by:getwidth28
ID: 39651321
Great thanks a bunch!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 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