How to get the formula for weekly change %

[Jeya Rexon]

Hi twinnyfo,

I am giving my thanks and appreciate your quick reply.
I forgot my password. so I writing this from another ID. Ok

As per your suggestion, if I make queries as Week-1, and Week-2, I need to put the data separately in every week on week tables. Then only I will get the weekly change out put.

But My boss needs, when we update the records (in date/time type) every week in the query (as per attachment-1), the out put query should show the current week ( ie,Date())completed percentage record(as per attachment-2), as well as the previous week record(like date()-7days). so that the next query will show the weekly change %.

In short, only we put the data (in date format)in my attachment-1. The rest of completed % & weekly change % should be produced itself through proper queries.

I hope you got it. Please help me.

Thanks
Rexon

-------------------------------------------------------------
[My Previous post]

Hi,

I made a query for Procedure completion status as per my attached sketch-1, by that I made one more query (as per attachement -2) to get the completed percentage for dated 13.11.2014.

My requirement is :

I want the (completed)percentage of change in next week on dated 20.11.14 and so on & as well as bar chart for every weekly change% (with seperate colour)

Can you help me, how to get the formula for weekly change % & bar chart.

Thanks

regards
Rexon
Attached Files
File Type: pdf 1.pdf (224.0 KB, 12 views)
File Type: pdf 2.pdf (181.5 KB, 10 views)



P: 1,401
twinnyfo
I have some assumptions:

1: You are able to accurately determine the Weekly percentages of the current week and the previous week? How is the previous week's data stored--or is this something that is determined dynamically?

2: You only want percentage change for "the Current Week vs. the previous week"?

Off the top of my head, one way to do this is as follows:

If you are able to, create two queries (e.g. qryCompRatesWk1 and qryCompRatesWk2). These queries would only return one record, which would be the completion rates for that week. , create a query that can evaluate both weeks at the same time. Then your fields/formulas look something like this:

Expand|Select|Wrap|Line Numbers
SELECT qryCompRatesWk2.DraftComplete - qryCompRatesWk1.DraftComplete AS DraftChange,
qryCompRatesWk2.FirstEditComplete - qryCompRatesWk1.FirstEditComplete AS FirstEditChange,
....
FROM qryCompRatesWk1, qryCompRatesWk2;
This should produce the percentage change from week to week.

Building the chart/graph is another story. But just begin by building a chart/graph, with your new query as a record source.

Hope this hepps (and makes sense)!
1 Days ago #2

[twinnyfo]

If you look back to my first post, the first assumption is critical, and I also think you currently are unable to do the second part, which is to capture the previous week's status in a query.

The only way to do something like this, with your current data structure is to save the calculated percentages, which is never advisable, as I try to never recommend saving calculated fields.

If there is absolutely no necessary reason to have Access create your chart (and using Excel would be acceptable) I would recommend just calculating the current percentages and updating a spreadsheet with the week's current values and using Excel's charting features, which are much easier to use than Access.

It can be done in Access, but I think the effort required would not outweigh the benefit. If this task MUST be done in Access, we can continue to pursue.....

Hope this helps.

[Jeya Rexon]

Thanks for your reply twinnyfo,

My boss needs in Access format only.

Can you try this task for me, Please?

Thank you

Rexon



@twinnyfo

[twinnyfo]

Your first step is to be able to develop two queries: The first must list the status of the projects as of the current week. The second query must list the status of projects as of the previous week.

[twinnyfo]

Your first step is to be able to develop two queries: The first must list the status of the projects as of the current week. The second query must list the status of projects as of the previous week.

So, your data must somehow take into account when a project's status moved to the next phase of review. This is your starting point, and it will depend on what your Data tables look like and how they are constructed.

[Jeya Rexon]

Hi twinnyfo

I want to add some new attachement. I couldnt attach with this reply. So I have posted as new posting on today (26.11.14). Please see my new posting with attachements.

Regards
Rexon



@twinnyfo