Weekly Query

[nick1408]

Hi Guys,

I'm trying to make a query (or formula) that will show the total effort expended each week for 52 weeks.

I have 2 fields I am interested in - [Total Effort], [31 date] which are part of table [NPS Tasks].

I plugged in the following into a form field - =(DSum("[Total Effort]","[NPS Tasks]","[31 date]">Weekday(Date(),1)&"[31 date]"<Weekday(Date(),7)) but this returns the total of the column rather than the data from this week only. I then decided that maybe the best way to do it was via a query but I am not sure how to set it up then also show it on a form.

Thanks for any help.

 

[sneuberg]

Try using the query designer to create an aggregate query. You want to group the data into weeks. You can do this by creating an expression using DatePart with "ww" for an interval. This gives a week number e.g., 47 for this week that you can group on. You can set the first day of the week in the DatePart function. You can select the specific 52 weeks you want to display by using BETWEEN in the criteria.

 

[nick1408]

I think I got it to work without the BETWEEN criteria. I set the first column of the query to Week #: DatePart("ww",[31 Date]) for the field and group by. The second I set to Total Effort for Field, Sum By for Total and it seems to work.

Thanks for the help!

 

[nick1408]

Above is my results from the query. My question now is how do I distinguish that the data is from week 5 2016 or week 5 2017 for example. I wish to put this data onto a form then graph it also. My form looks like this:

I would like this data to go into the Effort Forecast fields for each week.

Apologies if this is simple stuff - I'm still fairly new to Access.

 

[Galaxiom]

My question now is how do I distinguish that the data is from week 5 2016 or week 5 2017 for example.

By using the Between criteria that snuberg suggested

You can select the specific 52 weeks you want to display by using BETWEEN in the criteria.

 

[sneuberg]

You could try an expression like

[31 Date] - WeekDay([31 Date]) + 7

which would give you the date that falls on the last Saturday of the week and group on that instead of the DatePart expression.

 

[nick1408]

Perfect formula!

I am doing all this on the fly, hence the follow-on questions.

I haven't tried to query more than one table at a time. I have the following table:

How would I interrogate the Effort Per Week column next to the appropriate week ending date you have supplied?

Effort is always supplied per month so the daily and weekly is a formula to break it down into such - [Baseline]/(Day(DateSerial(Year([Effort Month]),Month([Effort Month])+1,0))) and [Effort Per Day]*7. Ultimately the user will be able to change the breakdown percentage (a breakdown of 70% and 30% between two types)

What I have done is setup a table to have the baseline monthly data entered into. I am currently setting up a table that will break this down further by the week with the percentages so that it can all be calculated with adjusted percentages if required. I have a split form where the data from the second table can be stored and manipulated. It is this manipulated data that ultimately needs to be interrogated against the graph that you guys have helped me design above.

 

[nick1408]

I guess the other thing I could do from the above is set a query to break the month down into weeks like I have above then use that data for my table. I still feel I need two table due to how the data is supplied (monthly).

I may be getting out of my depth here!

 

[sneuberg]

Sorry but this table confuses me and I don't understand what you mean by

How would I interrogate the Effort Per Week column next to the appropriate week ending date you have supplied?

If I could see the raw data involved and how you want this data summarized I could probably provide better help. Could you upload the database?

 

[nick1408]

Database uploaded as requested.

Thanks for the help

 

[sneuberg]

Sorry but the database only confuses me more. I'm mystified by the Open NPS Tasks query which appears to have a table named NPS Tasks that's not in the database. But aside from that this appears to be far from normalized with fields like 7 Date, 8 Date, ... 40 date. I think you structure needs to be reconsidered.

 

[nick1408]

I thought it may not make sense. I had to remove a heap of corporate crap before uploading. I thought I left in the required tables but my mistake there. I can guarantee you that the structure is bad. It was my first real go at this and I have since built upon what I started without considering the future

 

[nick1408]

NPS Tasks is certainly there - it is a hidden table

 

[sneuberg]

NPS Tasks is certainly there - it is a hidden table

Ok, I see it now. This is a first for me. I haven't see a database with user hidden tables before.

 

[nick1408]

Ok, I see it now. This is a first for me. I haven't see a database with user hidden tables before.

Apologies. Like I said, first for me so really appreciative of the help

 

[nick1408]

Perfect formula!

I am doing all this on the fly, hence the follow-on questions.

I haven't tried to query more than one table at a time. I have the following table:

How would I interrogate the Effort Per Week column next to the appropriate week ending date you have supplied?

Effort is always supplied per month so the daily and weekly is a formula to break it down into such - [Baseline]/(Day(DateSerial(Year([Effort Month]),Month([Effort Month])+1,0))) and [Effort Per Day]*7. Ultimately the user will be able to change the breakdown percentage (a breakdown of 70% and 30% between two types)

What I have done is setup a table to have the baseline monthly data entered into. I am currently setting up a table that will break this down further by the week with the percentages so that it can all be calculated with adjusted percentages if required. I have a split form where the data from the second table can be stored and manipulated. It is this manipulated data that ultimately needs to be interrogated against the graph that you guys have helped me design above.

I was thinking of this last night. If I query the quoted table as per below (the formula is wrong - I know that) I will get what I am after.

I do need to get the formula though.

Then I can add it to a split form like this (broken down to weeks instead of months):

I still think I need to take one step back and add a table prior to all of this. I'm probably getting out of the query forum a bit here but I would like the table to automatically add the week each time a new monthly baseline is added. I know this sort of thing can be done on a form with an On Load command so I'm not too concerned with that.

By the way, the query from the initial question came out perfectly: