Current & lastweek completed %

[Jeya Rexon]

Hello twinnyfo,

Thanks for your continous reply.

If I am confusing you, sorry. Forget about the previous postings and attachements. I am telling straight way with this new posting.

Herewith I have attached 3 attachements as: Query -1 (Status as on date.19-11-14), Query -1 (Status as on date.26-11-14) and Query -3 (Current & lastweek completed %).

1) Query -1 (Status as on date.19-11-14) = This query is updated on dated 19-11-14

2) Query -1 (Status as on date.26-11-14) = This query is the same query of the above ,but (with updated datas) on dated 26-11-14.

3) Query -3 (Current & last week completed %) = This is the query to be generated from the above query -1 (please open the attachement & see the date).I want this query's formula (Code)

Now I have manually typed this table for Query -3 (Current & last week completed %). I want this query formula.

This is I need.

Can you make the formula for this Query -3?

Thank you

Regards
Rexon

[twinnyfo]

Rexon,

Please remember that when you post pictures, they are automatically reduced in size to the max allowable on this site, so all your pics are tiny and fuzzy. Please embed in a word document so we can actually see what you are trying to explain.

[twinnyfo]

Also, it appears you have everything you need, which is why I am even more confused, now. If you have your first two queries, you can easily calculate the percentages for each week.

All you need is another query that looks at the two weeks and compares the values. We are not going to do the work for you. You have not even shown what your original queries are and we don't know your table structure, which could affect how we work toward a solution.

Again, we still need more information before anyone can properly guide you through this project.

[Jeya Rexon]

Dear twinnyfo

I Need your help. So nothing I have to hide.

1) Query -1 (Status as on date.19-11-14)
2) Query -1 (Status as on date.26-11-14)

The above are same query. For your explanation I have shown you as two screenshots in different dates.

I used the code for the query-1 is :

SELECT [Tbl WWTP Procedures].ProcedureNumber, [Tbl WWTP Procedures].[Draft Complete], [Tbl WWTP Procedures].[1st Editing Complete], [Tbl WWTP Procedures].[Supervising Complete], [Tbl WWTP Procedures].[2nd Editing Complete], [Tbl WWTP Procedures].[Final Editing Complete], IIf([Draft Complete]+[1st Editing Complete]+[Supervising Complete]+[2nd Editing Complete]+[Final Editing Complete] Is Null," "," Approved") AS [Final status]
FROM [Tbl WWTP Procedures];

As per my previous post, I need the query for my attachent "Query -3 (Current & last week completed %)"

Thank you

Regards
rexon






@twinnyfo

[twinnyfo]

Rexon,

For your first two queries, rather than merely listing the projects and their status, these should count the projects in each status. Because you have shown that you can identify these records, counting should be simple.

However, based on the query you have provided, I don't see how that query can return the values you provided in your Original Post. There is no way to differentiate dates (or as of dates) in your query. If you ran that particular query on a particular day, then you will get the results you want. However, as stated previously, what you need to do is design a query (and thus a table structure that supports it) in which you can put in one date and the query will generate the status of the records as of that date--which is not what you have.

Additionally, in your query, you use the following calculated field:

IIf([Draft Complete]+[1st Editing Complete]+[Supervising Complete]+[2nd Editing Complete]+[Final Editing Complete] Is Null," "," Approved") AS [Final status]

However, it appears that [Final Editing Complete] is the only field you need to check for being Null, as this is the Field that determines if the draft is Approved, Yes?

An additional question has to do with the number of records you want this to apply to. The challenge is that if you want this only to apply to "Records Not Approved", once that document is approved, it would be dropped from the list. This is OK, but you just need to be able to understand it.

I don't know how you are sending your desired date to your Query--which is what is going to drive this entire thing.

Here is a sample query that may help:

SELECT [Enter Your Date] AS AsOfDate,
    Sum(IIf([Tbl WWTP Procedures].[Draft Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS DCNow,
    Sum(IIf([Tbl WWTP Procedures].[Draft Complete]<=DateAdd("d",7,[AsOfDate]),1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS DCLast
    Sum(IIf([Tbl WWTP Procedures].[1st Editing Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS FirstNow,
    Sum(IIf([Tbl WWTP Procedures].[1st Editing Complete]<=DateAdd("d",7,[AsOfDate]),1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS FirstLast
    Sum(IIf([Tbl WWTP Procedures].[Supervising Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS SupvNow,
    Sum(IIf([Tbl WWTP Procedures].[Supervising Complete]<=DateAdd("d",7,[AsOfDate]),1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS SupvLast
    Sum(IIf([Tbl WWTP Procedures].[2nd Editing Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS SecondNow,
    Sum(IIf([Tbl WWTP Procedures].[2nd Editing Complete]<=DateAdd("d",7,[AsOfDate]),1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS SecondLast
    Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS FinalNow,
    Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete]<=DateAdd("d",7,[AsOfDate]),1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS FinalLast
FROM [Tbl WWTP Procedures]
WHERE [Tbl WWTP Procedures].[Final Editing Complete] Is Null
GROUP BY [Enter Your Date];

I have no idea if this will give you the results that you need, as I am free-handing this without having access to your data.

It might get you close.