J
Jennifer Cali
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...
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...