• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

create query to search back a year

Hello,
i have attached a small sample of data.
Is there a way to create a query for this data and find avg daily quantity used based on the field [qty installed]?
problem I have is first how do I join and group data and keep dates?
second can a query say look back a year from today and create daily average based on it?
thank you
Database1.accdb
0
davetough
Asked:
davetough
  • 2
1 Solution
 
darwinsradioCommented:
Database1.accdb

Hi

Is this what you mean?  There's one query that collects only the rows up to 365 days old based on the current system date.  Then I have added two queries that use the first query's results.  One gives an average of all units (i.e. 234 "somethings") and the other finds the average per description (which would be really better expressed with a definite "category" ID to ensure spelling variances don't confuse things)
0
 
darwinsradioCommented:
Sorry, just spotted you were after "daily average".  My bad.

Please see new attachment instead.  I've started from my previous database but the second level queries don't average now, they sum instead.  Then a third query (or your own report or whatever) divides the resulting sum by 365 to get daily average.  Slightly more complicated work needed to do "working day" average of course.

Hope it helps anyway.
Database2.accdb
0
 
davetoughAuthor Commented:
thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now