none
Roll up/ Cube RRS feed

  • Question

  • Hello all,

    We have two functions or statements which make grouping sets in SQL Server 2012. Do we have an equal statement for cube and roll up in Microsoft Access?

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, December 1, 2015 6:54 PM

All replies

  • Are you talking about a 'Totals' query in Access?

    Build a little, test a little

    Tuesday, December 1, 2015 7:16 PM
  • Hi GN,

    Do you want to achieve subtotal and grand total queries in Access? It would be helpful if you could share us more information about your requirement like table data, table result, or the query you used in SQL Server.

    If you want subtotal and grand total, I think you could use crosstab query.
    # HOW TO: Create a Crosstab Query in Microsoft Access 2000
    https://support.microsoft.com/en-us/kb/304348

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 2, 2015 2:35 AM
  • Hello team,

    There are some statements/ functions in SQL Server 2008, 2012, etc.

    I wonder if we can get the equal of these statement in Microsoft Access.

    For example

    Grouping by multiple sets:

    Select  ShipperId,  Year(ShippedDate)  As  ShipYear,  Count(*)  As  NumOrders

            From  Sales.Orders 

            Group  By  Grouping  Sets

            ((ShipperId,  Year(ShippedDate)),  (ShipperId ),  (Year(ShippedDate)),  ());

     

    Cube:

    Select  ShipperId,  Year(ShippedDate)  As  ShipYear,  Count(*)  AS  NumOrders

            From  Sales.Orders 

            Group  By  Cube(ShipperId,  Year(ShippedDate));

     

    Windows functions:

    Select  CustomerId,  OrderId,  Val, 

                Sum(Val)  Over(Partition  By  CustomerId)  As  CustomerTotal,

                Sum(Val)  Over()  As  GrandTotal

            From  Sales.Orders;

    Thank you for the help.

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, December 2, 2015 5:19 AM
  • I think several queries (UNION) to feed a crosstab query will do it.

    Post example data and desired results of that data.


    Build a little, test a little

    Wednesday, December 2, 2015 5:45 PM
  • Hello Karl,

    Yes, but we want to arrange the data in a different way.

    I don't want to group the rows and then put a total on the grouped data. Sometimes, we need to group by multiple sets or group by two fields rather than one field or we want to show detail line along with total line.

    Such as:

    customerId   orderAmount  total

    1                     24            54

    1                     30             54

    How can that be done in Microsoft Access?

    I know how do it in SQL Server.


    GGGGGNNNNN

    Thursday, December 3, 2015 6:12 PM
  • Karl,

    I don't thinks so.

    I have posted the examples.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, December 3, 2015 6:13 PM
  • Hello Edward,

    I have put the queries below. You can put the data into it.

    I can't sent the data out. This window doesn't allow me to do it. However the queries can guide you what I need.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, December 3, 2015 6:15 PM
  • I don't want to group the rows and then put a total on the grouped data. Sometimes, we need to group by multiple sets or group by two fields rather than one field or we want to show detail line along with total line.

    Such as:

    customerId   orderAmount  total

    1                     24            54

    1                     30             54

    I do not see anything to 'cube' in the above information.  

    Post the table and field names with datatype and data examples of input and display desired.  


    Build a little, test a little

    Thursday, December 3, 2015 7:04 PM
  • Please look into this queries:

    Cube:

    Select  ShipperId,  Year(ShippedDate)  As  ShipYear,  Count(*)  AS  NumOrders

            From  Sales.Orders 

            Group  By  Cube(ShipperId,  Year(ShippedDate));

     

    Windows functions:

    Select  CustomerId,  OrderId,  Val, 

                Sum(Val)  Over(Partition  By  CustomerId)  As  CustomerTotal,

                Sum(Val)  Over()  As  GrandTotal

            From  Sales.Orders;

    If you download the adventurework2012, you will be able to run this queries and see the data types and data example.

    What I asked is if we are going to do this queries in Microsoft Access, what would be the equal of these queries?

    The first one is cubing the data by (ShipperId,  Year(ShippedDate)) and the second one is a windows function in SQL Server.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, December 3, 2015 7:23 PM
  • I need more information as I will not go to an unknown website -  adventurework2012  - for the data!

    Build a little, test a little

    Thursday, December 3, 2015 9:22 PM
  • Hi GN,

    >> What I asked is if we are going to do this queries in Microsoft Access, what would be the equal of these queries?

    There queries are not supported in Access. You will need to achieve this function with your own queries like select, groupby and sum function.

    >> If you download the adventurework2012, you will be able to run this queries and see the data types and data example.

    Based on my research, adventurework2012 is Data file for SQL Server, it could not be used in Access. Which test data you have, which query you have wrote and which result you have got, which result you want? Did you have any specific issue with your queries. We will glad to help you with the specific issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, December 4, 2015 2:18 AM
  • Hello Edward,

    Yes, that is correct; these queries are for Microsoft SQL Server.

    I wondered if we want to get these results from top functions, what would be equal function/ statement.

    If you go to google and search for Adventurework2012, the first link takes you to tables and database. Enter these queries in management studio and see the result. Can we get these results in Microsoft Access?

    With cubing you build the groups that you can put aggregate functions to those groups; or in Microsoft Access, you can group data, however in SQL Server, you can build multiple groups.

    I think the best way to understand is to run these queries in Microsoft SQL Servers 2012. If you have not used SQL Server, it will be hard for me to explain.

    From what you said, these queries are not supported in Access and I do agree with you; that is why I am asking if somebody has the knowledge to get these result in Microsoft Access.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN


    • Edited by cloudsInSky Saturday, December 5, 2015 8:03 AM
    Saturday, December 5, 2015 8:00 AM
  • No!

    GGGGGNNNNN

    Saturday, December 5, 2015 8:03 AM
  • That might be possible. I have to test it. Thanks for the response.

    GGGGGNNNNN


    GGGGGNNNNN

    Saturday, December 5, 2015 8:04 AM
  • I am still waiting on you to post example data of what you want to do.

    If one of your fields is a datetime datatype then what granularity do you want - yearly, quarterly, monthly, daily, hourly, five-minute intervals, or all of the above plus everyting in between?


    Build a little, test a little

    Saturday, December 5, 2015 5:21 PM
  • Hello Karl,

    The queries which I have posted in this forum are for SQL Server 2012. What I asked was whether we have equal statements in Microsoft Access for these queries.

    To know what these queries are doing, you need to have access to data.

    You can download adventurework2012 from:

    https://www.google.com/search?q=adventureworks+2012+download&ie=&oe=

    You need to have SQL Server 2012 on you computer as well to be able to run these queries.

    Are you familiar with SQL server 2012?

    I have used two functions here: Cube and Roll up, both group the data based on the fields and then on the grouped data; we can show calculation. But the good point about these queries are that they show detail line along total line. In Access, when we group the fields, we lose detail line.

    Hope to this is clear. If you don't have knowledge about sql server and you don't have the tool, it is hard this to be figured out.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Saturday, December 5, 2015 10:33 PM
  • I have worked with previous versions of SQL Server but do not have it on the machine I now use.

    I will not go to that website!   I think the queries can be built after looking at example on the web.

    I asked for example data - not actual data.   

    You posted --

    customerId  orderAmount   total
    1 24 54
    1 30 54

    I do not see how that data would be used for Roll Up/Cube.

    On the other hand this --

    Product Type Variety Qty
    Fruit Apple Gala 5
    Fruit Apple Smith 10
    Fruit Pear Barlett 8
    Fruit Pear Bosc 6
    Fruit Pear Asian 2
    Nut Peanut Raw 4
    Nut Peanut Dry Roasted 1
    Nut Cashew Salted

    9

    Would result in this --

    Product Type Variety Qty
    Fruit Apple Gala 5
    Fruit Apple Smith 10
    Fruit Apple null 15
    Fruit Pear Barlett 8
    Fruit Pear Bosc 6
    Fruit Pear Asian 2
    Fruit Pear null 16
    Fruit null null 31
    Nut Peanut Raw 4
    Nut Peanut Dry Roasted 1
    Nut Peanut null 5
    Nut Cashew Salted 9
    Nut null null 14


    Build a little, test a little

    Saturday, December 5, 2015 11:08 PM
  • Yes,

    You got it.

    I wonder how can we get this result in Microsoft Access (I mean this data arrangement/ the result you have posted).

    Product Type Variety Qty
    Fruit Apple Gala 5
    Fruit Apple Smith 10
    Fruit Apple null 15
    Fruit Pear Barlett 8
    Fruit Pear Bosc 6
    Fruit Pear Asian 2
    Fruit Pear null 16
    Fruit null null 31
    Nut Peanut Raw 4
    Nut Peanut Dry Roasted 1
    Nut Peanut null 5
    Nut Cashew Salted 9
    Nut null null 14

    ---------------------------------------

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Sunday, December 6, 2015 10:32 PM
  • FYI:

    With T-SQL, you can define multiple grouping sets in the same query. In other words, you can use one query to group the data in more than one way. T-SQL supports three clauses that allow defined multiple grouping sets: Grouping Sets, Cube, and Rollup. You use these in the Group By clause.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Sunday, December 6, 2015 11:11 PM
  • What I presented was Roll Up and not Cube.   Here is the differences -

    http://stackoverflow.com/questions/7053471/understanding-the-differences-between-cube-and-rollup

     ROLLUP (YEAR, MONTH, DAY)

    With a ROLLUP, it will have the following outputs:

    YEAR, MONTH, DAY

    YEAR, MONTH

    YEAR

    ()

    With CUBE, it will have the following:

    YEAR, MONTH, DAY

    YEAR, MONTH

    YEAR, DAY

    YEAR

    MONTH, DAY

    MONTH

    DAY

    ()

    CUBE essentially contains every possible rollup scenario for each node whereas ROLLUP will keep the hierarchy intact (so it won't skip MONTH and show YEAR/DAY, whereas CUBE will)

    The query has to be created in Access.   Now I want you to give me your table and field names with datatype.   


    Build a little, test a little

    Monday, December 7, 2015 12:39 AM
  • Hello Karl,

    Thank you for the information.

    What would be the query in Microsoft Access to get the same result as you mentioned on the top?

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Monday, December 7, 2015 4:59 AM
  • You keep repeating the same mantra about other applications having Roll-Up/Cube asking for Access query.    Access does not come with queries (NorthWind is an example database with queries, forms, and reports) so they must be created based upon your actual need.

    I and others have told you that we cannot give you what you want based upon the information and references that you so far have provided.

    It seems to me that you do not have a real world requirement that this exercise is a school work assignment. 


    Build a little, test a little

    Monday, December 7, 2015 7:07 PM
  • Hi GN,

    >> Do we have an equal statement for cube and roll up in Microsoft Access?

    No, there is no cube and roll up in Access, and you will need to create your own query or function to achieve your requirement. If this is feature you want to include in the feature version of Access, please submit a feedback in the link below:

    Reference: http://access.uservoice.com/

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, December 8, 2015 3:13 AM
  • Building the data when I sent the query and field should be very easy for everyone:

    OrderId  Shipdate shipperID
    11/12/2013 1
    11/12/2013 2
    12/6/2012 3
    12/06/21015 1
    12/06/21015 2

    And orderids are from 1 to 5; It is very hard to paste the data in this window. This data is for this query:

    Cube:

    Select  ShipperId,  Year(ShippedDate)  As  ShipYear,  Count(*)  AS  NumOrders

            From  Sales.Orders 

            Group  By  Cube(ShipperId,  Year(ShippedDate));

     -----------------------------------------------------

    Windows function:

    Windows functions:

    Select  CustomerId,  OrderId,  Val, 

                Sum(Val)  Over(Partition  By  CustomerId)  As  CustomerTotal,

                Sum(Val)  Over()  As  GrandTotal

            From  Sales.Orders;

    This is the data:

    CustomerId , OrderId, Val

    1               2       3

    2                    3           4

    3                    4            3

    1               5         6


    It doesn't matter it is really this is about my work and homework school, I want to learn. I agree with Edward that cube and windows function don't exist in Access. However, we might get the same result if we are able to design queries.

    I can't write on this windows as I am restricted in term of words.

    Thanks for understanding.

    GN


    • Edited by cloudsInSky Tuesday, December 8, 2015 3:40 AM
    Tuesday, December 8, 2015 3:36 AM
  • 1- It doesn't matter it is really this is about my work and homework school, I want to learn. I agree with Edward that cube and windows function don't exist in Access. However, we might get the same result if we are able to design queries.

    2- I can't write on this windows as I am restricted in term of words.

    1- We DON'T do your homework here!

    I gave you the solution when I said to use a union query.    Try it.    

    2- I do not understand this.   Do you have a limited English vocabulary?

     

    Build a little, test a little

    Tuesday, December 8, 2015 3:31 PM