Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Performing Calculation on Calculation

Discussion in 'Information Technology' started by Jennifer Cali, Jul 28, 2006.

  1. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    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...


    --
    Thank you! - Jennifer
     
  2. Loading...

    Similar Threads Forum Date
    Controversial Kashmiri Muslim Performing Kirtan: Tasleema Langoo (Blog of Navdeep Singh Dhillon) Hard Talk Nov 28, 2010
    General Can successful executives raise high performing children? Hard Talk Feb 2, 2007

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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

    "Jennifer Cali" wrote:

    > 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...
    >
    >
    > --
    > Thank you! - Jennifer
     
  4. Nicholajlg

    Nicholajlg
    Expand Collapse
    Guest

    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!


    Jennifer Cali wrote:
    > 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...
    >
    >
    > --
    > Thank you! - Jennifer
     

Share This Page