Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 5th, 2009, 05:27 PM
Registered User
 
Join Date: Nov 2008
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple Calculations on a form

Hi,

I am trying to do multiple calculations on a form. The form has records for a single user and how they commute eg.
Bus, bike, walk...

Currently I am using a contiuous form, but I am not sure if what I am trying to do will work.
I have a calculated field for each of the commute types (textbox on form), it works in that it calculates all of the records that match the various commute types for the current user. The problem is that I need to have a different calculation for each commute type. eg. Miles travelled*.05 for bike and miles travelled .08 for bus. The calculation I have written in the textbox obviously applies the same calculation to each commute type. Any ideas as to how or where I might write calculations for each of these commute types?

Thank you,
Prema
 
Old March 6th, 2009, 06:39 AM
Friend of Wrox
 
Join Date: May 2005
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default Multiple Calculations on a Form

Prema:
Without knowing how/what your DB tables currently are, I am making the following suggestions:
Create CommuteTypeTbl
Code:
 CommTypeID          PK Autonumber      Primary Key, numeric
 CommTypeName    Varchar (10)            Defines type of commuting, Walk,Bus,etc.
 CommMultiplier       Decimal                 (0.99-example)
The data in the CommuteTypeTbl would be:
Code:
PK     CommTypeName     CommMultiplier
1             Walk                .02
2             Bike                 .05
3             Bus                  .08
Use the CommTypeTbl as a lookup table on your form. The single user will have the capability of recording any and all commuting in a single day.

Hope this helps.
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
 
Old March 6th, 2009, 12:31 PM
Registered User
 
Join Date: Nov 2008
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default if only...

Thank you, I was excited about your suggestion, but then realized there is an important detail I left out that gets in the way. The calculation that I need comes after a previous calculation, like this:=([SumofCommuteDistance]*19.4)*0.05

The field sumofcommutedistance is in a query (not stored)
The first part of the equation is the same for all commute types, but it is the second part that needs to be different, therefore it can't be based in the underlying table, I believe. Is this clear? Would it be helpful to post the fields of the relevant tables and queries?

Thank you,
Paul
 
Old March 6th, 2009, 09:17 PM
Friend of Wrox
 
Join Date: May 2005
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default Multiple Calculations On a Form

Hi,
Yes it would be helpful to view the relevant tables before the queries. Also it would be helpful to understand the process for the single user. Then I will be able to understand the relationships and answer the questions: When is the SumOfCommutedDistance Calculated? And is the sum calculated by Commute type? Also, what does the single user inputto your form? My first thought was to define the formula for the SumOfCommuted Distance as a CommuteFunction (x, y) passing to it the commuted distance calculated previously.

Again, I hope this is helpful.


Thanks.
__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.
 
Old March 9th, 2009, 03:48 PM
Registered User
 
Join Date: Nov 2008
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is very helpful, thanks for taking the time!

Here is the process, tables and queries.

Commuter logs in on frmCommuterLogin (based on tblJctCommuterLocation [locationID, CommuterID, Address)

Commuter goes to frmDailyLog (based on qryDailyLog)
qryDailyLog has tblCommuterDetailHistory and tblLocation
the fields in the query are:CommuterID, commute type, commute date, and location all from tblCommuteDetailHistory; as well as Commute Distance, which is sored in tblLocation.

frmDailyLog allows the commuter to enter their name (which is actually pulled up when the form is opened from the login form), their commute type for that day, the date and the location they came from.

The commuter then closes this form and may choose to view their totals to date. This is done on frmCommuteTotals. This is where we are stuck.
frmCommuteTotals is currently based on qryCommuteLog
QryCommuteLog contains CommuterID and CommuteType from qryDailyLog and also CommuteDistance. Commute Distance is a sum field.

When the user opens this form they currently see a sum of the mileage for each commute type. eg. If they rode their bike 4 times, each time for three miles the field will show 12 miles for bike commute totals. What I am trying to do is figure out the amount of carbon saved by using alternative commute methods. For cycling the formula is miles traveled x average mpg (19.4) x .05. For bus commuting the formula will be different. The final part of the equation will be different. The continuous form has no way of knowing that I want a different combination for each commute type.

I hope this is more clarifying than confusing. Thanks again for your help.

Best,
Paul





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Form Fields tripster General .NET 2 October 17th, 2007 04:22 AM
1 form for multiple inputs Vince_421 Access VBA 2 February 6th, 2007 04:06 AM
Real Time Calculations on Form losartan Access 10 February 10th, 2005 11:47 AM
Multiple filter on a form chimp Access VBA 0 September 9th, 2004 02:59 AM
Multiple Form Instances in a single form? gman997 ASP.NET 1.0 and 1.1 Basics 1 March 30th, 2004 06:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.