Top Product (1 Viewer)

[ria_arora]

I have 160 products in a table (one columns for each product).

Tables structure is like:

RM
Customer
Prd1Rev
Prd2Rev
Prd3Rev
Prd4Rev
.....
Prd160Rev

What will be the query to find top 50 product's revenue?
And what will be the query to find top 50 product's revenue and the RM?

something like this

SELECT TOP 50 SUM(RD.REVENUE1 + RD.REVENUE2) AS REVENUE, ADV.RM, ADV.RMNAME, ADV.CODE, RD.[RM Code], RD.RM
FROM tbl_ADV AS ADV INNER JOIN tbl_Revenue AS RD ON ADV.RM = RD.RM
GROUP BY ADV.RM, ADV.RMNAME, ADV.CODE, RD.[RM Code], RD.RM
ORDER BY SUM(RD.REVENUE1 + RD.REVENUE2) DESC

Regards
Ria

 

[JamesMcS]

Hi Ria, I think you might need to think about restructuring your database, rather than having all these columns - it's going to give you a lot of headaches in the future as per other threads you've started. Rather than having all the columns you should have fields in your table like Customer, Product Number, and Revenue.

 

[ria_arora]

I know this is a very bad model but I don't have any choise.

Please let me know if you know some work around for this.

Thanks
Ria

 

[JamesMcS]

OK, but the way it's set up is going to create a lot of extra work for you... I can't think of a way to read all those columns and select the top 50 of them, so I would create a recordset-type function to append all these fields as records to a new table, and select the top 50 from there.