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!