Results 1 to 2 of 2
2002-03-26, 00:37 #1
- Join Date
- Feb 2001
- Yilgarn region of Toronto, Ontario
- Thanked 0 Times in 0 Posts
Utility to summarize data values (Access97/Word97/SR2/VBA)
This UNSUPPORTED - USE AT OWN RISK template analyses an Access97 Data base, producing a statistical summary of every data field in every table in the nominated database; the data is passed by a reuseable string array (strAR()) which can be used by the developer.
I wanted a cheap'n'nasty means of inspecting a client's data before embarking on a cleanup phase. I get interested whenever a client says (of 13,000 records) "Oh, that field will only ever contain one of these four values, which we type in by hand". I like to see what other values are lurking.
For development purposes, I would harvest some of the array contents and use them to populate data validation routines, or drop-down lists.
I have chosen to dump the array to a New Document; you will be prompted by Word to save this document.
Please take the time to inspect the macro TESTAnalyseEveryField:
1. It adds a new document
2. It assumes a data base called "data.mdb", presumably a copy of a real database, which (copy) has been dragged to the template's host folder.
3. For each non-system table in the database ((dbs.TableDefs(k).Attributes And dbSystemObject) <> 0)
4. It dumps the table name as a Heading 1 paragraph (to start a new page and
5. Then calls "AnalyseEveryField"
6. AnalyseEveryFieldexamines every field in the table.
7. For each field an array (strAr) of unique data values is built, stopping when the array has found 100 unique values.
8. The array is dumped to the document, with field names in "Heading 2" style.
If you'd like to give it a try, let me know and I'll post the template here or in VBA (your call, Charlotte)
2002-03-26, 04:31 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: Utility to summarize data values (Access97/Word97/SR2/VBA)
The Wiz is the one who makes the calls around here, pardner! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> However, if this is supposed to be used on Access 97 databases, then the Access forum seems like a logical place to post it. Be our guest. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
I personally don't embark on a cleanup phase that way. I find out what they're doing (or what they *think* they're doing) and then redesign it in an iterative process until we've accounted for all their odd fields. I use queries to transfer the data once the structures are agreed upon. I do NOT do mass data conversions. Been there, done that, have the scars to show for it.Charlotte