Select Top N from multiple columns (1 row) or sort columns

[JS]

Hi,
I have a row of data like below:
Frequency1 Frequency2 Frequency3 Frequency4......
2 5 7 3

I need to pick the top 10 (highest numbers) in the row (7, 5, 3, 2
etc.). How do I do it?
Can I sort and do Top N for multiple columns as you can do for multiple
rows (1 column)?

Thank you so much!
JS

 

[Tom Wickerath]

JS -

You have an incorrect database design. You should not have multiple fields
that describe the same type of data. Your job will be made MUCH easier if you
take the time to redesign your database.

You should spend some time gaining an understanding of database design and
normalization before attempting to build something in Access (or any RDBMS
software for that matter). Here are some links to get you started. Don't
underestimate the importance of gaining a good understanding of database
design. Brew a good pot of tea or coffee and enjoy reading!

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)


Tom
__________________________________________

:

Hi,
I have a row of data like below:
Frequency1 Frequency2 Frequency3 Frequency4......
2 5 7 3

I need to pick the top 10 (highest numbers) in the row (7, 5, 3, 2
etc.). How do I do it?
Can I sort and do Top N for multiple columns as you can do for multiple
rows (1 column)?

Thank you so much!
JS

 

[JS]

Tom,
You are right about the importance of database design. Actuall the the
columns I showed you are the sum (using a select query) of thousands of
differnet rows (with each cell has a 1 or 0).
I need to find out which 10 columns have the highest sum.

Hope this makes it clearer.

Thanks,

 

[Tom Wickerath]

Try the following KB article:
How to Find Minimum or Maximum Value Across Fields of Record
http://support.microsoft.com/?id=209857

I'm pretty sure that your database design is still not correct. If you'd
care to send me a zipped copy, I'll take a look at it and be in a better
position to say for sure.


Tom

QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom,
You are right about the importance of database design. Actuall the the
columns I showed you are the sum (using a select query) of thousands of
differnet rows (with each cell has a 1 or 0).
I need to find out which 10 columns have the highest sum.

Hope this makes it clearer.

Thanks,

 

[Tom Wickerath]

On second thought, even that sample isn't going to get you what you want. It
just helps you identify the minimum or maximum, but not the top 10.

You will need to write a custom VBA function to do this. You can likely
start with the KB article method as a template, and modify from there.

Tom
__________________________________________

:

Try the following KB article:
How to Find Minimum or Maximum Value Across Fields of Record
http://support.microsoft.com/?id=209857

I'm pretty sure that your database design is still not correct. If you'd
care to send me a zipped copy, I'll take a look at it and be in a better
position to say for sure.


Tom

QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom,
You are right about the importance of database design. Actuall the the
columns I showed you are the sum (using a select query) of thousands of
differnet rows (with each cell has a 1 or 0).
I need to find out which 10 columns have the highest sum.

Hope this makes it clearer.

Thanks,