Link to home
Start Free TrialLog in
Avatar of staceymoore

asked on

Help with Ranking Results in MS Access Report


I have a query that currently ranks all of my customers by volume sold.  However, when I design the report by customer and reference the ranking results using DLOOKUP, the results are correct for some customers and incorrect for others.

Can anyone explain why this is happening and provide an alternative solution if necessary?

Avatar of IrogSinta
Flag of United States of America image

You would have to give some more information for us to determine where the error lies.  The DLOOKUP statement, the Recordsource of your report, and the SQL code of the query.
Can you give us a screen shot of your results.  

Usually, for rankings (in a report), I just sort my results largest to smallest, then use an unbound textbox with controlsource: =1 and then set the RunningSum property to Over Group or Over All.
Avatar of staceymoore



Please see below as requested:

The SQL Code for the Query:
SELECT RankingBBLS.Market, RankingBBLS.CustomerNumber, RankingBBLS.CustomerName, RankingBBLS.Barrels, (Select Count(*) from qry_Master_FY1213 Where [Barrels] >   [RankingBBLS].[Barrels])+1 AS Ranking
FROM qry_Master_FY1213 AS RankingBBLS
ORDER BY RankingBBLS.Barrels DESC;

Open in new window

The DLOOOKUP Statement:
=DLookUp("Ranking","qry_CustomerRanking_BBLS","[CustomerNumber] = [txtCustomerNumber]")

Open in new window

The Recordsource of the Report:

Other Info:

All of the records are listed in a query called qry_Master_FY1213

There are three (3) "TopCustomer" queries namely by BBLS, Sales and Margin based on the data in qry_Master_FY1213.  This was done this way because some customers may be in the top 5 in terms of BBLS and not in the top 5 in terms of Margin.  The top 5 customers in each query were then combined in a union query called union_TopCustomers (there are no duplicates in this union query) and this is the basis of my report since I am only interested in seeing the results for the top customers.

The name of the ranking query is qry_CustomerRanking_BBLS

So in summary:

1. qry_Master_FY1213 contains all records

2. qry_Top5_BBLS, qry_Top5_Sales and qry_Top5_GM were created based on the records in (1).

3. union_TopCustomers is based on the results of the 3 queries in (2) but the result only list 7 customers since some customers may be on more than one list.

4. qry_CustomerRanking_BBLS is based on qry_Master_FY1213

5. the recordsource for the report is union_TopCustomers and then a DLOOKUP statement was created to lookup qry_CustomerRanking_BBLS and return the customer ranking where the customer numbers match.

"All of the records are listed in a query called qry_Master_FY1213"

So can a customer appear more than once in this table?

A customer appears only once in qry_Master_FY1213.
If customernumber is a number then use...

=DLookUp("Ranking","qry_CustomerRanking_BBLS","[CustomerNumber] =" &  [txtCustomerNumber])

if it's text use..
=DLookUp("Ranking","qry_CustomerRanking_BBLS","[CustomerNumber] ='" &  [txtCustomerNumber] &"'")

I tried using the 2nd dlookup since my customer number is alphanumeric.  However, I am still getting some errors.

I have attached a screenshot of the top 10 customers according to rank as well as the resulting report.

When you compare the 2 attachments, you will notice that "Rayside" is ranked #3 in the query but comes up as #4 on the report.

"Sanitation" is also ranked #4 on the query but comes up as #5 on the report; but you will notice that there are also 2 companies ranked as #5 which is not possible.

Hope that you can assist further.
Did you try doing as I suggested?

1.  Add an unbound textbox to the report.

2.  Set the controlSource property of the textbox to: =1

3.  Then set the Running Sum property of the textbox to "Over All"

This will have the added advantage of running a lot quicker, as you are not doing a bunch of lookups.
hi fyed

your suggestion above numbers the customers as they appear on the report (i.e. sequential numbering) but does not rank them by volume
It would if you sorted them by volume instead of the customer number.
hi fyed

I was thinking of that too but the only issue is that the recordsource/underlying query for the report does not contain the volume field.  

If I were to use the query that contains the volume field, the report would return all of the customers on the list but in this case I only need the top 5 customers
I don't get it, isn't that "barrels" column in the report the volume you are talking about?
hi fyed

There isn't a column called "barrels" in the underlying query for the report.

"the recordsource for the report is union_TopCustomers and then a DLOOKUP statement was created to lookup qry_CustomerRanking_BBLS and return the customer ranking where the customer numbers match"

union_TopCustomers only contains the customer number and the customer name.
Really, dlookups are so slow and inefficient.

so, what is the query you listed above used for?

Is the sample screen shot you sent all there is to this report? or is there more to it?
hi fyed

I have been trying all morning to attach the actual database but with no luck so I am attaching the database tables in MS Excel and the SQL statements in MS Word for you to take a look.  Maybe you can copy and paste the attachments into a new database and test?

- The main table is CustomerDetails with four (4) other supporting tables.

- qry_Master_FY1213 is the primary query that sums all of the stats by CustomerNumber.

- The "Top 5" queries lists the top 5 customers based on BBLS, Sales and GM.

- Those top customers are then compiled in 1 query called union_TopCustomers.

- The "Ranking" query ranks each customer by BBLS, Sales and GM.

- The report is then based on the union_TopCustomers and I am hoping to display the BBLS, Sales and GM for these top customers as well as their associated ranking (as previously attached)

There is a lot more to add to the database and the report but I need to make sure that the report returns the correct ranking per customer before continuing.

Really hope that you can help.
Avatar of staceymoore

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
was able to figure it out by searching online