Grand totals on a single view (1 Viewer)

[JaneJohnstone]

Jeff,
Is it possible to set up a view within an access app to show a number of different "grand totals", related to one or more tables. For example, I'd like to display the total value of all orders, the total number of orders and the total number of items ordered, on a single view. In this case, all the information comes from one table - Orders, which contains fields - Total Cost and Quantity. I've tried to create a Summary view, but that requires me to use the "Group By" function, which I don't want here, so I tried to do it with queries, but it seems to involve multiple queries, which then means multiple views (unless I've misunderstood?)
I've doubtless missed something obvious......
Thanks
Jane

 

[JaneJohnstone]

Sorry - ignore the question - I should have read on a bit further - seems I can do this with subviews.....

 

[AccessJunkie]

Hi Jane,

Apologies for not getting back to you sooner (been very busy with work recently getting ready for the SharePoint Conference).

It sounds like you've already come up with a solution to your issue, correct?

Depending upon exactly your goal here, there are a few different possibilities.

1. You can drop a Related Items Control onto the single item view and display related data to the parent view's record. The Related Items Control has the ability to display a Count, Sum, or Avg of a number field in the display column. Look for these options on The Calculation property menu.

2. If you just want to display related records, you could also use subviews to display related records (or unrelated) on the single item view. In this scenario, you can make the single item view be like a dashboard displaying different items.

3. You can also use data macros to retrieve some count information from tables and bring those totals up to the UI layer. A UI macro that calls the data macro could then use the SetProperty macro action to "push" those values into label or text box controls to display totals information.

I think I remember you mentioning you have a copy of my 2013 book, correct? Although I don't really talk very much about the Auctions web app sample that comes with my book, take a look at that app when you have some time. The main home page for that app displays a predefined list of auction categories. Beneath each category, you'll see a count of the total number of open auctions for that category. This is also done on a single item view. A data macro is fired in the On Load event of the view (check out the macro logic there to see what I'm doing). The data macro gets a count of each category (using a totals query), brings it back up to the view, and then pushes those values to the labels beneath each category picture.

Explore that app, view, and macro logic to learn more. I've attached a screenshot of that view to my post here. Notice the category total beneath each picture (where the red arrows are pointing in the screenshot). This is dynamically filled every time you navigate to that view. (There are just a few sample records in that app.)

Hope that helps,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------