thanks to a colleague asking me a question about doing an access query to get rid of duplicate records, i have learned 2 new-ish tricks. well, okay. only one. but i figured out a new use for a pivot table (PIVOT!) and that counts as a trick in my book. suffice it to say, i'm still and even-more smarter than JMM.
after searching for "unique" query access, and seeing the instructions were 1000 pages long, i quickly decided this must be doable in excel. guess what? i was right. you can delete duplicate records in excel much faster. and--since his original records are in excel to begin with, who needs access???
trick #1 - select all the columns and rows in your data range, go to your data menu and use the Advanced Filter to only copy Unique Records (shows up as a checkbox on the Advanced Filter window). now, these instructions are easily downloadable from many excel-help websites and i read them over and over again...apparently, i am the only geeky excel user who DIDN'T know how to do this. (but, then again, i have never had a reason to do this). however...i kept doing them over and over and wasn't getting any results so i started to think that a) they didn't work (because, of course, i'm Not Stupid) and b) anyone who read the instructions and thought they worked was stupid. well, i was wrong on both counts. the reason it wasn't working for me is because you have to have an exact duplicate, like the entire row has to match another entire row. whereas, my data was only duplicated in specific columns, namely the first 3. so...to make a long point even more boring, what i did was shortened my data range--you have to do this manually because even if you only select a few columns in your data range, excel changes it to all the columns because excel thinks i'm stupid, too--and THEN used the advanced filter to get rid of duplicates. this gave me a count of the rows in which there were duplicates in the first 3 columns.
okay, trick #2. create a pivot table with only the columns you need, like Member #, Date, & Diagnosis Code and change your sum function to a count function; that requires excel to group any duplicate member #s and give you a count of how many dates and diagnosis they have. you can easily "count" the number of members by seeing how many rows it takes up on your new pivot-table-tab. this is actually much quicker (i think) and a lot more fun!! i love pivot tables!!! i want to be buried with a pivot table. is that possible???
Showing posts with label Access. Show all posts
Showing posts with label Access. Show all posts
Thursday, May 8, 2008
Tuesday, March 25, 2008
Ye Old Pivot Table vs. Crosstab Query
*Warning* This is a very geeky blog entry...!
subtitled: Who needs Excel anyway?
Today, I discovered the Crosstab query in Access. (I also discovered the Make Table query, but that discussion is for another time & place!) How did I discover this, you might ask... Well, it's all due to the fact that Excel will only let you import 65K records, because that's the maximum lines in a spreadsheet. Now, there are tons of "workarounds" on the web, if you're a programmer, data nerd, or techy geek (or all of the above)...but I'm just a wee little data enthusiast, definitely NOT tech savvy enough to read the numerous workarounds posted for this problem. In fact, I can't even read the questions posted regarding this problem, because they too include one of these scary "languages" called Code.
Anyway, instead of throwing my hands up in the air--which, really, I should've done given the amount of sleep I got last night--I began to think...think...think...think... You know, like Winnie-the Pooh does when encountered with a problem. I thought, there must be a way to do this in Access. (And by this, I mean a "pivot table" because that was my ultimate reason for wanting the 65,000+ records in Excel in the first place.) Guess what?? THERE IS!
Yes! I hearken back to the Access class where my teacher was like, basically anything you find yourself doing with data in Excel on a daily basis, you should be doing in Access instead. I LOVE HER FOR SAYING THAT. On the surface, Access does present intself as hard-to-understand and it's not really all that user intuitive. But, let me tell you...it is a lot easier to do difficult stuff in, as long as you're able to find a tutorial that you can understand. I mean, most of what I learned in Excel, I learned on the fly. Or, someone showed me once and I "got it." But for Access, the classes definitely help; the books help; the online tutorials help; even Microsoft's help center helps. In other words, you will need help if you are going to use this program.
Hey--that's probably the main reason why laymen AND tech savvy peeps neither one like it: they don't want to admit they need help! But me? No problemo. I love learning stuff, which--they have told me--usually involves trusting other people to teach it to you. (At first glance, it would seem this is incongruent with my stubborn, know-it-all personality. Au Contraire... Because once I have learned something, I can then pass it off as something I have known all along...)
Anyway.
What is the moral of this story? Let me recap for you:
subtitled: Who needs Excel anyway?
Today, I discovered the Crosstab query in Access. (I also discovered the Make Table query, but that discussion is for another time & place!) How did I discover this, you might ask... Well, it's all due to the fact that Excel will only let you import 65K records, because that's the maximum lines in a spreadsheet. Now, there are tons of "workarounds" on the web, if you're a programmer, data nerd, or techy geek (or all of the above)...but I'm just a wee little data enthusiast, definitely NOT tech savvy enough to read the numerous workarounds posted for this problem. In fact, I can't even read the questions posted regarding this problem, because they too include one of these scary "languages" called Code.
Anyway, instead of throwing my hands up in the air--which, really, I should've done given the amount of sleep I got last night--I began to think...think...think...think... You know, like Winnie-the Pooh does when encountered with a problem. I thought, there must be a way to do this in Access. (And by this, I mean a "pivot table" because that was my ultimate reason for wanting the 65,000+ records in Excel in the first place.) Guess what?? THERE IS!
Yes! I hearken back to the Access class where my teacher was like, basically anything you find yourself doing with data in Excel on a daily basis, you should be doing in Access instead. I LOVE HER FOR SAYING THAT. On the surface, Access does present intself as hard-to-understand and it's not really all that user intuitive. But, let me tell you...it is a lot easier to do difficult stuff in, as long as you're able to find a tutorial that you can understand. I mean, most of what I learned in Excel, I learned on the fly. Or, someone showed me once and I "got it." But for Access, the classes definitely help; the books help; the online tutorials help; even Microsoft's help center helps. In other words, you will need help if you are going to use this program.
Hey--that's probably the main reason why laymen AND tech savvy peeps neither one like it: they don't want to admit they need help! But me? No problemo. I love learning stuff, which--they have told me--usually involves trusting other people to teach it to you. (At first glance, it would seem this is incongruent with my stubborn, know-it-all personality. Au Contraire... Because once I have learned something, I can then pass it off as something I have known all along...)
Anyway.
What is the moral of this story? Let me recap for you:
- Excel has a silly limitation of 65,000 records in a spreadsheet and no inherent workaround when importing data that exceeds that limitation
- Access doesn't need your stinkin' Excel spreadsheet anyway...
- I still like Excel for formatting and makin' her pretty.
- ...but TODAY, my heart belongs to Access. LOVES.
Subscribe to:
Posts (Atom)
Posts
