:Project and Access. : outputing timephased data.

[stuart block]

Can anyone please help me.

I am using Project 2000 and Access 2003.

My situation is that I control between 50 and 100 projects.

Management want to be able to see information for resource level or project
level.

I have created a Project MPD file. this works fine.

What I want is a query in access or a vba macro that will give me, eg.

Project Name : Resource Name : Wk Start : Hours : Cost

Project1 : John Smith : 04june2005 : 36 : 360
Project1 : John Smith : 11june2005 : 36 : 360

etc.

So that I can generate query's / report to give management the information
that they require.

Thanks
Stuart

 

[Ed Morrison]

Stuart,
You may want to look into Project Server 2003. Its was designed to give you
the type of information you are looking for.

 

[stuart block]

thanks Ed for your quick reply. The company I work for are not willing to
spend out money on Project Server 2003.

So relooking at the problem, I need a VBA solution.

Thanks

 

[Ed Morrison]

If the schema is the save as the Project part of the Project Server 2003
database, then try the following query:

select p.proj_name, r.res_name, min(assn_start_date) start_date,
sum(assn_work/60000) as work, sum(assn_work/60000) * avg(rr_std_rate) as
cost
from msp_projects p
inner join msp_resources r on r.proj_id=p.proj_id
inner join msp_tasks t on t.proj_id=p.proj_id
inner join msp_assignments a on a.task_uid=t.task_uid
and a.proj_id=t.proj_id
inner join msp_resource_rates rr on rr.res_uid=r.res_uid
and rr.proj_id=r.proj_id
where res_name is not null
group by p.proj_name, r.res_name

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[stuart block]

Thanks Ed, but is for a query in Access ? As I don't have project server
2003, only project 2000.

Stuart

 

[Ed Morrison]

I don't have Project 2000 but the Access data structure in 2003 looks it
will work. Try it out. If it doesn't work try and tweak the query.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[stuart block]

Thanks Ed,

Your answer looks like SQL, I copied this in to the SQL view of a query. But
as soon as it is run then it falls over on the first line. with this error
message.

Syntax error (missing operator) in query expression
'min(assn_start_date)start_date'

Regards SB

 

[Ed Morrison]

I wrote this for SQL 2000. You may have to change a the syntax slightly for
Access. Access may need an explicit 'as' for a column's synonym. Change
the line to 'min(assn_start_date) as start_date'.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[stuart block]

Thanks for the change. It went passed that error.

I am really lost in SQL, I am only just learning VBA.

Stuart