455,490 Members | 1,774 Online
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

# Calculate net amount based on conditions

 100+ P: 418 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 Replies

 Expert 2.5K+ P: 2,653 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

 Expert Mod 15k+ P: 31,709 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

 100+ P: 418 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 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 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

 Expert Mod 15k+ P: 31,709 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

 Expert 2.5K+ P: 2,653 @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

 100+ P: 418 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

 Expert Mod 15k+ P: 31,709 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

 100+ P: 418 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

 Expert Mod 15k+ P: 31,709 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

 100+ P: 418 NeoPa: Very good suggestions. I will definitely do so. Many thanks. Jun 11 '09 #11

 Expert Mod 15k+ P: 31,709 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

 100+ P: 418 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

 Expert Mod 15k+ P: 31,709 How big is it? Jun 12 '09 #14

 100+ P: 418 Not very big, just 26KB in excel Jun 12 '09 #15

 Expert Mod 15k+ P: 31,709 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

 Expert Mod 15k+ P: 31,709 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

 Expert Mod 15k+ P: 31,709 FYI. Another copy is found at How to incorporate prior year expenses and maintain current balance. Jun 17 '09 #18