Solved

MS Access Reports Problem

Posted on 2007-12-06
8
370 Views
Last Modified: 2013-11-28
I have an Access 2000 database. I am trying to group a report by one field, but sort the results by another one.

I have posted a screenshot of the existing report here:
http://www.nebulasystems.net/groupingshot.jpg

What I am trying to do is group the report by Supplier, but sort the Suppliers by the items sold (circled in red)

Any ideas?
0
Comment
Question by:JedNebula
  • 4
  • 3
8 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 20421688
You didn't provide what problem you are experiencing.  I am guessing you are trying to sort by the OrderBy property but it is not working. When you are using grouping, the sorting must be done in the Sorting and Grouping dialog as it overrides the OrderBy property.  Just set the sort order and set Group Header and Group Footer to no for the field you want to use for the sorting.
0
 
LVL 7

Expert Comment

by:rheitzman
ID: 20424642
A quick solution that often works is to use Office Links, Analyze with Excel. If you have a simple flat table structure that maps well into a spreadsheet you have lots of ways to work with the data.
0
 
LVL 1

Author Comment

by:JedNebula
ID: 20426276
Thank you both for your responses. Unfortunately  Rheitzman, the database will be deployed to many different places, some of which will not have an installed copy of Office, just Access runtime.

Sorry TheNelson if I was unclear, please let me try to clarify&
To demonstrate what I am trying to do, I have replicated the situation in another database which I have posted here:

http://www.nebulasystems.net/RptTest.zip

In this database, I have actually achieved what I am trying to do in the With Sort report. The problem is, when I use this method on my live database, it is extremely slow because the sales table has about 4.7 million records in it. I am looking for a simpler and more efficient way than to use the DSUM function.

I am just trying to group by SupplierID, but sort the records in descending order of the sum of the how many Items that supplier has sold.

I hope this helps&.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 20427054
<I am looking for a simpler and more efficient way than to use the DSUM function.>
Again more detail would be helpful so we could better understand what you are trying to do, but to speed up the sum:

Create an aggregate (summation) query -save the query.

Create another query that combines the new aggregate query and the table or query that is the record source for the report. - save this query

Use the combination query as the record source for the report.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:JedNebula
ID: 20427537
Sorry thenelson, I'm not sure what additional detail you're after that I haven't mentioned, but I'll give you an overview of the situation. Basically, this report is a sales report. In the Report Open event, I ask the user what they would like to see. They can choose to group the report by Supplier, Customer, Product Type or by Product Group. This changes the ControlSource property of my GroupLevel.

However in addition to the report being grouped by Suppliers for example, the report also need to sort in order of which suppliers have sold the most in terms of Value, Qty, Profit or Margin.

Due to the 16 odd possible reports that could be run, I am trying to get one report to do it all, rather than create 16 different ones!

I did try the sub query method and it did work as well, but the speed still wasn't pleasing. I only really want it working out the subQuery for the relevant information used on the report as well, so that would take me down the Changing the QueryDefs path if I wanted to avoid having lots and lots of subQueries for the different options possible, which would make it even slower. That is why I tried to add a group at the beginning which used the DSUM function to go and evaluate only the relevant info.

It may be that there is no quick way to get this kind of functionality with so many records in the Sales table. If so, never mind, but it just seemed to be quite a simple thing to try and do, well at least it has been in other reporting progs.

Is that any clearer? Clear as mud?
0
 
LVL 39

Accepted Solution

by:
thenelson earned 500 total points
ID: 20429116
Is it the DSUM function that is slowing down the report?  Are you using the DSUM on the same field for all the different permutations of the report?  If yes to both, try using an aggregate query to do the sum and to reference the query, in order of speed a recordset, a combobox, or DLookup.

With about 4.7 million records you should be using SQL or SQL Express with a stored procedure.  Then speed would not be an issue.
0
 
LVL 1

Author Comment

by:JedNebula
ID: 20429157
At the moment, I've only got as far as trying it on one permutation. I saw how slow it was and so didn't continue. The data is actually stored in SQL, so maybe I'll try a stored procedure and a pass-through query next week and see what happens.
Thanks for your help. I'll let you know the result.
0
 
LVL 1

Author Closing Comment

by:JedNebula
ID: 31413137
Stored procedure worked fine.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now