Task Text Fields in MS Access

[Hadi]

Hello Experts,

I have been testing the capabiliites of using MS Access to report on my
departments 200 projects. we use Server 2003 so I have our admin give me a
snap shot of the tables a few days ago. I have to say other than losing the
graphics it's alot easier to do rollup reports and such. I'm trying to
understand how the text fields that i have assigning to the tasks come across
in Access. There is one table called "MSP_TEXT_FIELDS" and the Primary Key is
Proj_ID. My question is where do these text values tie back to the actual
Tasks ( I assume thru TASK_ID field)??

thank you

 

[JulieS]

Hello Hadi,

See the following link. It contains the database schema for Project
2003.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Hadi]

Thank Julie,

I have been looking at the Project Database Schema which only shows the
PRJ_ID fields connected between the MSP_Text_Fields and the MSP_PROJECTS
tables. I then found a little note on the schema diagram talking about the
connection between the Text_Fields table and the MSP_Coversion table. I was
able to make a crosstab query that shows the Tasks and the corrected text
field values associated with them but so far it only shows the activities
that has a text field value assigned to it and not all of the activities. I
tried to change the relationships between the different fields but no luck.
Is there another Schema i can look at the really shows all of the
relationships between the tables inside any MS Project client file??

I hope i made my question clear

Hadi

 

[JulieS]

Hello Hadi,

Try the pjdb.htm file stored on your local machine. It is usually
stored in the Program File folder directly off C:\

I'm not sure exactly what you are trying to accomplish, but by
joining the MSP_Text_Fields table to the MSP_Tasks table using the
Text_Ref_UID in the MSP_Text_Fields to Task_UID in the MSP_Tasks
table allowed me to create a quick query. The SQL is below:

SELECT MSP_TEXT_FIELDS.TEXT_VALUE, MSP_TEXT_FIELDS.TEXT_REF_UID,
MSP_TASKS.TASK_NAME
FROM MSP_TEXT_FIELDS INNER JOIN MSP_TASKS ON
MSP_TEXT_FIELDS.TEXT_REF_UID = MSP_TASKS.TASK_UID;

I hope this helps.

Julie

 

[JulieS]

Sorry, just a quick comment. By changing the join to a right join,
it's possible to see Task Name for all tasks and text for only those
tasks with notes or text fields populated.

Julie

 

[Hadi]

Julie, thank you. let me show you what I have now in my query. the datasheet
view gives me a view similar to the data entry table in project but it was
only showing the activiites that have text fields assigned to them. I feel am
very close but cant show all of the tasks in any given projects.

TRANSFORM First(PMD_Update_vw_MSPS_MSP_TEXT.TEXT_VALUE) AS FirstOfTEXT_VALUE
SELECT PMD_Update_vw_MSPS_MSP_TASKS.TASK_UID,
PMD_Update_vw_MSPS_MSP_TASKS.TASK_ID, PMD_Update_vw_MSPS_MSP_TASKS.TASK_NAME
FROM PMD_Update_vw_MSPS_MSP_TASKS INNER JOIN (PMD_Update_vw_MSPS_MSP_TEXT
INNER JOIN PMD_Update_vw_MSPS_MSP_CONVERSIONS ON
PMD_Update_vw_MSPS_MSP_TEXT.TEXT_FIELD_ID =
PMD_Update_vw_MSPS_MSP_CONVERSIONS.CONV_VALUE) ON
(PMD_Update_vw_MSPS_MSP_TEXT.TEXT_REF_UID =
PMD_Update_vw_MSPS_MSP_TASKS.TASK_UID) AND
(PMD_Update_vw_MSPS_MSP_TASKS.PROJ_ID = PMD_Update_vw_MSPS_MSP_TEXT.PROJ_ID)
WHERE (((PMD_Update_vw_MSPS_MSP_TASKS.PROJ_ID)=1283) AND
((PMD_Update_vw_MSPS_MSP_TASKS.TASK_ID) Is Not Null) AND
((PMD_Update_vw_MSPS_MSP_CONVERSIONS.CONV_STRING) Like "*task*"))
GROUP BY PMD_Update_vw_MSPS_MSP_TASKS.PROJ_ID,
PMD_Update_vw_MSPS_MSP_TASKS.TASK_UID, PMD_Update_vw_MSPS_MSP_TASKS.TASK_ID,
PMD_Update_vw_MSPS_MSP_TASKS.TASK_NAME
ORDER BY PMD_Update_vw_MSPS_MSP_TASKS.TASK_ID
PIVOT PMD_Update_vw_MSPS_MSP_CONVERSIONS.CONV_STRING;

 

[Hadi]

Julie, am sorry if my last SQL statement was long. basically what i was
trying to say is after joining the REF_UID with the Task_UID, try joining the
PROJ_ID fields together, it only limits the list to the activities with text
values assigned to them. also, it will not allow me to change the join type
of the UIDs to a right one

thanks alot of your help this weekend

 

[Hadi]

sorry to keep sending these msgs but am finding stuff as am testing. back to
to the query you gave me. if i select the Task ID and Task Name and specify
the project ID then i get a list of ALL of the activities under that project
ID. However, if I add the Text_Value field from the MSP_Text_Fields table
then I'm getting all of the activities from the other projects as well since
the Task_UID repeats itself in every project. how do I show a list of all
activities and their text fields values for any single project or a group of
projects as i desire.

 

[JulieS]

Hi Hadi,

Have you tried setting criteria in the Project ID field to limit it
to only one project? Sorry in my quick test there was only one
project saved in the db, so I didn't have the problem of the same
task IDs in multiple projects.

Julie