473,288 Members | 1,743 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,288 software developers and data experts.

Calculate net amount based on conditions

418 256MB
Calculate net amount based on conditions
________________________________________
I need to calculate a net amount that is based on FedRatio. Normally the FedRatio is set to be 80% but there are a few instances where it varies depending on a GrantNo and ProjectNo. To convert APAmount to NetAmount, I will need to incorporate these criteria.

If I have 2 invoices for the APAmount $100 and $200 and these belong to two projects with two different fed ratio (80% and 90%) – I need to figure out how to calculate the Net Amount of $80 and $180. The grant number remains the same. If I create txtNetAmount in my report, what formula will be in its control? This refers to a control on report. Right now I have based the report on a query that is based on two tables (see below).


Table Name: tblAP
APAmount: Currency
GrantNo: Text
ProjectNo: Text

Table Name: tblGrantBudget
Fed Ratio : FedRatio (Percentage)

Any guidance anyone? Thanks.
Jun 11 '09 #1
17 2171
FishVal
2,653 Expert 2GB
You ought to join your datasets in such a way that invoice amount and correspondent fedratio appear in the same record. Calculation could be done either via calculated field in the query or via formula in report control.
Jun 11 '09 #2
NeoPa
32,554 Expert Mod 16PB
As Fish says, add a calculated field to your query at the detail level.

Later on (in the report somewhere maybe) calculate the sum of the calculated field.

Does that make sense?
Jun 11 '09 #3
MNNovice
418 256MB
FishVal / NeoPa:

I understand what's being asked of but I don't know exact syntax of this condition. The SQL for the Query in question is given below. My question is if I were to include an argument "net amount = ApAmount * FedRatio but lookup project no, if project no is 61611 and fund no is 822 the fed ratio is 95% otherwise fed ratio is 80%"

I would prefer the argument to be more flexible as such that I don't have to mention any project number or fund number. When updating frmAP (based on tblAP), as soon as a fund and project number is entered it will automatically calculate the net amount by looking at the fed ratio that's residing in tblGrantBudget.

How do I do it? I am willing to make any changes to the query or to start from the scratch.

Many thanks.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblECHO.ECHOID, tblGrantFunds.GrantFundID, tblGrants.GrantNo, tblProjects.ProjectNo, tblAP.MTAP, tblAP.SPAP, tblAP.InvoiceNo, tblVendors.VendorName, tblAP.ContractNo, tblECHO.ECHONo, tblGrants.FedRatio, tblECHO.DtSubmitted, tblAP.ApAmount, tblECHO.DtReceived, tblAP.APID, [ApAmount]*[FedRatio] AS FedAmount, Round([FedAmount]+0.0001) AS NetAmount
  2. FROM tblVendors INNER JOIN (tblGrants RIGHT JOIN (tblGrantFunds INNER JOIN (tblECHO INNER JOIN (tblAP INNER JOIN tblProjects ON tblAP.ProjectID = tblProjects.ProjectID) ON tblECHO.ECHOID = tblAP.ECHOID) ON tblGrantFunds.GrantFundID = tblAP.GrantFundID) ON tblGrants.GrantID = tblGrantFunds.GrandID) ON tblVendors.VendorID = tblAP.VendorID;
Jun 11 '09 #4
NeoPa
32,554 Expert Mod 16PB
It seems to me that you have a field [FedAmount] already available in your SQL that would be summed to produce the result you need.
Jun 11 '09 #5
FishVal
2,653 Expert 2GB
@MNNovice
As NeoPa said you already have [FedRatio] fetched in your query.
As far as I could guess from the SQL you've posted [FedRatio] is a record in [tblGrants] and thus particular fedratio value belongs to particular grant.

Does it unambiguously define fedratio for a particular project/fund combination?

If no, then you need to redesign your table structure in order to obtain desired association of project/fund with fedratio.
Jun 11 '09 #6
MNNovice
418 256MB
FishVal:

I am afraid I will have to add tblGrantBudget and start a new Query. The problem I have is that tblGrantFund does not tie with this table which is why I had to go with tblGrant. So far my first attempt failed because it resulted in ambiguous joint ...

Shall keep you posted if and when I make some progress. Meanwhile here is the table structures for those two tables. Many thanks.

tblGrantBudget
BudgetID
GrantID
AccountID
RevDescr
FundID
OrgID
ProgramID
SubClassID
ProjectID
Budget
Change
FedRatio
LocalMatch

tblGrants
GrantID
GrantNo
GrantTitle
CFDANo
FundSource
FPCode
FundID
AwardDt
StartDt
EndDt
FedRatio
LocalRatio
GrantTotal
Jun 11 '09 #7
NeoPa
32,554 Expert Mod 16PB
As you have [FedRatio] (as well as some other fields) in both these tables, this is clearly not a normalised structure M.

