ms access 2007 sql (compute by or rollup function)

[learning_grexcel]

Hi
I want to use roll up or compute by function in Access 2007 using SQL view but it shows the error "undefined' function. How to sort this out?

 

[Joe4]

I have never heard of such functions, and they do not appear in the Access list of functions.

Where did you get those function names from?
What exactly are you trying to do?

 

[learning_grexcel]

I found it from some Google search. And I want those functions because it creates subtotal

 

[Joe4]

Those are not native Access functions. They might be part of an Add-In, or they may be User Defined Functions (UDFs). So you are not going to be able to use them without finding the associated Add-In or VBA/SQL code. So you may want to go back to where you found that, and see where it mentions you can acquire that Add-In or code required to use those.

By the way, have you looked at Aggregate (Totals) Queries in Access? You can create sub-totals using those. Check out Access's built-in help files to see how you can do that.

 

[learning_grexcel]

Thank you.
I have a data similar to the top of the image below and looking for output similar to the bottom of the image below.

 

[Joe4]

Are you trying to do this in Queries, Forms, or Reports (where do you want your output to go)?

 

[learning_grexcel]

I want in MS Access using SQL query.

 

[Joe4]

No, what I am asking is your desired output.
Do you want that desired output in a Query, Form, or Report (three distinctly different Access object)? It probably depends on what you plan to do with desired output.

 

[learning_grexcel]

I don't know how it will come output in query, form or report as I'm new to access. But I want to export those output in excel using sql query.
I used to do these small stuffs in excel but now it's more than 65536 rows so I have to do in access. I will be glad if you help me to get those outputs in any easy form.

 

[Joe4]

I am guessing that you used to do these as Pivot Tables or Subtotals in Excel.

If you want to export to Excel, you are probably going to want this in a query in Access, as those are easier to export to Excel.

I have never really used Pivot Tables in Access, but I know it is possible. Here are a few links that show you how you can do that. One is a YouTube video.

http://office.microsofhttp://office.microsoft.com/en-us/access-help/designing-your-first-pivottable-pivotchart-views-in-access-HA001034580.aspx
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
http://www.youtube.com/watch?v=xr6IoPMYmU0