Query to calculate # of set fees owed

[InformaticsNurse]

Good afternoon,



I am new to Access and I am building a resident and property tracking database for an my HOA to assist our bookkeeper with all of the owners and lots and what they owe. Here's some background, if interested: https://www.accessforums.net/showthread.php?t=59707

I have a Homeowner table (TMember), a Lot table (TLot), and an Ownership junction table (TOwnershipJunction) which has an OwnershipBegin and OwnershipEnd date fields.

I have created a query that that counts the number of lots the homeowner is associated to in the Junction Table as long as there is not a date in the OwnershipEnd field. I am trying to create a query that will calculate the number of HOA fees due every month for each owner. Essentially, if you own 1 or 2 lots you pay 1 HOA fee, but if you own 3 lots you pay 2 fees, 4 lots = 3 fees, 5 lots = 4 fees, etc...


SELECT TMember.LastName, TMember.FirstName, Count(TLot.LotID) AS CountOfLotID
FROM TMember INNER JOIN (TLot INNER JOIN TOwnershipJunction ON TLot.LotID = TOwnershipJunction.LotID) ON TMember.MemberID = TOwnershipJunction.MemberID
GROUP BY TMember.LastName, TMember.FirstName, TOwnershipJunction.OwnershipEnd
HAVING (((TOwnershipJunction.OwnershipEnd) Is Null));


I'm not sure how to get the next step to work. Basically I want to create FeesOwed: by evaluating [CountofLotID] and if the number is greater than 1, then subtract 1 and multiply the result by 25 (dollars), otherwise multiply by 25.



Thank you!

[CJ_London]

use the query you have written in another query and the formula would be

FeesOwedCountofLotID+(countofLotID>1))*25

countofLotID>1 will return 0 if it is 1 otherwise it will return -1

[InformaticsNurse]

Thank you for the quick reply. When I enter this in Field: in Design View and Run it, I am asked for to enter a parameter value by a small prompt. This is my first calculated field and I am unable to get it to work like tutorials suggest.

That is a very clever way to calculate the # of fees owed, at least to my MS Excel brain.

[CJ_London]

yeah - don't think it works in excel - you would get a true or false value which can't be evaluated as 0 or -1

with regards your prompt - what exactly does it say? it suggests you have a typo somewhere

[orange]

InformaticsNurse,

Seems the calculation for the query would be something like this

FeesOwed: Iif(CountOfLotsOwned>2,(CountOfLotsOwned -1)*25,25) 'only for Owners

We're still talking Access, right.