Example SQL query for extracting timephase data from Project datab

From: E. Froma (Froma_at_discussions.microsoft.com)
Date: 09/18/04

  • Next message: Grant Lawes: "Date Extraction" 
    Date: Sat, 18 Sep 2004 08:53:02 -0700

    After extensively searching the web for sql-examples on how to extract
    timephase data from Project database tables I found that there was very
    little sample material around. It took me hours of work to construct a query
    to extract the timephase data for a given assignment. I hope this example
    will help others trying to achieve the same.
    To use this query simply save a project (2003) as an access-database. (Be
    sure the option "Expand timephased data in the database" on the
    Tools/Options-menu is checked.) This example makes use of the custom field
    "Resource Code" which can be accessed in the "Resource View" of Project. With
    this query the completed work (=Assignment Actual Work) for a specific task
    and resource are determined for a specific week. Replace the value "310" in
    the query with your own valid Resource Code and the value "161" with a valid
    TaskID. You may also want to change the dates. Create a new query in Access,
    click on the SQL-view and paste the query in the window. Execute the query.
    To see a graphical presentation of the links of the tables simply click on
    the Design-view in Access. The query:

    SELECT
    SUM((IIf(IsNull([TD_VALUE1]),0,[TD_VALUE1])+IIf(IsNull([TD_VALUE2]),0,[TD_VALUE2])+IIf(IsNull([TD_VALUE3]),0,[TD_VALUE3])+IIf(IsNull([TD_VALUE4]),0,[TD_VALUE4])+IIf(IsNull([TD_VALUE5]),0,[TD_VALUE5])+IIf(IsNull([TD_VALUE6]),0,[TD_VALUE6])+IIf(IsNull([TD_VALUE7]),0,[TD_VALUE7])))/60000 AS [Completed Work (h)]
    FROM MSP_CONVERSIONS AS MSP_CONVERSIONS_1 INNER JOIN (MSP_TEXT_FIELDS INNER
    JOIN (((MSP_TASKS INNER JOIN (MSP_RESOURCES INNER JOIN (MSP_ASSIGNMENTS INNER
    JOIN MSP_TIMEPHASED_DATA ON (MSP_ASSIGNMENTS.PROJ_ID =
    MSP_TIMEPHASED_DATA.PROJ_ID) AND (MSP_ASSIGNMENTS.ASSN_UID =
    MSP_TIMEPHASED_DATA.TD_REF_UID)) ON (MSP_RESOURCES.PROJ_ID =
    MSP_ASSIGNMENTS.PROJ_ID) AND (MSP_RESOURCES.RES_UID =
    MSP_ASSIGNMENTS.RES_UID)) ON (MSP_TASKS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)
    AND (MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID)) INNER JOIN
    MSP_CONVERSIONS ON MSP_TIMEPHASED_DATA.TD_FIELD_ID =
    MSP_CONVERSIONS.CONV_VALUE) INNER JOIN MSP_STRING_TYPES ON
    MSP_CONVERSIONS.STRING_TYPE_ID = MSP_STRING_TYPES.STRING_TYPE_ID) ON
    (MSP_TEXT_FIELDS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID) AND
    (MSP_TEXT_FIELDS.TEXT_REF_UID = MSP_RESOURCES.RES_UID)) ON
    MSP_CONVERSIONS_1.CONV_VALUE = MSP_TEXT_FIELDS.TEXT_FIELD_ID
    WHERE (((MSP_CONVERSIONS.CONV_VALUE)=2 Or (MSP_CONVERSIONS.CONV_VALUE)=3)
    AND ((MSP_TEXT_FIELDS.TEXT_VALUE)="310") AND
    ((MSP_TIMEPHASED_DATA.TD_CATEGORY)=3) AND
    ((MSP_CONVERSIONS_1.CONV_STRING)="Resource Code") AND
    ((MSP_STRING_TYPES.STRING_TYPE)="Timephased Contour Type") AND
    ((MSP_TASKS.TASK_ID)=161) AND ((MSP_TIMEPHASED_DATA.TD_START)>=#8/16/2004#)
    AND ((MSP_TIMEPHASED_DATA.TD_FINISH)<=#8/22/2004 23:59:59#));

    To extract timephase data for a resource without regard to a specific task
    remove the constraints on the task number in the WHERE-clause of the query.
    To determine Remaining work for the assignment simply replace
    ((MSP_CONVERSIONS.CONV_VALUE)=2 Or (MSP_CONVERSIONS.CONV_VALUE)=3) with
    (MSP_CONVERSIONS.CONV_VALUE)=1

    Possible values for CONV_VALUE :
    1 Assignment remaining work
    2 Assignment actual work
    3 Assignment actual overtime work
    4 Assignment baseline work
    5 Assignment baseline cost
    6 Assignment actual cost
    7 Resource baseline work
    8 Resource baseline cost
    9 Task baseline work
    10 Task baseline cost
    11 Task percent complete

    Possible values for TD_CATEGORY:
    0 Task
    1 Resource
    3 Assignment

    For further information on timephase data search your computer (or the
    internet) for the following documents: pjdb.htm, projdb.htm, prjoledb.htm

    If you find this query helpful please post a reply on this topic.
    Sincerely,
    Edze Froma.

  • Next message: Grant Lawes: "Date Extraction"

    Relevant Pages

    • Re: Need Help with Date Query.....Finding days in between.
      ... IF you are doing all this in the query, post the SQL of the query. ... I have a project table that contains the project id and name, resource ... that contains the resource id and name, and an assignment table that links ... User opens report, the system asks for a start date and finish date for ...
      (microsoft.public.access.queries)
    • Need Help with Date Query.....Finding days in between.
      ... I am writing a resource utilization database. ... that contains the resource id and name, and an assignment table that links ... I need a query that will calculate the amount of days allocated ... User opens report, the system asks for a start date and finish date for ...
      (microsoft.public.access.queries)
    • Re: Updatable Query (trying to edit records)
      ... the query so some fields are lookup fields. ... The only reason the Staff table is ... CABudgetHrs to arrive at the budgeted $ for that assignment. ... two tables ClientAssignment and Staff. ...
      (microsoft.public.access.queries)
    • RE: Advice on Form Design
      ... One question I do have now though, I can make the query that relates to the ... you will want an employee table. ... It should be related to the task assignment table. ... I need some advice on how to go about a database I am trying to design. ...
      (microsoft.public.access.forms)
    • Re: Accounting system query
      ... But you can just as easily do it yourself, by going into the Properties of the form, and putting the appropriate entry in the Record Source property. ... If I understand you correctly, you want fields from the Credit Limit Check - Debtors 4 query to be included in the Record Source of the form, instead of the way the form was first set up. ... Similarly I have another form called [Assignment Requests]. ...
      (microsoft.public.access.queries)
    • Quantcast