Sorting Calculated Expressions and finding top values

  • Thread starter Thread starter Natasha
  • Start date Start date
N

Natasha

Hi,

I have a list of stores that have sales and units for 2008 and 2007 being
fed to the report from a query.

Within the report I have calulated the $ per unit for 2008 and 2007.
Also within the report I have calculated the growth of the $ per unit from
2007 to 2008.

Now my report shows the following fields (3 calculated)

Store #
Product Type
2008 $/unit =[SumOfclearance_not_selling_qty]/[SumOftotal_div_qty]
2007 $/unit =[SumOfclearance_not_selling_qty1]/[SumOftotal_div_qty1]
% Growth =[CNS % 2008]-[CNS % 2007]

The report executes (I have a prompt asking for the store #) and all the
calculated fields are showing correctly.

Now, I need to see the see the data in decending order the top 50 Product
Type by store. To do that I have tried the Grouping and Sorting function to
at least get the information sorted, however, it seems to have hiccup.
After I put in the store number for which I want the report executed, it
pops up a dialogue box asking for "2008 $/unit" and "2007 $/unit"...

I don't understand why the pop up come up, and why when the report executes,
the information is not sorted.

How do I get the top 50 values in the report and have it presented in a
decending order?

I am not very good at Access and help would be greatly appreciated. Thnaks.

- Natasha
 
To sort on an item in a report it must be part of the row source, not a
calculation in the report. If your row source is a table, you will need a
query that references the table and also does the calculation. For
simplicity, let's say your table T has columns A,B and C

Let's say query Q is defined as

SELECT A,B,C, (A+B)/C As MyCalc FROM T WHERE ....

(If you design the query in the query design grid, the 4 colulmns in the
grid would look like this

Field: A B C MyCalc:(A+B)/C

In your report, you would use query Q as your row source.
You may then have it do sorting and grouping on, say, column MyCalc.


Now to get the top 50 you have to calculate all of the rows (Query Q) and
sort on the result, and then use a 2nd query that uses Q as the source, sorts
it, and then grabs just the top 50. Here's how...

To get the top 50 - define Q50 as a query based on Q where you grab all of
the data in Q, with a DESCENDING sort on MyCalc.

Q50 would read like this:

SELECT * FROM Q ORDER BY MyCalc DESC


Then in the query design (grid view) of Q50, up in the menu bar take a
look at where it has a drop down that currently says "ALL" - change it to 50.

If I remember correctly, this will change the query to read

SELECT TOP 50 * FROM Q ORDER BY MyCalc DESC

Use Q50 as your row source for the report and you will get the first 50.

To get the division totals, if you don't have them in the table (and you
shouldn't store calculated things like that anyway) you will need a query
(let's call it QT) that totals by item number for the division. Then JOIN
that query to Q by the item number so that the total for the division is
available for the calculation.

QT would be something like this

SELECT [item_number], Division, Sum([sales dollars]) As SD
FROM T GROUP BY [item_number], Division

Then in Q drag in QT and join the [item number] fields in each query and
[Division] in each query - so you will have TWO lines connecting the query
boxes in the design view.


Natasha said:
Hi,

I have a list of stores that have sales and units for 2008 and 2007 being
fed to the report from a query.

Within the report I have calulated the $ per unit for 2008 and 2007.
Also within the report I have calculated the growth of the $ per unit from
2007 to 2008.

Now my report shows the following fields (3 calculated)

Store #
Product Type
2008 $/unit =[SumOfclearance_not_selling_qty]/[SumOftotal_div_qty]
2007 $/unit =[SumOfclearance_not_selling_qty1]/[SumOftotal_div_qty1]
% Growth =[CNS % 2008]-[CNS % 2007]

The report executes (I have a prompt asking for the store #) and all the
calculated fields are showing correctly.

Now, I need to see the see the data in decending order the top 50 Product
Type by store. To do that I have tried the Grouping and Sorting function to
at least get the information sorted, however, it seems to have hiccup.
After I put in the store number for which I want the report executed, it
pops up a dialogue box asking for "2008 $/unit" and "2007 $/unit"...

I don't understand why the pop up come up, and why when the report executes,
the information is not sorted.

How do I get the top 50 values in the report and have it presented in a
decending order?

I am not very good at Access and help would be greatly appreciated. Thnaks.

- Natasha
Click to expand...
 
Back
Top