Solved

MS Access Reports Problem

Posted on 2007-12-06
8
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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