473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

I got to be making calculations too complicated

I'm coming from a Corel paradox background and moving into an Access environment. So I'm struggling with something that I think is probably way simpler than I'm making it out to be.

Access 2007
WindowsXP SP2

What I have is a table in a subform that tracks dollars spent per project
There is sub sub form that breaks down each dollar amount per fund. For example: sewer/water/streets/parks. In the sub sub form the fields I'm having trouble with is Percentage and CalcValue. What is desired is for a project manager to be able to enter the percentage of the cost that is fund related and then have the CalcValue field become populated with the correct dollar amount. I want the data stored in its own table for other purposes, so that we can then find out what all the Sewer related costs were for example.

So, if a street rehab project had a project survey cost of $100,000.00 and 20% was related to Sewer, 30% was Water and 50% was Street related then the CalcValue fields should automatically populate $20,000, $30,000, and $50,000 in the child table respectively.

I tried making a query object, which was ok but wouldn't allow me to add new records. No good.

I made a table that I could populate but couldn't figure out how to get expression builder to sucessfully recognize a related field in a higher level sub form and then calculate off of said field.
=[CalcValue]=[Funding]![Amount]*[Funding Source]![Percentage]

So I moved onto VBA script trying to combine the two; a table and an update query that would run when the Percentage field was departed or changed. And after some hunting on the forums here I came up with this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Percentage_Change()
  2.  
  3.       On Error GoTo Err_Update_Click
  4.  
  5.           Dim stDocName As String
  6.  
  7.          stDocName = "CalcForFundSrcSubform"
  8.          DoCmd.OpenQuery stDocName
  9.          Me.Requery
  10.  
  11. Exit_Update_Click:
  12.     Exit Sub
  13.  
  14. End Sub
  15.  


[QUERY]
UPDATE Funding INNER JOIN [Funding Source] ON Funding.FundingID = [Funding Source].FundingIDLink SET [Funding Source].CalcField = [Funding]![Amount]*[Funding Source]![Percentage]
WHERE ((([Funding Source].CalcField) Is Null));
[/QUERY]


So, after all this. I'm getting to think two things. First, I'm missing Paradox; I'd be so done by now. Second, I'm guessing that I'm making this waaaay to complicated. Is there something that I'm missing? What am I doing wrong.

Mark
Sep 14 '07 #1
7 2291
I have figured out part of the problem. Microsoft's security tripped me up. It was protecting me and making so none of the code was running.

I'm now back to trying to make an expression work. I'm putting it "on click" so I can verify when the code runs.

Expand|Select|Wrap|Line Numbers
  1. =[Forms]![ProjectBudgetForm]![FundingSubform].[Form]![Amount]*[Percentage]
  2.  
Sep 14 '07 #2
nico5038
3,080 Expert 2GB
Hi Mark,

Basically we don't store result like the calculated value in a table because of "data-redundancy".
Just imagine that the sewer price goes up and you "forget" to correct the calculated value :-(

The general solution is to use queries to calculate the result dynamically from the "basic" data. This will also allow the additon of any number of "sewer" and/or other part.

Getting the idea ?

Nic;o)
Sep 14 '07 #3
Hi Mark,

Basically we don't store result like the calculated value in a table because of "data-redundancy".
Just imagine that the sewer price goes up and you "forget" to correct the calculated value :-(

The general solution is to use queries to calculate the result dynamically from the "basic" data. This will also allow the additon of any number of "sewer" and/or other part.

Getting the idea ?

Nic;o)
Yep, sure do. The amount of the bills aren't likely to change so I hadn't considered that. But you are correct, it is possible.
And again, little differences I am learning. In Paradox the form is built so that if you changed a value in a related field all the related values would change downstream. Not unlike a spreadsheet. It's one of the differences I'm getting used to.
This is exactly the clarification I'm needing, Thank you!

So, I set up a control, not bound to anything, stack it in with the other fields (I'm still getting over how the interface operates, it is odd) and have made it a straight calculated field. It achieves the desired results, as long as the correct record is selected before you push the (+) symbol next to the record.

So how do I force focus to a record when a user push the plus symbol next to the chosen record?
Sep 14 '07 #4
nico5038
3,080 Expert 2GB
Hmm, I guess you're using linked tables as you mention the + sign.

I normally start with defining a "normal" form, in your case based on a project, and add a subform by pressing that button in design mode.
Next the Access wizard will propose to link the subform by a field and you can acceopt that proposal to have Access sync the main (project) and the sub (costs).

The subform will be by default in datasheet format, but when you change it to "Continuous" (see the properties under the Format tab), then you can use a footer that's holding =Sum([detailsectionfieldname]) to get totals.

Nic;o)
Sep 15 '07 #5
Thanks. But I think what I need is to get more familiar with the Access environment and how to force record focus.

Here's the thing, I have a form with two sub forms by linked fields.

Base(projects) >> Sub(costs) >> Sub Sub(cost breakdown)

The base form is simply that, it shows project name, which displays the first sub form by way of a datasheet of costs below it. Alongside each datasheet entry is a plus (+) symbol which will display another datasheet (sub-sub form) of breakdown when pressed.

Unfortunately if there is more than one cost entry (middle form) and you just click on the plus symbol of the last entry what happens is the form expands and then the calculated fields in the sub-sub calculate form the incorrect sub form record; as the focused record in the sub form is still the first record. Clicking on the plus symbol does not change record focus.

