Referencing Fields from non-current records

[ckirmser]

Another problem has thrust its ugly head into my personal universe.

I have a report to make that is to show a break down of employees hired in each month since Oct. '05 and to show what percentage of the total for that month remain.

I have created one query that grabs each of the relevant personnel;

SELECT Right(Str(Year([dbo_personnel]![originalapptdate])),4)+" -"+Str(Month([dbo_personnel]![originalapptdate]))+" : "+MonthName(Month([dbo_personnel]![originalapptdate]),True)+" '"+Right(Str(Year([dbo_personnel]![originalapptdate])),2)+" Hires" AS hire_month, [dbo_personnel]![lname]+"; "+[dbo_personnel]![fname]+IIf(IsNull([dbo_personnel]![mname]),""," "+Left([dbo_personnel]![mname],1)+".") AS name, dbo_personnel.badge, dbo_personnel.originalapptdate, IIf(IsNull([dbo_personnel]![dateoftermination]),[dbo_personnel]![assignedorg],"EOS'd") AS EOS
FROM dbo_personnel
WHERE ((Not ((dbo_personnel.badge) Like "R*" Or (dbo_personnel.badge) Like "9*")) AND ((dbo_personnel.originalapptdate)>=#10/1/2005#))
ORDER BY dbo_personnel.originalapptdate;

I've saved this query as "2-yr Service Check Baseline."

And then this query that does the count;

SELECT [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") AS EOS, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS
FROM [2-yr Service Check Baseline]
GROUP BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active")
ORDER BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active");

The output from the second query stores the Active count on odd numbered records and the terminated ones on the even numbered records.

So, to get the percent left, I'd need to sum the two count fields from the two relevant records and then divide this into the Active count.

But, in the report, how do I reference a field from a record other than the current one?

Thanx in advance for any help!

 

[ckirmser]

So, is there no way to perform a calculation using data from two different records in a query?

 

[namliam]

You could do it the hard way, I am not sure about a report doing it

Create a simular query but like so:

SELECT [2-yr Service Check Baseline].hire_month, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS
FROM [2-yr Service Check Baseline]
GROUP BY [2-yr Service Check Baseline].hire_month;

Save this query.

Now create a third query to join your query and min on the month. Now you have the total and the "part" total on one line. Now it is a simple matter of deviding to get your %.

 

[ckirmser]

Xnaht, namliam!

I'll give that a try and see how it works...