I know this has been said before, but you really need to get this sorted before trying to design anything with it. It's hard to overstress this point. You will continue to struggle with problems if this is not sorted out first.
Jun 11 '09 #8
MNNovice
418 256MB
NeoPa:

Oh boy!! seems like I am in more trouble now....(LOL). I will have to look at tblFund before I restructure tblGrant because these two are tied on a crucial issue which is the grant number. I will look into it before progressing further.

Thanks.
Jun 11 '09 #9
NeoPa
32,554 Expert Mod 16PB
Here's a tip M. It may be quite a lot of work, but for a project of this size it may be well worth the effort :

Write down in words an explanation of why you have organised it as you have. Explain your structure to your own satisfaction. This may not be required by anyone (although adding it to the documentation may win you some extra brownie points), but it will help you to get a better grip on what you're doing.

If this takes up to a full working day (7 or 8 hours) then you can consider the time well-spent. I expect that it will more than pay for itself over time. Not limited to this project either.
Jun 11 '09 #10
MNNovice
418 256MB
NeoPa:
Very good suggestions. I will definitely do so. Many thanks.
Jun 11 '09 #11
NeoPa
32,554 Expert Mod 16PB
You can tell me that when you're looking back on doing the work. If, that is, you still have any hair left un-pulled out :D

Seriously, this won't be an easy task, but should be worth the effort when you come out the other side.

Good luck.
Jun 11 '09 #12
MNNovice
418 256MB
NeoPa:

I am cautiously approaching this with very little confidence in my own ability. I just finished typing it up. Where do you think I should post it for some feedback? Thanks.
Jun 12 '09 #13
NeoPa
32,554 Expert Mod 16PB
How big is it?
Jun 12 '09 #14
MNNovice
418 256MB
Not very big, just 26KB in excel
Jun 12 '09 #15
NeoPa
32,554 Expert Mod 16PB
Email it to me & I'll look at it.

Remember though, this (the producing of it) is mainly for your benefit. Not about getting it assessed. Your project will show whether it's a good job or not.

PS. It's only just past 18:00 here now and I'm off for the weekend. I can still connect from home though for later ;)
Jun 12 '09 #16
NeoPa
32,554 Expert Mod 16PB
M,

I have looked at it and I will probably look again.

Unfortunately, it is quite minimalistic, and doesn't seem to illustrate a clear understanding yet of all the rules. This may either be due to a lack of understanding or more simply just that you haven't expressed your understanding fully.

I would certainly recommend keeping this, as well as maybe building upon it when you have time or maybe flashes of clarity.

I can't give this top priority I'm afraid, but will try to get a better understanding of your project when time allows.
Jun 17 '09 #17
NeoPa
32,554 Expert Mod 16PB
FYI. Another copy is found at How to incorporate prior year expenses and maintain current balance.
Jun 17 '09 #18

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

Similar topics

3
by: carla | last post by:
I am using Access 2000 and have a table, tblCurrent. That table contains several fields - three of those fields are , and . What I want to accomplish in my data entry form, is first entering the...
7
by: Jurek | last post by:
I have 10+ experience in C/C++ - mostly automation and graphics. I have never written any business apps though. Recently I've been asked to write a simple report that would calculate sales...
7
by: carterweb | last post by:
This is how I do it now. 1. Determine the dimensions of the rectangle. 2. Set a my font size to a fixed maximum size. 3. Apply the font my string and measure the string using the graphics...
3
by: Michelle Anderson | last post by:
I have an access form. In the form, it co ntains 3 fields : Type, hours, and Amount. Field "Type" is a combo box and it contains 2 values: Standard and Rush Field " Hours" is just a numeric...
5
by: Michael | last post by:
Hi. I need dinamically calculate input text field based on parent static TD before showing content of input. Please, advice. Michael
0
by: rabraham | last post by:
I have a commission report I’m working on and it is causing to lose hair fast. I need help customizing this commission report. The current report has a couple conditions: Condition 1: Pay 2%...
9
by: Desyree | last post by:
I am putting together a database with the fields: 1. Number of Projects 2. Project (Name) 3. Project Contact 4. Company 5. Location (City, State) 6. Last Contact Date 7. Mw/g 8. Project Type
3
by: Libber39 | last post by:
Hi everyone, Have a query on how to calculate the amount of weeks and days contained in a number in an access query. ie: the difference in days between 2 dates amounts to 17 days. I want to now...
1
by: MNNovice | last post by:
I need to calculate a net amount that is based on FedRatio. Normally the FedRatio is set to be 80% but there are a few instance where it varies depending on a GrantNo and ProjectNo. To convert...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.