Help with an Access Report

[Hadi]

Hello Experts,

I am using MS Access to directly access the MS Project Server tables for a
report that I want to generate. I have everything working (tables, queries,
reports) but I dont think my query is very effecient. Basically I have a
crosstab query that uses the following fields as rows (PROJ_ID, PROJ_Name,
TASK_ID, TASK_Name, TASK_START_DATE) and CONV_STRING as a Column, and
TEXT_VALUE as a Value.

I want to report on certain project schedules on the Server and filter on a
couple of Activities that share a common name across all of the schedules (we
are using standard templates). I am using the PROJ_ID to callout the specific
schedules that I want to report on.

The problem I'm having is in the query design view under "Criteria" I am
having to list 3 criterias for each schedule. the First one is to callout the
specifc project ID that want AND to show TASK ID 0. I need the TASK ID 0
because this where all of the Enterprise Text Fields show where I have the
Project Manager's Name, the Area, the Progarm ID that are specific to our
line of work. I have about 120 schedules that I need to report on and you can
imagine that i need to have about 360 criterias in my query. I'm not much of
a SQL programmer but i'm sure there is an easier way to design this query.
This is a sample SQL code for 2 project schedules. Your help will save me a
ton of time

TRANSFORM First(MSP_TEXT_FIELDS.TEXT_VALUE) AS FirstOfTEXT_VALUE
SELECT MSP_TASKS.PROJ_ID, MSP_WEB_Projects.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_START_DATE
FROM ((MSP_TASKS INNER JOIN MSP_WEB_Projects ON MSP_TASKS.PROJ_ID =
MSP_WEB_Projects.PROJ_ID) LEFT JOIN _MSPS_MSP_TEXT_FIELDS AS MSP_TEXT_FIELDS
ON (MSP_TASKS.PROJ_ID = MSP_TEXT_FIELDS.PROJ_ID) AND (MSP_TASKS.TASK_UID =
MSP_TEXT_FIELDS.TEXT_REF_UID)) LEFT JOIN MSP_CONVERSIONS ON
MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE
WHERE (((MSP_TASKS.PROJ_ID)=1362) AND ((MSP_TASKS.TASK_ID)=0)) OR
(((MSP_TASKS.PROJ_ID)=1362) AND ((MSP_TASKS.TASK_NAME) Like "*Design/Eng*"))
OR (((MSP_TASKS.PROJ_ID)=1362) AND ((MSP_TASKS.TASK_NAME) Like "*Packet to
RMC*")) OR (((MSP_TASKS.PROJ_ID)=1246) AND ((MSP_TASKS.TASK_ID)=0)) OR
(((MSP_TASKS.PROJ_ID)=1246) AND ((MSP_TASKS.TASK_NAME) Like "*Design/Eng*"))
OR (((MSP_TASKS.PROJ_ID)=1246) AND ((MSP_TASKS.TASK_NAME) Like "*Packet to
RMC*"))
GROUP BY MSP_TASKS.PROJ_ID, MSP_WEB_Projects.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_START_DATE
ORDER BY MSP_WEB_Projects.PROJ_NAME
PIVOT MSP_CONVERSIONS.CONV_STRING;

Thank you

 

[Gary Chefetz]

Hadi:

You should ask your developer questions in the developer group.