[AccessD] Clients and money

OK. But years ago, before ACCESS or FOXPRO etc, I used to write Database
Records using READ, PRINT, WRITE, WRITE APPEND, etc with a hash generated
key.
Could you perhaps do something similar using the AutoNumber as the key.
Don't ask me how to do it now, too many vodkas passed in the last 10 years
or so, but it should be achievable, 'cos that is how we used to create
databases.  No bloat etc.  Don't know about speed of access etc but it is
*fairly* low level stuff so should be pretty fast (particularly if compiled
and called once via an API and then left open for read/write).  I used to
use QuickBasic and similar to do it.  The Hash Key could be the Prime ID + a
Notes Key.

Even though you say you use JIT to minimise traffic, there must be tons of
memo content dragged over from what you are saying below.

>>TOP SECRET stuff happens there don't you know.  ;-)
Your not holding our two missing government CDs by any chance?  I know some
data went out to Omaha (is that spelt right?).

Max
Ps. If you could achieve this, what would that do for your reputation. 'The
fastest Call Centre in the West standing alongside the coolest water
cooler'.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, January 10, 2008 6:59 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Clients and money

Max,

>My answer is: What are happening to create the fragmentation? ...Are 
>they
using memo fields to write all sorts of 'comments' based on the telephone
calls.

Yes, they use memo fields everywhere.  This isn't one table it is hundreds.
It is not a single field it is dozens in many of the tables.  It is often
not something that be contained in 255 characters.  I understand
normalization, and there are ~75 fields in the CLAIM table, ~40 fields just
in the claimant table, then there is policy, policy holder, insurer,
underwriter and so forth ad nasium

Memos serve a purpose, storing data where the potential exists for more than
255 characters of input.  I am careful not to use them for things like name
or address but there are still dozens of memo fields in this database.  In
fact I do use entire other databases to store data from the file imports,
however using a separate db just for memo fields just wouldn't work imo.

This is a call center database but a very complex one.  The main form has
about 20 tabs, each tab has at least one and occasionally more child forms
on it, child to claim.  In at least one case the subform has a tab with
about 6 subforms on it.

There is a LOT of data to be captured and tracked in order to process an
insurance claim, and disability claims have to be processed every month, and
in the case of short term disability, every week (they get a check weekly).
All of it has to be "a click away" because they are talking to the claimant
and discussing parts of their claim, live, on the phone.

It is really a lovely little application.  There are 115 "events" (so far)
that can happen in the claim, everything from open / close / archive /
reopen etc to mail received, document sent or received, surveillance
requested, claim approved / denied, data exported to the insurer, data
imported from the insurer etc.  Each event can cause a status change to the
claim (a review event may cause a denied status or a reopen status).  Events
need memo fields to explain what happened to trigger the event.  Events
occur daily in some cases and at some stages of processing.  Then there are
"diaries" as they call them, tickler records to remind them in 60 days to
send a follow-up letter or check whether medical records ever came in.
Certain processes (mail merge for example) can automatically trigger events
(saying that a specific document was sent to a specific doctor, or a form
was sent to the claimant), and events can automatically trigger and fill in
diaries to prompt the users to do specific things at a specific date in the
future.  Diaries require a memo to hold what needs to happen, and what
actually happened when the diary popped up.  Then there is the "claim
contact" table where notes about telephone, email, fax and any other
contacts are made with the claimant, lawyers, doctors, family members etc.
Memo fields to write these notes.  Payments sent, payments canceled, checks
voided, overpayments, recovery efforts...  get the picture.  

This thing is so complex that they use it "live" as a demo to potential
clients to get new business.  The insurers go away astonished that a company
of 60 people can have a database that does all of that stuff, and even more
astonished that they don't have such an application.  Of course they are not
allowed to see the data center.  TOP SECRET stuff happens there don't you
know.  ;-)

So yea, there is fragmentation of the database itself (compact / repair
required regularly).

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 Max Wanadoo
Sent: Thursday, January 10, 2008 12:59 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Clients and money

My answer is: What are happening to create the fragmentation?
800 = 1 Gig (give or take a byte or two) So it will be easy to fragment a BE
that has lots of COPY TO (which is what essentially memo fields do).  Are
they using memo fields to write all sorts of 'comments' based on the
telephone calls.  If so, how's about having a table dedicated to memo
fields, and link that to the main table and then compact/repair that table
each time the form with the table is exited (or sommat like that - lateral
thinking, etc)...OR.. Giving each user a FE for the memo fields in a
tblMemos table and then copy that as an append to the BE....OR...putting
comments into a TEXT file using WRITE?  Keep on thinking, Max... Always more
than one way to skin a problem....

Max

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, January 10, 2008 5:39 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Clients and money

>Are they doing *other* things on the disk where the BE is stored.

LOL.  The BE (800 megs compacted) is on a 60 gig hard drive that had 6 gigs
left. 

Now, what is YOUR answer to that question?

;-)

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 Max Wanadoo
Sent: Thursday, January 10, 2008 12:17 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Clients and money

Hmmm, interesting.  I don't really understand how the BE will be fragmented
unless there are lots of memo fields in there.
Also, a reasonably large disk should not need defragging for ages.  Mine has
been going years and still does not need defragging.  Are they doing *other*
things on the disk where the BE is stored.  If so, it might be worthwhile
putting the BE on its own drive.

With regard to the COSTS.  Ask they question, "Which or your activities
generate the most income?"  When they start saying anything other than the
DB, then ask them "Tell me, if the DB stopped completely, what would the
impact be on your bottom line?".  Then ask the question "Do you have a
Disaster Recovery Plan that is regularly tested and checked?".  Take it from
there.

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