Solved

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

Posted on 2013-11-05
8
266 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

828 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