MS Access datasheet view like pivot table

[johny6685]

Hi Guys,

Is there any possiblity to bring pivot table view in datasheet. where we can group data and see the total on each group seperately ?

http://pbrd.co/182mJDo

[twinnyfo]

I know it is possible to work with Pivot Tables using a Query or Table, but I have not worked with them in Access much (just played around with them a bit).

When you open a Table or Query in Datasheet view, go to the Home Tab, Views Group and select Pivot Table. I know you can play with it from there, but not sure what results to expect.

[johny6685]

Thanks twinnyfo, But I would like to make the pivot table through VBA. I mean a treeview structure datasheet through vba from a dynamic query which changes on user selection from a form.

Please see below site which will help you understand what I am looking for.

http://www.experts-exchange.com/Database/MS_Access/Q_27964604.html

[twinnyfo]

Johny,

Keep in mind that there is a world of difference between a Tree View and a Pivot Table. Yes, there are some similarities, but they are much different.

If you just want grouping and totals, I think you can do exactly what you want to do with a report, with Grouping and Sums. Forms don't work quite that way.

How is this Tree/Pivot going to be used?

[johny6685]

As you stated, user would like to have the subtotal of each parent and child separately due to which I am thinking to get it through datasheet or report.

Like this


Parent Child Child1 Total
----------------------------------------------
ABC-------------------------------------50
-------------CDB------------------------10
---------------------------RST----------5
---------------------------UWV----------3
---------------------------MNO----------2
-------------EFG------------------------10
---------------------------NPO----------3
---------------------------PNO----------4
---------------------------FPO----------3
DBC-------------------------------------30
-------------HIG------------------------15
---------------------------KLM----------5
---------------------------RST----------10
-------------LMN------------------------15


But only worry with the report is, it will repeat the parent for all child and subchild. But user want that a single time and not repeated. I am not sure whether it is possible with report. Request your expertise

[twinnyfo]

If you create headers for your groups it will only print once. As long as you have your groupings and sums created properly you should be able to create a report exactly as shown.

[johny6685]

Hmmm... I am not catching everything...

Do you have any example link or sample file twinnyfo.?

[twinnyfo]

When you create your report, create groups with headers for Parent, Child, Child1 and Total. I don't have an example report I can send at this point. I'll try to grab something from work tomorrow.

[johny6685]

Hi twinnyfo, did you find any examples. I am really stuck into this part. :-(

I tried the Group and Total part but I am not sure whether it helps me for the dynamic report.

Please advise whether it will helps me to create a dynamic report or I have to look for another way.

[twinnyfo]

What do you mean by a dynamic report? Reports, by their nature, are somewhat static, but can be manipulated in certain ways. They merely report the data that is provided. It is the manipulation of the data that you should be seeking.

[johny6685]

I am trying to create the report from a query during runtime with VBA code, so I am not sure how best "Group and total" will helps me in that.

Bcoz, my previous idea is to show the data in datasheet view, since the grouping and showing subtotal is not possible in datasheet I thought of using report but reports again it is dynamic as it created based on the user selection. So I am trying to create the report through vba based on the query.

[twinnyfo]

As long as you have standardized groupings you should be fine.

Now, one thing that you might be able to do to play games with the report's grouping feature is to create "dynamic" grouping. So, the query may change based on what you want in the query, but it always creates a "Group1", "Group2" and "Group3", which would all apply to various parts of the data. Then you could assign a name to that group in another field. The report would have grouping based on the fields "Group1", "Group2" and "Group3" with corresponding text boxes for "GroupName1 ", "GroupName2 " and "GroupName3 " etc.

You just have to make sure that the output fields of the query are standardized with the report.

Based on the desired output in Post #5, it is very straightforward in reporting. But, from Post #11, it appears that the output fields of the Query change dynamically?

[johny6685]

I am sorry about that. May be I might have confused in post 5 but if you see my post 3 i have made a note that this report is coming out from a query based on user selection.

I am going to try your words, let me check and get back if something I get positive. :-)

[twinnyfo]

I am talking about the results you want in Post #5. If you ask me, it looks like Grouping/Summing within a report is the proper approach.

When you create a report in Access, you must declare what the corresponding field names will be for that report. Now, it is possible to manipulate the Record Source for a text box on a report, but most have found that this can be way more work than it is worth.

If your users are creating completely different queries with different field names and different levels of grouping/summing each and every time, it would be nearly impossible to capture that type of dynamic querying in a report built either with VBA or by modifying an existing report in VBA.

However, if you are able to create a query that ALWAYS has standardized Field Names (for example:

SELECT tblName.FieldName1 AS Group1
    tblName.FieldName2 AS Sum1 ...
GROUP BY Group1
ORDER BY Sum 1 DESC;

) then you might be able to be quite successful on this.