Using your query

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

tighe wrote:
overall i guess my best solution is running sum buit i cant get the right
results, for each period no less adding in a 12 month constraint.

Jeff:right but i guess my problem is i dont know how to werite it to get the
required answers. all my tries do not get the expected result.

Karl: i did, http://support.microsoft.com/kb/208714, but my result from
DSum("[HiredNum]","Firm_Count_Hired_Fired",[Firm_Count_Hired_Fired]![Hired.CurDate]=[Firm_Employee_Count_time].[OccurDate])
, coming from the table below.is not running but i get the total sum for
every record(row) in the result.

John: it should be but i did format it :Format([myDate],"yyyymm")
current table Curdate is the date for the hiring/firing records, you will
notice some are blamk where no action took place in that month:
OccurDate EmployeeCount Fired.CurDate Hired.CurDate HiredNum FiredNum
200910 1 200910 1 0
200911 1 0 0
200912 1 0 0
201001 1 0 0
201002 1 0 0

i font have anything except the Dsum above, the FirmNum was incorrectly
labeld and is really FiredNum, as seen above.

hope this additional information helps, maybe ive over complicated the whole
matter. the end result i need is YearMonth, total employess, HiredNum,
FiredNum, Running sum for twelve months, if that sum goes over 10 employee if
under 33.3 or over 30% if over 33.3 employees. last the last part can
probably be done with conditional formatting.

also the above table is really a query:SELECT
Firm_Employee_Count_time.OccurDate, Fired.CurDate, Hired.CurDate,
Firm_Employee_Count_time.EmployeeCount, nz([fired].[Firm],0) AS FiredNum,
nz([hired].[Firm],0) AS HiredNum
FROM (Firm_Employee_Count_time LEFT JOIN Fired ON
Firm_Employee_Count_time.OccurDate=Fired.CurDate) LEFT JOIN Hired ON
Firm_Employee_Count_time.OccurDate=Hired.CurDate
GROUP BY Firm_Employee_Count_time.OccurDate, Fired.CurDate, Hired.CurDate,
Firm_Employee_Count_time.EmployeeCount, nz([fired].[Firm],0),
nz([hired].[Firm],0);


"John Spencer" wrote:

Is OccurDate a date field? Or a number field? Or a textfield?

Also what is CurDate? and what table is it in? You might want to post the SQL
text of the query you are currently using.

For instance you have FirmNum in the columns you posted. If you are trying to
do this by firm, then we need to know that also.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

tighe wrote:
i feel a little retarded i can’t pull the available information together to
get the result i need. i have looked at
http://support.microsoft.com/kb/208714 and a number of other posts, but i
have not been able to get anything but the most recent sum for all Occur
Date. That equation is Expr1:
DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]=
[OccurDate]")=28 for every record.

i need to be able to get the gross hired/fired for an occur date but only
the last twelve months.
currently i have the first 4 columns below and used excel to show what the
answers would be:
OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months
200710 1 1 0 1
200711 1 0 0 1
200712 1 0 0 1
200801 1 0 0 1
200802 1 0 0 1
200803 1 0 0 1
200804 1 0 0 1
200805 4 3 0 4
200806 4 0 0 4
200807 4 0 0 4
200808 5 1 0 5
200809 5 0 0 5
200810 6 1 0 5
200811 7 1 0 6
200812 10 3 0 9
200901 10 0 0 9
200902 11 1 0 10
200903 13 2 0 12
200904 14 1 0 13
200905 15 1 0 11
200906 16 1 0 12
200907 17 1 -1 12
200908 16 0 -2 9
200909 16 2 -1 10
200910 17 2 0 11
200911 20 3 0 13
200912 21 1 0 11
201001 24 3 0 14
201002 24 0 0 13

TIA
.