[AccessD] deduplication

Michael,

I am looking at normalizing the data.  However in many cases the client has
to determine the fields that belong in a "child" survey table.  Sometimes
you can just look at the field names and tell the groupings, sometimes not.
And of course, visually LOOKING at the data is simply not an option when
there are 50 million records and any given survey may only populate a
million of those records.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael R Mattys
Sent: Monday, January 15, 2007 7:46 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] deduplication

----- Original Message -----
From: "JWColby" <jwcolby at colbyconsulting.com> <snip>
> Thus my job is to now identify all three of these "john Colby" records 
> and merge all of the "survey" fields back into a single record, 
> deleting the two now un-needed extra records in the process.  If it 
> weren't for the fact that there are about 650 "survey" fields this 
> would not be such a big task.
<snip>
> It is specifically the ~650 fields that causes the issue for Access as 
> (I
> believe) Access cannot handle that many fields at a time.
<snip>

I think I'd still look at how to normalize the database.
Look at how to query the data such that an INSERT ...WHERE would create a
distinct table for Survey N.
Then write all 700 fields as the header to a csv Then use each survey-query
to populate the one line across.
Import into a new table when finished.

Michael R. Mattys
MapPoint & Access Dev
www.mattysconsulting.com 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com