So to get correct values on the sub-sub form, a person needs to first click the record to attain focus and then the plus symbol to assure correct calculations.

What I want to know is how do I make it so that if a user just selects the plus symbol, record focus is set to the record alongside the corresponding plus symbol?

Hmm, I guess you're using linked tables as you mention the + sign.

I normally start with defining a "normal" form, in your case based on a project, and add a subform by pressing that button in design mode.
Next the Access wizard will propose to link the subform by a field and you can acceopt that proposal to have Access sync the main (project) and the sub (costs).

The subform will be by default in datasheet format, but when you change it to "Continuous" (see the properties under the Format tab), then you can use a footer that's holding =Sum([detailsectionfieldname]) to get totals.

Nic;o)
Sep 17 '07 #6
SOLVED.

So my problem was not with forcing focus, but rather the expression that calculated the field in the datasheet.

I had this before:
=[Forms]![ProjectBudgetForm]![FundingSubform].[Form]![Amount]*[Percentage]

Which did the job. Unfortunately as a calculated field on a sub sub form it started to return some strange results as I bounced around on the sub form.

What I wanted was the ability to find a conditional value, that is to say, I need the dollar amound from this table when the ID fields match. I found the command DLookUP:
=DLookUp("[Amount]","Funding","[FundingID]=" &[FundingIDLink])*[Percentage]

Now my forms are showing the correct values when and where I need them to.
Sep 17 '07 #7
I'm coming from a Corel paradox background and moving into an Access environment. So I'm struggling with something that I think is probably way simpler than I'm making it out to be.

Access 2007
WindowsXP SP2

What I have is a table in a subform that tracks dollars spent per project
There is sub sub form that breaks down each dollar amount per fund. For example: sewer/water/streets/parks. In the sub sub form the fields I'm having trouble with is Percentage and CalcValue. What is desired is for a project manager to be able to enter the percentage of the cost that is fund related and then have the CalcValue field become populated with the correct dollar amount. I want the data stored in its own table for other purposes, so that we can then find out what all the Sewer related costs were for example.

So, if a street rehab project had a project survey cost of $100,000.00 and 20% was related to Sewer, 30% was Water and 50% was Street related then the CalcValue fields should automatically populate $20,000, $30,000, and $50,000 in the child table respectively.

I tried making a query object, which was ok but wouldn't allow me to add new records. No good.

I made a table that I could populate but couldn't figure out how to get expression builder to sucessfully recognize a related field in a higher level sub form and then calculate off of said field.
=[CalcValue]=[Funding]![Amount]*[Funding Source]![Percentage]

So I moved onto VBA script trying to combine the two; a table and an update query that would run when the Percentage field was departed or changed. And after some hunting on the forums here I came up with this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Percentage_Change()
  2.  
  3.       On Error GoTo Err_Update_Click
  4.  
  5.           Dim stDocName As String
  6.  
  7.          stDocName = "CalcForFundSrcSubform"
  8.          DoCmd.OpenQuery stDocName
  9.          Me.Requery
  10.  
  11. Exit_Update_Click:
  12.     Exit Sub
  13.  
  14. End Sub
  15.  


[QUERY]
UPDATE Funding INNER JOIN [Funding Source] ON Funding.FundingID = [Funding Source].FundingIDLink SET [Funding Source].CalcField = [Funding]![Amount]*[Funding Source]![Percentage]
WHERE ((([Funding Source].CalcField) Is Null));
[/QUERY]


So, after all this. I'm getting to think two things. First, I'm missing Paradox; I'd be so done by now. Second, I'm guessing that I'm making this waaaay to complicated. Is there something that I'm missing? What am I doing wrong.

Mark
Dear Sir/Madam

I want learn programming with Corel Paradox 12, Would help me ?

My email address "appajar789@gmail.com"

Pl replay

thanks
Feb 8 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: lduperval | last post by:
Hi, I`m trying to do date calculations in three types of time zones: local, GMT and specified. The issue I am facing is that I need to be able to specify a date in the proper time zone, and I`m...
5
by: iminal | last post by:
I am trying to make a very simple program and am very new to the whole programming thing. my program is supposed to ask a user for any time in the for format XX:XX:XX and then ask for a time...
3
by: brian kaufmann | last post by:
Hi, I had sent this earlier, and would appreciate any suggestions on this. I need to make calculations for unemployment rate for three different data sources (A,B,C) for many countries and age...
2
by: chudson007 | last post by:
This is a very general quastion..... I regularily take data extracts from a SQL server database drop them in excel and run a macro on the data. The macro does nothing more complicated than...
3
by: AZKing | last post by:
Hello, I am trying to create a query to calculate certain fields in from a table. The calculations are pretty simple, they are just counts and sums. Once I run it I get the following error...
12
by: yawnmoth | last post by:
http://www.frostjedi.com/terra/scripts/demo/samesize.html I'd like to make inner1 and inner2 have the same height but am not sure how. I don't want to explicitly set a height in px or anything...
1
by: BUmed | last post by:
Normally my forms are simple, but not this one. I need to do some calculations in a form and I would normally do them in query but this is more complicated. I have a form that representation of a...
50
by: Juha Nieminen | last post by:
I asked a long time ago in this group how to make a smart pointer which works with incomplete types. I got this answer (only relevant parts included): ...
9
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.