Grouping dates in Microsoft Query

[H.G. Lamy]

Hello,

is it possible to aggregate lots of daily billing dates to 12 months in
Microsoft Query, before returning the data to Excel?
Thank you in advance.

Kind regards,

H.G. Lamy

 

[JP]

Excel version?

Can you give an example of what the output would look like?

--JP

 

[H.G. Lamy]

J.P.,

thanks for feedback.

Instead of something like:

PRODUCTS SALES ORDERDATE
Cheese 123 3/3/2009
Cheese 456 3/12/2009
Milk....
etc.

I would like to aggregate (group) the single dates to months:

PRODUCTS SALES ORDERDATE
Cheese 579 March 2009
Milk....
etc.

Is there any way to have Microsoft Query doing that ?

Thank you in advance.

hgl








Excel version?

Can you give an example of what the output would look like?

--JP

 

[JP]

That'll be tough. One way is to simply pull all the data in as-is,
then use a Pivot Table (with its grouping feature) to aggregate the
data by date. You can group by month:
http://www.mrexcel.com/articles/pivot-table-group-dates-by-month.php

Or, edit (or create) the query, then in the MS query window, go to
View > Criteria and click the SQL button. You're viewing the SQL query
that is being run against your data source. Add the GROUP BY statement
after the FROM statement, i.e.

SELECT Products, Sales, OrderDate
FROM OrdersTable
GROUP BY Sales

will show you the count of sales for each product. But since you have
the OrderDate column, you won't get the totals, because each order is
probably on a different date, which will cause each sale to be broken
out into its own row regardless of the GROUP BY statement's directive.
In that case you might want to try an Access group for the appropriate
SQL query statement:

http://groups.google.com/group/microsoft.public.access.externaldata

--JP

 

[H.G. Lamy]

JP,

thank you. I suspected taht inside MS Query it would be tough to realize, as
you said.
While not an "immediate" solution, grouping later in Excel is an easier way.

Kind regards,

hgl

That'll be tough. One way is to simply pull all the data in as-is,
then use a Pivot Table (with its grouping feature) to aggregate the
data by date. You can group by month:
http://www.mrexcel.com/articles/pivot-table-group-dates-by-month.php

Or, edit (or create) the query, then in the MS query window, go to
View > Criteria and click the SQL button. You're viewing the SQL query
that is being run against your data source. Add the GROUP BY statement
after the FROM statement, i.e.

SELECT Products, Sales, OrderDate
FROM OrdersTable
GROUP BY Sales

will show you the count of sales for each product. But since you have
the OrderDate column, you won't get the totals, because each order is
probably on a different date, which will cause each sale to be broken
out into its own row regardless of the GROUP BY statement's directive.
In that case you might want to try an Access group for the appropriate
SQL query statement:

http://groups.google.com/group/microsoft.public.access.externaldata

--JP