Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-05
8
Medium Priority
?
302 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 85
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 31

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host 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 85
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

670 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