[AccessD] Property Value is too large

John Colby jwcolby at gmail.com
Mon May 15 19:55:22 CDT 2023


In 2004 I was hired by a small and brand new company.  The four principals
were each VP level execs in four different insurance companies in Hartford,
an insurance center at the time.    One of them brought an excel
spreadsheet with several thousand disability claims to be processed.  Their
concept was to be a company which handled such claims for the big companies.

Anyway... the spreadsheet was a denormalized output, a single line of which
contained all the info for a single claim. Insurer (company),Claim,
Claimant etc.  All on a single line.  My job was to normalize that out into
about 15 tables, build a database be to hold the data, a FE with forms for
each of those 15 tables, and a small set of reports.  The employees of the
company were actively processing the claims directly in the spreadsheet at
the same time I was converting the spreadsheet to an Access database.  The
database was to be a call center app to handle disability claims from start
to finish.

Funny story (kinda) a lady (consultant) had the job before me.  One day she
just didn't show up.  They never saw her again.  I think she was in over
her head.

So they called me.  It took me around 4 months to analyze the spreadsheet,
write the queries to copy the pieces out into the normalized tables, link
the pieces back together (FKs etc), and write a script that I could just
click to perform the normalization start to finish.  And of course build a
switchboard, a main form with tabs with subforms for the child records
etc.  Reports.  Everything.  By the end of the 4 months they were getting
kinda nervous.. lots of 'when are we going to see this...

But I not only had to do all the db work, we also had to train the
employees how to use the database.

One day we told everybody to get out of the spreadsheet, clicked the
button, converted the spreadsheet to data in the database tables, and the
employees switched to using the database. Five years later the db had about
180 tables and tracked every aspect of processing a disability claim.
Doctors to diagnose the disability, lawyers involved, private investigators
to watch those suspected of fraud, check processing to tell a payment
company to calculate all of the various taxes for various states, and a
special format CSV which was emailed to a bank to tell them to cut those
checks.

All done in Access.  At the end of my involvement, about 30 people were in
the database simultaneously, all day long.  And the only real issue was
corruption issues caused by multiple edits of the same memo fields.  Access
couldn't handle that.  They moved to SQL Server (I didn't do that) and SQL
Server could handle that, the corruption issues went away.




On Mon, May 15, 2023 at 8:21 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> On 15 May 2023 at 19:11, John Colby wrote:
>
> > Yea but nobody has said what the table is...
> >
> >
>
> It's a poorly designed  (very not-normalised) major table in a large
> application which contains
> about 50 tables .
>
>  It's used by one person (the principal of a small company) as their
> primary business
> application.   It has been undergoing continuous evolution by several
> different people over
> the last 20 years :)
>
> (A couple of other list members have had input to it over the years.)
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
John W. Colby
Colby Consulting


More information about the AccessD mailing list