Minimum Value across Multiple Columns

[OU18]

All,

How can I rank the minimum values across multiple columns in a row for each RouteID value.

ex.

Route ID A B C D E
1 .05 .06 .007 .10 .09
2 .05 .08 .099 .11 .04
3 .10 .03 .12 .002 .10

So the results would look as follows

Route ID A B C D E Min1 Min2 Min3
1 .05 .06 .007 .10 .09 .007 .05 .06
2 .05 .08 .099 .11 .04 .04 .05 .08
3 .10 .03 .12 .002 .11 .002 .03 .10

Thanks for all the help in advance.
Steve

 

[GingerR]

I suggest you normalize your table so instead it's like this:

RouteID Section Value
1 A .05
1 B .06
1 C .007
1 D .1
1 E .09
2 A .05
etc

Is this a possibility? With your db structure normalized, calculating mins and max's and everything else is a breeze. You'd just write a totals query, group on RouteID and calculate MIN of 'Value'.

 

[OU18]

GingerR,

Sorry it has taken so long to reply. The option to normalise the data is a bit of an issue.

See there are over 360,000 rows of data. To put the data into columns would generate a huge amount of rows. Having it in columns is big enough. And the number of rows grows monthly. The number of columns across could grow or it could shrink. Depends on pricing from vendors.

So if I were to use your suggestion I would then have 360,000 rows per vendor.

See we are telecom company and we have to find (Best) pricing per NPA/NXX of which we download some 360,000 records. And because it is continually being enlarged the number grows each month.

Hopefully this clears up the reason why we have it in columns as opposed to rows.

Thanks
Steve

 

[lespaul]

So, what's the problem with having 360,000 rows per vendor? We have one table with almost 1,000,000 records and another with more than 1,500,000 and that's just 2 tables out of 50 or more!

You say that the number of columns can change, so you have to restructure the table to include a new column. If you only had to add a new row, the table structure wouldn't have to be changed.

If actually changing the table structure is not an option, you could just write a query that normalizes it:

SELECT ROUTEID, A, "A" As ValueType
UNION
SELECT ROUTEID, B, "B"
UNION
SELECT ROUTEID, C, "C"

save this query: qryNormalized

and then you can do a query using qryNormalized - you'll probably want a cross-tab query to get the columns that you want to display.





Leslie

 

[OU18]

lespaul,

Thanks for the suggestion. I understand what your saying about the size of the table. Thanks for the input. I will give you normalise query a try and see how it works out.

Steve

 

[OU18]

lespaul,

That works out great, next question is, how can I get the union query to only show the top 3 for each rout id.

this would be determined on which ones have the smallest rates.

Thanks for the help again
Steve

 

[lespaul]

If the search function is working, you can search for several threads that show how to do this. I'll look around and if I can find something, I'll post a link. Otherwise, I'll see if I can remember how to do it!

leslie

 

[GingerR]

This is the best post I found about it:

http://www.tek-tips.com/viewthread.cfm?qid=788031

i thought what about if you have the same value mulitple times in the same Route? I assume you would only want it reported one time (i.e. if values = 1,1,2,3 you'd want 1,2,3 reported instead of 1,1,2). So i first wrote a Totals query that simply groups on RouteID and Value1 in order to emliminate the duplicates. I named it Query2. If you don't want this, don't write a totals query and instead substitute your table name for "Query2" below.

Then I wrote a second query based on the post I listed above:

SELECT Query2.RouteID, Query2.Value1
FROM Query2
WHERE (((Query2.Value1) In (Select top 3 t.Value1 from [Query2] as t where [Query2].Routeid = t.Routeid order by t.Value1 asc)))
ORDER BY Query2.RouteID, Query2.Value1;

sub Query2 with the name of your new totals query
sub RouteID with the name of your RouteID field
sub Value1 with the name of your Value field

My data:

RouteID	Value1
1	1
1	3
1	2
1	8
1	1
1	2
2	5
2	8
2	9
2	1
3	5
3	8
3	4
3	5

My results:

RouteID	Value1
1	1
1	2
1	3
2	1
2	5
2	8
3	4
3	5
3	8