473,326 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Current & lastweek completed %

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
Nov 26 '14 #1
4 1031
twinnyfo
3,653 Expert Mod 2GB
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.
Nov 26 '14 #2
twinnyfo
3,653 Expert Mod 2GB
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.
Nov 26 '14 #3
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
Nov 27 '14 #4
twinnyfo
3,653 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. 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:


Expand|Select|Wrap|Line Numbers
  1. SELECT [Enter Your Date] AS AsOfDate,
  2.     Sum(IIf([Tbl WWTP Procedures].[Draft Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS DCNow,
  3.     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
  4.     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,
  5.     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
  6.     Sum(IIf([Tbl WWTP Procedures].[Supervising Complete]<=[AsOfDate],1,0))/Sum(IIf([Tbl WWTP Procedures].[Final Editing Complete] Is Null,1,0)) AS SupvNow,
  7.     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
  8.     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,
  9.     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
  10.     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,
  11.     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
  12. FROM [Tbl WWTP Procedures]
  13. WHERE [Tbl WWTP Procedures].[Final Editing Complete] Is Null
  14. 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.
Dec 1 '14 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Plumer | last post by:
Hello everyone, Yesterday I posted a message about implementing drag & drop in a TreeView control. I'm having real difficulty getting this to work -- the process seems to be incredibly...
3
by: Vic | last post by:
Dear All, I have a database of laboratory records in Access 2000. There is one form which acts as an interface to input experimetal data. This form incorporates information from several tables....
2
by: Melissa | last post by:
I have a single (not continuous) form with an Undo button for entering finished projects. On the form is also a subform that lists all finished projects for reference. When I enter the...
8
by: Z D | last post by:
Hello, I'm having a strange problem that is probably due to my lack of understanding of how threading & COM Interop works in a WinForms.NET application. Here's the situation: I have a 3rd...
1
by: terry.statham | last post by:
Hope someone can help me here. I am fairly new to Access. I have set up a database relating to vehicle movements prior to sale. I have a subform based on 'locations' table which is linked to my...
0
by: bughunter | last post by:
Local server C:\TRACE>db2level DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with level identifier "03050106". Informational tokens are "DB2 v8.1.11.973", "s060120",...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
9
by: JFS | last post by:
I know most of you have probably read "The C Programming Language" (K&R) at some point. Well here is something that is driving me crazy. The exercises are impossible (most of them) for me to do....
8
by: bytes access nubie | last post by:
In MS Access, we are trying to write code that will loop through all databases in the current folder AND subfolders to create a table (or file) containing a list of the database names, their LINKED...
1
by: Parita Pandya | last post by:
I am preparing a to do list in which there is Status (completed / not yet) and Task name in sheet 1. In sheet 2 (completed) the task which is marked completed is moved with the current time. Now I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.