claytont454
New Member
- Joined
- Feb 28, 2013
- Messages
- 8
I'm hoping someone here knows a better way than my current method that takes about 6 hours of messing around between Excel and Access as a beginner/intermediate user.
I'm working with a table that is about 15000 rows by 18 columns.
Column A is a person's initials, and the rest of the row shows a code for different activities that person did in a single day.
Since this represents several months, each person's initials appears in column A of the table many times.
Sometimes they only have 1 activity for the day, or they may have 15 or 20 in a day.
There are thousands of potential "codes" that may appear in the rest of the columns, and each has a specific meaning.
This is from the healthcare world, so some codes start with a 0, others start with a letter, and they may have one or two or no decimals, and each is meaningful.
My task is to create a list for each person that shows how many times each code appeared for them on all the days represented. Here is a short snippet of my raw data (this is junk data, so there's no confidentiality issues):
<tbody>
</tbody>When I sort by Column A, 'MB' might have 75 rows, 'AEB' might have 113 rows, and so on. In the end, I want to show them sorted like this with each value and how many times it appears for that person, then the next person and the same list for them. The ideal would be to just show the top 10 for each, but that may be too much to ask.
AEB
<tbody>
</tbody>
MM
<tbody>
</tbody>and so on. Any help you can offer is appreciated!!!
I'm working with a table that is about 15000 rows by 18 columns.
Column A is a person's initials, and the rest of the row shows a code for different activities that person did in a single day.
Since this represents several months, each person's initials appears in column A of the table many times.
Sometimes they only have 1 activity for the day, or they may have 15 or 20 in a day.
There are thousands of potential "codes" that may appear in the rest of the columns, and each has a specific meaning.
This is from the healthcare world, so some codes start with a 0, others start with a letter, and they may have one or two or no decimals, and each is meaningful.
My task is to create a list for each person that shows how many times each code appeared for them on all the days represented. Here is a short snippet of my raw data (this is junk data, so there's no confidentiality issues):
| Name | SDC | SDC1 | SDC2 | SDC3 | SDC4 | SDC5 | SDC6 | SDC7 | SDC8 | SDC9 | SDC10 | SDC11 |
| MB | 729.5 | V57.1 | V12.72 | 414.00 | 250.00 | 401.1 | 272.4 | 333.94 | 600.90 | |||
| MM | 729.5 | 719.41 | V57.1 | |||||||||
| DLV | 724.2 | V57.1 | ||||||||||
| AEB | 723.1 | 781.2 | V57.1 | 295.90 | 318.0 | 781.0 | 715.96 | 244.9 | 401.9 | 272.4 | V74.1 | V04.81 |
| AS | 727.41 | V57.21 | 354.0 | |||||||||
| SLM | 959.4 | V57.21 | V62.84 | V62.85 | 244.9 | 493.90 | 304.80 | 296.90 | ||||
| TM | 340 | 729.89 | 726.2 | V57.1 | ||||||||
| MB | V76.51 | 562.10 | V16.0 | |||||||||
| JK | 847 | V57.1 | 569.0 | 455.2 | 455.3 | V16.0 | ||||||
| AH | 840.9 | V57.1 | ||||||||||
| ... |
<tbody>
</tbody>
AEB
| E000.9 | 15 |
| 558.9 | 8 |
| 401.9 | 8 |
| E849.0 | 7 |
| 276.51 | 6 |
| E849.9 | 6 |
| 250 | 6 |
| 780.6 | 5 |
| 303 | 5 |
| 79.99 | 5 |
<tbody>
</tbody>
MM
| V76.44 | 19 |
| V65.3 | 18 |
| 733 | 18 |
| 780.79 | 17 |
| 414 | 17 |
| 211.3 | 16 |
| 599 | 16 |
| 300 | 16 |
| 276.1 | 15 |
| 724.5 | 15 |
<tbody>
</tbody>
