Solved

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

Posted on 2013-11-05
8
246 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
 

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
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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

914 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

20 Experts available now in Live!

Get 1:1 Help Now