Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Reports Problem

Posted on 2007-12-06
8
Medium Priority
?
379 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 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