Roll up/ Cube

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

Answer 1

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

---

Build a little, test a little

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Karl,

I don't thinks so.

I have posted the examples.

Regards,

GGGGGNNNNN

---

GGGGGNNNNN

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

I need more information as I will not go to an unknown website -  adventurework2012  - for the data!

---

Build a little, test a little

Answer 11

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.

Answer 12

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

Answer 13

No!

---

GGGGGNNNNN

Answer 14

That might be possible. I have to test it. Thanks for the response.

GGGGGNNNNN

---

GGGGGNNNNN

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

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.

Answer 24

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

Answer 25

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