related ranking prob.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a report built on querie that shows company_name, sales, assets, assets order. The report must shows all this fields based on sales sorted descending, the problem is: sometimes when there are 2 companies has same assets figure, assets order calculated and appear same figure, for example
Company name Sales Assets Assets orde
Comp 1 5000 10000
Comp 2 4500 12000
Comp 3 4000 9000
Comp 4 3500 12000
This is the report, comp2 & comp4 has same assets but comp2 sales is higher than comp4 sales and the assets order same for both
I need the report to show that comp4 assets order as 2 because its sales is less than comp
How?
The assets order SQL as follows
assetsord: (select count(*) from companyinfo where companyinfo.assets > comp1.assets)+
 
Make a new parameter as a quantity for ordering:

new_ord_quanty = assets * const + sales

where const should be a sufficiently big number.

NON
-----Original Message-----
I am making a report built on querie that shows
Click to expand...
company_name, sales, assets, assets order. The report must
shows all this fields based on sales sorted descending,
the problem is: sometimes when there are 2 companies has
same assets figure, assets order calculated and appear
same figure, for example:
Company name Sales Assets Assets order
Comp 1 5000 10000 3
Comp 2 4500 12000 1
Comp 3 4000 9000 4
Comp 4 3500 12000 1
This is the report, comp2 & comp4 has same assets but
Click to expand...
comp2 sales is higher than comp4 sales and the assets
order same for both.
I need the report to show that comp4 assets order as 2
Click to expand...
because its sales is less than comp2
How?
The assets order SQL as follows:
assetsord: (select count(*) from companyinfo where
Click to expand...
companyinfo.assets > comp1.assets)+1
 
Dear Ana:

Your subquery appears to try to follow the pattern of a correlated
subquery, but you do not have any aliasing.

I think your whole query might need to look like this:

SELECT [Company name], Sales, Assets,
(SELECT COUNT(*) + 1 FROM companyinfo T1
WHERE T1.assets > T.Assets
OR (T1.assets = T.assets AND T1.Sales > T.Sales))
AS [Assets order]
FROM companyinfo T

The T and T1 are aliases, allowing you to reference 2 instances of the
same table independently.

Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top