Rule of Thumb

I know you didn’t bother with monster SQL SELECT and instead waited for me to come up with something chewable. And here it is. We reached the limits of Microsoft Query so we can now switch to Microsoft Access for some more flexibility.

First we’ll create a helper query in Microsoft Access called Resource Groups by copy-pasting the following SQL SELECT into query’s SQL view:

SELECT
    MSP_TEXT_FIELDS.TEXT_REF_UID,
    MSP_TEXT_FIELDS.TEXT_VALUE
FROM
    MSP_TEXT_FIELDS
        INNER JOIN MSP_CONVERSIONS ON
            MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE
WHERE
    (((MSP_CONVERSIONS.CONV_STRING)="Resource Group"));

Next, create a query in Microsoft Access called Resource Assignments by copy-pasting the following SQL SELECT into query’s SQL view:

SELECT
    [Resource Groups].TEXT_VALUE AS [Resource Group],
    MSP_RESOURCES.RES_NAME AS [Resource Name],
    MSP_TASKS.TASK_NAME AS [Task Name],
    MSP_ASSIGNMENTS.ASSN_WORK /
        MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY /
        1000 AS [Assignment Work]
FROM
    (((MSP_PROJECTS
        INNER JOIN MSP_ASSIGNMENTS
            ON MSP_PROJECTS.PROJ_ID=MSP_ASSIGNMENTS.PROJ_ID)
        INNER JOIN MSP_RESOURCES
            ON MSP_ASSIGNMENTS.RES_UID=MSP_RESOURCES.RES_UID)
        INNER JOIN MSP_TASKS
            ON MSP_ASSIGNMENTS.TASK_UID=MSP_TASKS.TASK_UID)
        LEFT JOIN [Resource Groups]
            ON MSP_RESOURCES.RES_UID=[Resource Groups].TEXT_REF_UID
WHERE
    (((MSP_PROJECTS.PROJ_NAME)="SOFTDEV"))
ORDER BY
    [Resource Groups].TEXT_VALUE,
    MSP_RESOURCES.RES_NAME,
    MSP_TASKS.TASK_NAME;

And that’s it. Now you get grab the assignments data into Microsoft Excel by selecting Resource Assignments query.

If you’re stuck, read the post on how to create a query in Microsoft Excel.