Calculate Weekly Average from Monthly Value

[Ktrez1]

I am trying to calculate what my Average weekly values are based on a Monthly Value where the end of my week is Sunday. I am not sure how to go about calculating this. Any guidance would be greatly appreciated.

For example:
MonthlyRevenue
Jun-15 - $100,000.00
Jul-15 - $125,000.00
AvgDailyRev
Jun-15 - $3,333.33
Jul-15 - $4,032.26

WeekEndingRevenue (AvgDailyRev*7)
6/7/2015 - $23,333.33
6/14/2015 - $23,333.33
6/21/2015 - $23,333.33
6/28/2015 - $23,333.33
7/5/2015 - $26,827.96
7/12/2015 - $28,225.81
7/19/2015 - $28,225.81
7/26/2015 - $28,225.81

 

[Ranman256]

make the 1st query Q1, to get all the data you need for the month,
Q1 = select * from table where [month] = 'July'
(this query must also have the field [WeekEnding] in it)

Q2 , take data from Q1 to get the AVG
select [weekEnding], Avg([Amt]) from Q1 group by [weekEnding]

 

[Ktrez1]

Ranman256, Thank you for the response. I currently do not have a [WeekEnding] field. I'm trying to create the [WeekEnding] field in a query to give me the totals. Reading back my initial post I realize I did not illustrate it properly.

I have a MonthlyRevenue table that contains the month year and the Revenue etc.

The rest of the information labeled AvgDailyRev and WeekEndingRevenue are the results I would like returned if possible.

Current Table
MonthlyRevenue
Jun-15 - $100,000.00
Jul-15 - $125,000.00

Create Query with these Results
WeekEndingRevenue
6/7/2015 - $23,333.33
6/14/2015 - $23,333.33
6/21/2015 - $23,333.33
6/28/2015 - $23,333.33
7/5/2015 - $26,827.96 (note this value contains info from both June and July)
7/12/2015 - $28,225.81
7/19/2015 - $28,225.81
7/26/2015 - $28,225.81

 

[Ranman256]

Nonsense! Why would the date not be recorded? You cant calculate the week without a date.

calcualate [weekending] in the query Q1, from the date in the record.
getWeekEnding([date]) as weekending

Public Function getWeekEnding(ByVal pvDate)
Dim iDayNum As Integer, iLastDay As Integer, iDaz2Add As Integer
Dim vDate

iLastDay = vbSaturday

If IsNull(pvDate) Then
   getWeekEnding = ""
Else
iDayNum = Format(pvDate, "w")
iDaz2Add = iLastDay - iDayNum
   getWeekEnding = DateAdd("d", iDaz2Add, pvDate)
End If
End Function

 

[plog]

You cannot do this in a query with the data you have.

Queries cannot create records where there are none. You have 2 records in your MonthlyRevenue table but you want to end up with 8 records in your query. Not possible with what you have given us.

With the way you want the results, you would need a table for every day (not just week) you want to report on. That's because your calculations are actually a summation of days (see the 7/5 record), not weeks. The final query can be done at the week level, but the calculations you are doing are at the day level, therefore you need a table for every day you want to report on.

Do you have such a table?

 

[Ktrez1]

The final query can be done at the week level, but the calculations you are doing are at the day level, therefore you need a table for every day you want to report on.

Do you have such a table?

I do not have a table for every day however I created a query which calculates what the daily amounts are per day based on the MonthlyRevenu divided by days in a given month. Is it possible to do it with this query?

 

[plog]

Nope, its all about number of records at this point. For 2014 your new query produces 12 records (Average Per Month), but you want a query that spits out 52 (give or take).

 

[Ktrez1]

Again, thank you both for your time. From what I gather I must have a table to enter the daily values, then create a query grouping it by week.

 

[plog]

Essentially, yes. A query can't really go from high level to a lower level. You might be able to set something up easier (but still a pain) in Excel.