Solved

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

Posted on 2013-11-05
8
236 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

747 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

13 Experts available now in Live!

Get 1:1 Help Now