Query/Reports - looking to get averages from data base

[Guest]

I am new to Access and am trying to get query information on averages. i
created a data base with a date field, followed by several product fields
showing quantity. I used the query wizard to get YTD averages. How do I
query a fixed weekly average from the data base? As enter daily data I would
also like to see the running weekly average. Thanks in advance for your
help. This could make my daily reporting much easier.

 

[Guest]

this should help you get started.....

http://office.microsoft.com/en-us/access/HA101208541033.aspx?pid=CH100645661033#runningsum

 

[kingston via AccessMonster.com]

You'll have to indicate the week for each record, e.g. week 1, week 2, etc.
Then, you'll be able to use a totals query to group by the week (and year)
and average the data. In short, this will entail the creation and/or
calculation of a new column of data that can be used to group the records by
week.

One way to do this is to use Excel to quickly generate a table of start and
end dates for each week (pick a Monday or Sunday and add seven 51 times).
Each pair of dates would get a corresponding week number. Copy and paste
this into Access and use a query compare your dates to see where they fit...

 

[Michael Gramelspacher]

I am new to Access and am trying to get query information on averages. i
created a data base with a date field, followed by several product fields
showing quantity. I used the query wizard to get YTD averages. How do I
query a fixed weekly average from the data base? As enter daily data I would
also like to see the running weekly average. Thanks in advance for your
help. This could make my daily reporting much easier.

Try these in Northwind and see if they are any help.

Northwind Query: Daily Sales Totals
--------------------------------------------
SELECT Orders.OrderDate,
SUM([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.Orderdate BETWEEN [Enter start date:]
AND [Enter end date:]
GROUP BY Orders.OrderDate;


Northwind Query: Weekly Sales Totals
------------------------------------
SELECT DATEADD("ww",DATEDIFF("ww",0,[d.orderdate]),0)
AS [week ending],
SUM(d.[daily sales]) AS [weekly sales],
COUNT(d.orderdate) AS weekdays,
CCUR(SUM(d.[daily sales]) / COUNT(d.orderdate))
AS [daily average],
(SELECT SUM(a.[daily sales])
FROM [daily sales totals] AS a
WHERE DATEADD("ww",DATEDIFF("ww",0,[a].
[orderdate]),0)
<= DATEADD("ww",DATEDIFF("ww",0,[d].
[orderdate]),0))
AS [ytd sales]
FROM [daily sales totals] AS d
GROUP BY DATEADD("ww",DATEDIFF("ww",0,[d.orderdate]),0);

 

[Jamie Collins]

SELECT DATEADD("ww",DATEDIFF("ww",0,[d.orderdate]),0)
AS [week ending],

Based on the idea that the integer value zero is a known Saturday <g>.

I like this approach because it uses temporal functionality so why not
use a 'real' date e.g. #1990-01-06 00:00:00# to makes things a little
more intuitive.

FWIW here's what I'd use, which uses the *next* Saturday if the date
itself is a Saturday:

SELECT DATEADD('D', (DATEDIFF('D', #1990-01-06 00:00:00#,
[d.orderdate]) \ 7) * 7, #1990-01-13 00:00:00#)
AS [week ending]

Jamie.

--