Calculate net amount based on conditions

[MNNovice]

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.

[FishVal]

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.

[NeoPa]

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?

[MNNovice]

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.

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;

[NeoPa]

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.

[FishVal]

@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.

[MNNovice]

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

[NeoPa]

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.

[MNNovice]

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.

[NeoPa]

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.

[MNNovice]

NeoPa:
Very good suggestions. I will definitely do so. Many thanks.

[NeoPa]

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.

[MNNovice]

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.

[NeoPa]

How big is it?

[MNNovice]

Not very big, just 26KB in excel

[NeoPa]

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 ;)

[NeoPa]

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.

[NeoPa]

FYI. Another copy is found at How to incorporate prior year expenses and maintain current balance.