Please, please help this poor Access beginner here. I've tried to search google world for last 3 days and no luck.
Background:
I'm building a comprehensive analysis of warranty database where I have to compare two columns of values to prioritize in product groups for our team needs to investigate.
These are;
1. First by Numbers of Failures
2. When Numbers of Failures are tied, then secondly by, Warranty Cost
Steps Taken:
I've used Subquery techniques to get what I want. But using this route was so slow for query to complete. (11,000 rows, literally freeze my comp at least 5 min).
Thus I googled and found this user on following thread talks exact, same challenge as mine, luckily got resolved using INNER JOIN technique instead as answered by other expert. Yay!
Please refer to pcreview.co.uk/forums/ranking-performance-slow-t2844316.html
Well, except.....my query is very similar to his, but I'd also like to compare 2nd column when failures are tied, thus get unique ranks for all in same group. (Above user only compares one column, and no concerns about ties, unlike me for his/her application)
Desired Output:
Table1.
Part Failures Cost Desired Rank
A 10 $5 1
A 5 $3 3
A 5 $20 2
B 5 $10 1
B 5 $5 2
B 9 $2 3
My Query
SELECT Count(*) AS Rank, dupe.Part, dupe.Failures, dupe.Cost
FROM [Table1] AS dupe
INNER JOIN [Table1] AS dupe1
ON dupe.Part = dupe1.Part and dupe.Failures <= dupe1.Failures and dupe.Cost<=dupe1.Cost
Group By dupe.Part, dupe.Failures, dupe.Cost;
I tried to tweak the red criteria for last few days without really knowing the basics of programming. (I'm mostly learning through copying others codes and try to understand it after seeing the output)
Please note, I think I have to stick to this JOIN ranking technique, as it extremely cut down query time vs subquery
Your expertise is greatly appreciated!!
Background:
I'm building a comprehensive analysis of warranty database where I have to compare two columns of values to prioritize in product groups for our team needs to investigate.
These are;
1. First by Numbers of Failures
2. When Numbers of Failures are tied, then secondly by, Warranty Cost
Steps Taken:
I've used Subquery techniques to get what I want. But using this route was so slow for query to complete. (11,000 rows, literally freeze my comp at least 5 min).
Thus I googled and found this user on following thread talks exact, same challenge as mine, luckily got resolved using INNER JOIN technique instead as answered by other expert. Yay!
Please refer to pcreview.co.uk/forums/ranking-performance-slow-t2844316.html
Well, except.....my query is very similar to his, but I'd also like to compare 2nd column when failures are tied, thus get unique ranks for all in same group. (Above user only compares one column, and no concerns about ties, unlike me for his/her application)
Desired Output:
Table1.
Part Failures Cost Desired Rank
A 10 $5 1
A 5 $3 3
A 5 $20 2
B 5 $10 1
B 5 $5 2
B 9 $2 3
My Query
SELECT Count(*) AS Rank, dupe.Part, dupe.Failures, dupe.Cost
FROM [Table1] AS dupe
INNER JOIN [Table1] AS dupe1
ON dupe.Part = dupe1.Part and dupe.Failures <= dupe1.Failures and dupe.Cost<=dupe1.Cost
Group By dupe.Part, dupe.Failures, dupe.Cost;
I tried to tweak the red criteria for last few days without really knowing the basics of programming. (I'm mostly learning through copying others codes and try to understand it after seeing the output)
Please note, I think I have to stick to this JOIN ranking technique, as it extremely cut down query time vs subquery
Your expertise is greatly appreciated!!
Last edited: