Originally Posted by
Ajax
no easier in vba and will be extremely slow
Code:
SELECT A.Varnummer, A.Price, Count(B.Varnummer) AS Rank
FROM UQry A INNER JOIN UQry B ON A.Varnummer=B.Varnummer
WHERE B.Price<=A.Price
GROUP BY A.Varnummer, A.Price
next you will need to link this back to UQry on Varnummer and Price. If you have same prices this will give you a problem because they will produce duplicates. No way round this without a primary key. You might be able to create a fake one by combining distributor and price into one field, it won't be indexed but will remove duplicates
from this you can now create a crosstab query, using the ranking as the column header and suggest first distributor(or the fake primary key) as the value
I strongly advise you normalise your data, use an autonumber primary key and index the price and handle bloat as a separate operation. Even then it will still be slow for the number of records you have.
Whereas simple sorting and seeing the data vertically will be pretty much instantaneous with a normalised dataset.
Hey Ajax
I appreciate the time you've taken to spell this out for me and the step-by-step process! I understand that normalizing the data might be a must, though I will give this approach a go, and if it's too slow for what is needed I will revisit your plan.
I'm having some difficulties with the Ranking, as I'm getting Ranks displayed as 20 and 25, but I only have 10 different pricefiles.
Code:
SELECT Join_distributor.Varenummer AS Varenummer, Join_distributor.Pris AS Pris, Count(Join_distributor_1.Varenummer) AS Rang
FROM Join_distributor INNER JOIN Join_distributor AS Join_distributor_1 ON Join_distributor.Varenummer = Join_distributor_1.Varenummer
WHERE (((Join_distributor_1.Pris)<=[Join_distributor].[Pris]))
GROUP BY Join_distributor.Varenummer, Join_distributor.Pris
ORDER BY Join_distributor.Varenummer;
Query in Query_design window Antal=Count:
Filtered to Varenummer called 2 (only one varenummer called that), with a rank of 25?