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
Solved

MS Access Reports Problem

Posted on 2007-12-06
8
374 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

856 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