EDIT: Not solved entirely...some math errors...will post the solution when I get it.
EDIT 2: Solved it! Query was pulling for every fiscal and calculating negatives. I added code to specify the fiscal year, and to not include contracts falling entirely outside the specified FY.
Well, I solved it myself. To recap for any archaeologists...
1) Built a query that multiplies the first three (qryOrderAnnualCost) that normalizes the data into annual numbers. Here's the SQL:
Code:
SELECT Order.Contract, OrderProduct.UnitCost, OrderProduct.Quantity, InvoicePeriod.InvoicePeriodID, InvoicePeriod.AnnualNumber, Order.ContractStart, Order.ContractEnd, [Quantity]*[UnitCost]*[AnnualNumber] AS TotalFROM InvoicePeriod, [Order] INNER JOIN OrderProduct ON Order.OrderID = OrderProduct.OrderID
WHERE (((Order.Contract) Is Not Null) AND ((InvoicePeriod.InvoicePeriodID)=[Order].[InvoicePeriodID]));
2) Built a query that calculates the percentage of the year as a calculated field (qryOrderFYCost), then multiplies that percentage against the normalized total from qryOrderAnnualCost. Here's the SQL:
Code:
SELECT FiscalYear.FYID, FiscalYear.FYStart, FiscalYear.FYEnd, qryOrderAnnualCost.ContractStart, qryOrderAnnualCost.ContractEnd, qryOrderAnnualCost.Total, IIf([ContractStart]<=[FYStart] And [ContractEnd]>=[FYEnd],1,IIf([ContractStart]<[FYStart] And [ContractEnd]<[FYEnd],DateDiff("d",[FYStart],[ContractEnd])/(DateDiff("d",[FYStart],[FYEnd])),IIf([ContractStart]>[FYStart] And [ContractEnd]>[FYEnd],DateDiff("d",[ContractStart],[FYEnd])/(DateDiff("d",[FYStart],[FYEnd])),0))) AS FYPercentage, [Total]*[FYPercentage] AS FYTotal, qryOrderAnnualCost.DepartmentID, qryOrderAnnualCost.SubAccountIDFROM qryOrderAnnualCost, FiscalYear
WHERE (((FiscalYear.FYID)=[txtFYID]) AND ((qryOrderAnnualCost.ContractStart)<=[FYEnd]) AND ((qryOrderAnnualCost.ContractEnd)>=[FYStart]));
Just have to work out passing the parameter from the form, and I should be good.