Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping dates in Microsoft Query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping dates in Microsoft Query
Excel version?
Can you give an example of what the output would look like? --JP On Feb 2, 7:00*am, "H.G. Lamy" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping dates in Microsoft Query
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 "JP" wrote in message ... Excel version? Can you give an example of what the output would look like? --JP On Feb 2, 7:00 am, "H.G. Lamy" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping dates in Microsoft Query
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/pivo...s-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/micro...s.externaldata --JP On Feb 2, 1:15*pm, "H.G. Lamy" wrote: 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 "JP" wrote in message ... Excel version? Can you give an example of what the output would look like? --JP On Feb 2, 7:00 am, "H.G. Lamy" wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping dates in Microsoft Query
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 "JP" wrote in message ... 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/pivo...s-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/micro...s.externaldata --JP On Feb 2, 1:15 pm, "H.G. Lamy" wrote: 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 "JP" wrote in message ... Excel version? Can you give an example of what the output would look like? --JP On Feb 2, 7:00 am, "H.G. Lamy" wrote: 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Excel - Grouping and Sorting Cells | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
Microsoft Query: retrieve data by dates | Excel Programming | |||
Microsoft Query: retrieve data by dates | Excel Programming | |||
Microsoft Query & Dates from Oracle | Excel Programming |