Grouping Data in a Query

  • Thread starter Thread starter Paul Fraser
  • Start date Start date
P

Paul Fraser

I have a set of products that are published annually where i need to track
the currency of the products as a statistic. (ex. 10% are 1-2 years old, 20%
are 2-3 years old, etc.)

The database stores the date of publication. How can I use a query to assign
products with post date between x and y to the 1 year category, and products
with post date between z and f to the 2-3 year category, etc.

I need to establish a pivot table that groups products by the office that
produced them, and the age of the products grouped by age.

I'm at a complete lost, I respectfully request help.
 
Start with the between command in the criteria of your query. Between
1/1/2006 and 1/1/2007 would get all data from jan 1, 06 and jan 1, 07.
 
Effectively, I want the query to do just that, establish counts of all
records where post date falls between 1/1/2006 and 1/1/2007, and 1/1/2007 and
1/1/2008, and then tell me how many fall into each category, as a number.
Should I make 4 queries, then a query of the 4 queries that will give it to
me in a pivot table?
 
1) the between function INCLUDES the start and end date. so you cannot
say between 1/1/7 and 1/1/8 if you only want 2007 information.

2) Do a SUM query and
group by year([tableorqueryname]![nameofdateofyourchoice])
Group by Department or whatever you want the groups to be
sum or count or any of the other possible functions for whatever
fields you want.

Ron
 
Back
Top