Problems with Forecast Query

[Walter]

I get a "You tried to execute a query that does not include the specified
function" error.

I am working on a database to determine when I need to order fuel. The M1P1
is Month 1 Period 1 (the months are divided into 3 periods of 10 days eachs).
M1P1 is the amount of fuel the location will have on hand at that time. I
did this out for 4 months. The MaxAuthLvl is the Maximum Authorized fuel
level for the location. The ERQ is the Economic Resupply Quantity. My idea
was to take the fuel on hand (M1P1) and minus is from the (MaxAuthLvl) and if
it amount was less then the (ERQ) it would state (No) or (Yes) in my
ReorderM1P1 field. I copied the formula from a Microsoft Access 2003 Forms,
Reports, and Queries book. PS. I'm only half way done with the book.

ReorderM1P1: IIf([M1P1]-Sum([MaxAuthLvl])<=[ERQ],"Yes","No")

Below is the SQL.
SELECT tblDODAAC.DODAAC, tblDODAAC.LOCATION, tblProduct.Product,
tblImpData.MaxAuthLvl, tblImpData.ERQ,
IIf([M1P1]-Sum([MaxAuthLvl])<=[ERQ],"Yes","No") AS ReorderM1P1
FROM (tblDODAAC INNER JOIN (tblProduct INNER JOIN tbl16WeekQry ON
tblProduct.Product = tbl16WeekQry.Product) ON tblDODAAC.DODAAC =
tbl16WeekQry.DODAAC) INNER JOIN tblImpData ON (tblProduct.Product =
tblImpData.Product) AND (tblDODAAC.DODAAC = tblImpData.DODAAC)
GROUP BY tblDODAAC.DODAAC, tblDODAAC.LOCATION, tblProduct.Product,
tblImpData.MaxAuthLvl, tblImpData.ERQ;

 

[Dale Fye]

Walter,

The fact that you are trying to store data in fields is a key that your data
structure is probably flawed. I would assume that since you have a field
named M1P1, and that you indicated that you extended this for 4 months and 3
periods per month, that you also have fields (M1P2, M1P3, ..., M4P3). This
is classic spreadsheet design, and will complicate the process. If you post
your data structure, someone here might be able to help you restructure your
data before you go too far down the wrong path. My first guess is that you
need to replace your 12 fields (M1P1-M4P3) with two fields (InvMonth,
InvPeriod).