need band-aid for poorly constructed db

[cloclo]

Hello,
I have been assigned to fix some problems on Access databases. Unfortunately
it doesn't look like they were created by someone who is schooled in database
design. One in particular is giving me a headache. Here is the scenario:

A form using a multi-table join is presented to the user - it is a list of
patients. One user wants to update ColumnA and another user wants to update
ColumnB. The patient should have only one row on the main table, but once
both users perform their updates, there are actually 2 rows on the table. In
one row, there is data in ColumnA and no data in ColumnB. On the other row,
there is no data in ColumnA and there is data in ColumnB. One row is
selected for display on a report. No matter which row is selected, there is
data missing in one of the columns and so the report is wrong.

Now if I were being given time to properly fix this, I would add a key to the
main table to ensure a unique row for each patient. I would then need to
rework the form, and I'm assuming other parts of the database as well that
use this table. It's a significant change that I think would require some
user testing. My boss doesn't want me spending much time on this because
it's only one of a dozen other "high-priority" problems I need to fix in the
next couple of days, and there is no chance of any user testing! I was kind
of toying with the idea of writing a macro that runs when the form closes,
that collapses the data to look like it "should" look. I know that isn't the
ideal way it should work, but what do you think?

 

[rolaaus]

I would recommend trying to explain to your boss the entirety of the problem,
and explain that cutting corners is probably what got them into this mess in
the first place .. of course being diplomatic about it, and not just saying
that their problem is they are cheap and lazy and nothing will ever be done
right unless they change their ways, which is exact what they need to hear

I could offer to provide some outside expertise ... I'm not sure your level
of knowledge or experience, so I don't know if this is insulting or not, but
I've been doing Access stuff for about 15 years and have experience all the
way back to ver 2.0 But I don't know if your boss would go for that. I even
have Access 2010 running on my development machine

The original person that created this could have been considering the
multi-user aspect of having more than one person editing the same record.
I'm not agreeing with this reasoning, but that could have possibly been why
they did what they did.

Depending on the complexity of the application, it could be properly and
completely fixed, without too much headache. I would be willing to take a
look at give your boss a fixed price quote. You can strip out any PII or
send any NDA via fax, if you'd like, contact me directly.

 

[Fred]

Dear Rolaaus,

Theses forums are for free advice.

If you are as new to this forum as it appears I assume that you were unaware
of that.

 

[Fred]

Dear CloClo,

Your post left out the fundamental structural information and used some
ambiguous terminology in key areas, too the point of being self-conflicting
if one tries to follow what you said rigorously. I don't think it will be
possible to help until you clarify those areas.

Could you describe the structure and and linkages in the main tables(s)
relevant to your inquiry?

- - - -

In the meantime, I'm taking a wild guess that you have a single patient
table, and have people entering the same person twice. If so, 3/4 of your
solution will need to be people training.....rule #1 for entering a new
record is making sure it's not already in. If you are using a unique
identifier (e.g. SSN) for the person, then you could just set that field up
as a PK or index set to unique. Otherwise, you could try setting a
multi-field index set to unique (like maybe last name and DOB)

 

[Jeff Boyce]

Although rolaaus offered a fix for a fee, s/he had some advice you might
want to seriously consider ...

If you believe you could figure out a way to 'band-aid' this one problem
successfully, you might gain credibility with your boss and parlay that into
a discussion of the implications/ramifications of using a band-aid approach
on something that might be more serious.

After all, it's your bosses' data and business, right? How will s/he react
if the system goes down and you aren't available?

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.