I'm stumped..

[Kipper]

Hi,

I m trying to find a way of automatically inputting the progress of my project.. it's confusing so i'll try ot explain more. I have a project plan broken down into weeks . Each element of this has a schedule and complete date for various different milestones which indicate what week my project is sitting at:- eg: I have reached this milestone therefore my project is at week 10 and so on until completion.

Is there some query I can run that can do this automatically?

I'd be grateful for any help on this.

Thanks K

 

[elbweb]

my suggestion for you is to see if you can give us a sample to look at, since its that easy to pull what you are trying to say out of your post, no offense, it just seems like it would be easier to understand, and easier to get you the right answer, if we had somethign to look at.

 

[Kipper]

Sorry Elbweb - I knew it was confusing. I don't have access onthis pc so I will have to wsait until tomorrow to send something... But I will try once more to explain myself properly.

Current Week 1 Week 2 Week 3
Week Complete % Complete % Complete

Apple ? 100% 100% 100%
Orange ? 100% 100% 0%
Banana ? 100% 0% 0%

What I need is a query (or something) which will automtically complete the "Current Week" field so for Banana it will tell me that the Orange project is at week 2 (based onthe fact that weeks 1 and 2 are complete and week 3 is not), project Apple is week 3 and project Banana is at week 1.

Does that make any more sense? Sorry to be confusing :-(

K

 

[elbweb]

well now i understand what you want, i've done this before, but i can't think off hand what i need to do, but i will let you know and get back to you on it when/if i find anything

 

[Kipper]

Thank you

Thaak you so much for looking into this for me!! I appreciate any help you can give me Hopefully hear from you soon.

 

[elbweb]

i have been told, and found out myself, that you cannot have an if statement within a query which is basically what you are asking for.

It would be extreamly simple though, to create code that built another table based on this information, and that table was just linked to the original, if you would like an example of that, i can do that for you.

but again, sorry, i dont think you can do what you are asking for exclusivly within a query.

 

[Kipper]

I would like an example if that's ok

 

[elbweb]

sorry i dont have time to comment it, but i hope you can figure it out, if not i will get back to you and help you out further

    Dim myConn As ADODB.Connection
    Dim rsTemp As ADODB.Recordset
    Dim rsTemp2 As ADODB.Recordset
    
    Set myConn = CurrentProject.Connection
    Set rsTemp = New ADODB.Recordset
    Set rsTemp2 = New ADODB.Recordset
    
    rsTemp.CursorLocation = adUseClient
    rsTemp.CursorType = adOpenStatic
    rsTemp.LockType = adLockOptimistic
    rsTemp2.CursorLocation = adUseClient
    rsTemp2.CursorType = adOpenStatic
    rsTemp2.LockType = adLockOptimistic
    
    rsTemp.Open "SELECT * FROM tblItems;", myConn
    rsTemp2.Open "SELECT * FROM tblOutput;", myConn
    
    While rsTemp.EOF = False
    rsTemp2.AddNew
    rsTemp2!Title = rsTemp!Title
    If rsTemp!Week3 = "100" Then
    rsTemp2!Completed = "Week 3"
    ElseIf rsTemp!Week2 = "100" Then
    rsTemp2!Completed = "Week 2"
    ElseIf rsTemp!Week1 = "100" Then
    rsTemp2!Completed = "Week 1"
    Else
    rsTemp2!Completed = "None"
    End If
    rsTemp2.Update
    rsTemp.MoveNext
    Wend
    
    rsTemp2.Close
    rsTemp.Close
    
    Set rsTemp = Nothing
    Set rsTemp2 = Nothing
    myConn.Close
    Set myConn = Nothing

 

[mattv10]

I'm pretty new to developing databases. Actually, my internship has me learning fast. But, I thought you could have a If statement in a query using TransAct SQL?? MS Access might not support this feature yet or does it???

-Matt-

 

[Kipper]

Thanks again Elbweb. I will have a look tomorrow and let you know how I get on.

 

[elbweb]

matt,

i dont know off hand, i hadn't been able to come up with something, i am an intern as well and asked around and no one had any idea what to do, and it wasn't that hard to create a code that did it so i didn't go any further.

 

[Banana]

Wouldn't a crosstab query accomplish Kipper's needs?

 

[elbweb]

banana, i will leave that one for you,the only time i ever tried to get a cross tab query to work it blew up on me and i have avoided them ever since, forgot they existed, but i think that shoulds about right..

i tried a crosstab query wizard real quick and don't know how to make it work for his data, but mayube you could get it to work? the data is pretty simple to recreate.

 

[Banana]

Yeah, crosstab can be confusing affair.

To get this to work, we will need three "columns"

1) A table of Weeks. I'm not sure how many weeks Kipper wanted, but it should be in a table something like:

WeekID
WeekNumber

2) ProjectList. It can be a query if there isn't a list of projects already.

3) Progress Status. Again, I don't know how Kipper has that set up, but I'd assume if there is a table that tell how a project is progressing in a given week, then you just need that field.

Set those as following:

Week as Column Heading
Projects as Row Heading
Progress Status as Value.

The crosstab query then will spread out the entry in Week table/query and Projects table/query.

I will try a sample crosstab query when I get to work.

 

[Kipper]

Thank you Banana!

I would be interested in hearing if you can get it to work. I will try this too and let you know.


Thaanks again.

 

[Banana]

Okay I got some things wrong, but I got up a quick and dirty crosstab query that works just what Kipper wants. Be sure to notice that the crosstab query is based on a query aptly named qtrCrossTabSetter to get the necessary information.

HTH.