Performing Calculation on Calculation

[Guest]

I have a tough situation in a recruiting database
1. A table stores position status (active/closed), location (there are 3
locations), day position became vacant, and day position was filled.
2. A form tied to this query has a calculation in it that determines the
number of days vacant for a position. On the form, each record is
individually displayed and the number of days vacant is autocalc'd by
subtracting dtmDateVacant from dtmDateFilled.
3. I need to create a report showing the average number of days vacant for
all active positions by location. So...there are two criteria: (1) must be
active, (2) must be in location A - I'll then repeat this calc for the other
two locations.

I'm stuck b/c I don't know how to write the statement in the query that
would pull this info. I'm currently using an aggregate function to calc other
info (ex: Sum(IIf(chrMgrType="GMB" And chrArchive="Active" And
chrActualCode="LOA" And (chrZone="Zone 11" Or chrZone="Zone 12"),1,0)) AS
GMB1_LOA), but I don't know how to do it to calculate a field that is already
a calculation itself b/c it's not stored anywhere...

 

[Guest]

Jennifer:

Two possible approaches:

1. You can return just the Active status rows and do the date arithmetic in
a query and base the report on that. Then do the averaging in the report.
The query would go something like this:

SELECT Location, dtmDateFilled-dtmDateVacant AS DaysVacant
FROM YourTable
WHERE Satus = "Active";

In the report based on this query group the report on Location in the
sorting and grouping dialogue. Make sure you opt to give the group a group
footer. If you only want to show the averages and no detail rows just leave
the detail section as zero height. In the group footer add a control bound
to the Location field and add an unbound text box with a ControlSource
property of:

=Avg([DaysVacent])

The report will list all locations with the average days vacant for each
whwre the sataus is active. If you want the option of reporting for just one
lovation or for all then add a parameter to the report's query like so:

SELECT Location, dtmDateFilled-dtmDateVacant AS DaysVacant
FROM YourTable
WHERE Status = "Active"
AND (Location = [Enter location (leave blank for all)]
OR [Enter location (leave blank for all)] IS NULL);

This will prompt for a location when the report is opened. If one is
entered the report will show the average for just that location, if its left
blank then all locations will be returned.

2. Alternatively you could do the whole thing in the query and base a
simple report on it:

SELECT Location,
AVG(dtmDateFilled-dtmDateVacant) AS AverageDaysVacant
FROM YourTable
WHERE Status = "Active"
AND (Location = [Enter location (leave blank for all)]
OR [Enter location (leave blank for all)] IS NULL)
GROUP BY Location;

Ken Sheridan
Stafford, England

 

[Nicholajlg]

Hello

Is there a particular reason for using a form for this calculation over
a query? To create this in a query though, is relatively simple. first
calculate the number of days vacant in a query, essentially the same,
you just put Field Name: sum([DateVacant] - [DateFilled]). Include
labels designating as active and the location.

Then build a query off of this one. This time it's easy b/c every time
you enter a field, you can specify what the field should contain in
order for it to be included. ie for Location A, in the field named
Location, type in the name of Location A in the criteria space & the
only thing you'll get is Location A. You can build 3 separate queries
and calculate your averages for each location separately. Or, you can
load just this data into a table and calculate your averages in a query
based on this data. Either way, Putting the data in a table after
separating the three allows them to all be listed separately, in the
same column. The query built off of this table now contains every
piece of data you are looking for - and can be used in any form or
report to show all data together or to filter in any way you like.

Good Luck!