?
Solved

MS Access Reports Problem

Posted on 2007-12-06
8
Medium Priority
?
383 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

589 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