Archiving Entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian Mosher - 12 Jun 2007 18:26 GMT

I have a very simple form in Access 2000 that a user can enter information
about IV Pumps and keep track of them. They enter the patient, facility,
serial #, date it went out, date it came in, date it needs to be serviced,
etc. We have to keep a running history of that dates it came back to us, and
went out to a patient though. Can anyone think of a simple way that each time
they enter a date in these 2 fields, it could be kept in another table, and
referred back to as necessary? So when they go to the record in the form, it
would show the most recent entries, but they could click on something to
allow them to see what dates it has been checked in and out in the past. It
seems really simple in theory, and I'm sure it's easy to accomplish, but it's
been years since I've done any sort of developing in Access. The ideas are
not coming to me.

Thanks,

Brian Mosher

Reply to this Message

KARL DEWEY - 12 Jun 2007 23:39 GMT

You need to tables - IV Pump, Issue_Return, and PumpMaint.
IV Pump ---
PumpID - autonumber - primary key
serial - text
Model - text
DateInSvc - DateTime

Issue_Return ---
IssueID - autonumber - primary key
PumpID - number - integer - foreign key
Patient - text
Facility - text
DateOut - DateTime
DateIn - DateTime
Remarks - text or memo

PumpMaint ---
MaintID - autonumber - primary key
PumpID - number - integer - foreign key
Type - text - repair, calibration, etc.
MaintDate - DateTime
Remarks - text or memo

Use form/subform for pump/issue and pump/maint. The query for the issue to
pull records without a DateIn. Form maint subform on open goto last record
sorted in date order.

Signature

KARL DEWEY
Build a little - Test a little

> I have a very simple form in Access 2000 that a user can enter information
> about IV Pumps and keep track of them. They enter the patient, facility,
[quoted text clipped - 12 lines]
>
> Brian Mosher

Reply to this Message

Jamie Collins - 13 Jun 2007 10:30 GMT

On Jun 12, 11:39 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> You need to tables - IV Pump, Issue_Return, and PumpMaint.

"Need"? Considering this is a medical-related post, are you
prescribing a solution or merely posting a proposal? If the former,
I'd take issue with your lack of keys (patient = text, a pump can be
'issued' to multiple patients, etc) and constraints (a pump can be
simultaneously in the maintenance shop and 'issued' to a patient,
etc). If it's the former, perhaps you could make this clear. TIA.

FWIW I wrote a IV pump module for a prescribing system and *needed*
multi-million USD indemnity insurance cover.

Jamie.

--

Reply to this Message

KARL DEWEY - 13 Jun 2007 15:13 GMT

>>a pump can be simultaneously in the maintenance shop and 'issued' to a

patient
I see your point so reduce the number of tables.
Issue_Return ---
IssueID - autonumber - primary key
PumpID - number - integer - foreign key
Action - text - issue, repair, calibration, etc.
OutTo - text
DateOut - DateTime
DateIn - DateTime
Remarks - text or memo

>>FWIW I wrote a IV pump module for a prescribing system and *needed*

multi-million USD indemnity insurance cover.
Why did you not impart some of your wisdom in answering the individual’s
inquiry?

Signature

KARL DEWEY
Build a little - Test a little

> On Jun 12, 11:39 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 13 lines]
>
> --

Reply to this Message

Jamie Collins - 13 Jun 2007 17:00 GMT

On Jun 13, 3:13 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> >>a pump can be simultaneously in the maintenance shop and 'issued' to a
>
[quoted text clipped - 8 lines]
> DateIn - DateTime
> Remarks - text or memo

I don't think you do because in your revised schema a pump can still
be simultaneously in the maintenance shop and issued to a patient e.g.

INSERT INTO Issue_Return (PumpID, Action, OutTo, DateOut, DateIn)
VALUES (1, 'Issue', 'Patient', DATE(), NULL)
;
INSERT INTO Issue_Return (PumpID, Action, OutTo, DateOut, DateIn)
VALUES (1, 'Repair', 'Repairman', DATE(), NULL)
;

So where is the pump today? Garbage in, garbage out but it's your job
as designer to ensure the garbage doesn't get in. I can even issues
the pump to the repairman! "Reduce the number of tables" is hardly the
correct response.

> Why did you not impart some of your wisdom in answering the individual's
> inquiry?

Because the OP's isn't a prescribing system, because I'm no longer
insured to give such advice, because I don't want to encourage
sponsors of life-critical systems to design-by-email, because
interaction with electronic patient records demands a complex data
model (which the OP has not posted) and a proper audit trail (which
you haven't posted), etc. If you think you can do it you'll have to
put up with me pointing out the flaws in your design :(

Jamie.

--

Reply to this Message

Brian Mosher - 13 Jun 2007 17:39 GMT

Yea, this is truly a simple database to keep track of where our pumps are.
This has absolutely nothing to do with dispensing. Our dispensing system is a
completely separate program that I have nothing to do with. This is a network
admin telling someone "I'll put a database together for you to take place of
this tiny insignificant module in an old system we aren't using anymore and
are decomissioning". The only thing we HAVE to be able to show is the dates
they were issued and returned. And tecnically they could keep up with this on
paper. We only have about 70 pumps. Just trying to make it as simple as
possible for them. No "life-critical system" here, no "complex data model".
Hence my thought to ask this simple question on a message board.

I've played around with the 2nd table a little bit, but can't get it to
quite work the way i want. I'll get it to work sooner or later.

> On Jun 13, 3:13 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 40 lines]
>
> --

Reply to this Message

Jamie Collins - 14 Jun 2007 09:07 GMT

On Jun 13, 5:39 pm, Brian Mosher
<BrianMos...@discussions.microsoft.com> wrote:

> This is a network
> admin telling someone "I'll put a database together for you to take place of
> this tiny insignificant module in an old system we aren't using anymore and
> are decomissioning". The only thing we HAVE to be able to show is the dates
> they were issued and returned. And tecnically they could keep up with this on
> paper.

Presumably the issued and returned dates HAVE to have consistent and
you HAVE to record an accurate audit trail ('archive'): pump not
recorded as being issued to two patients, pump not recorded as being
simultaneously in use and being in repaired, etc. I don't see these
featuring in the proposed design. Buyer beware!

No offence but I'd expect a requirements spec to be written by a
domain expert in the sense of the business, rather than a domain
expert (pun intended) in the sense of a 'network admin'.

Take a look at this simple example of a petty cash reimbursement to an
employee:

Business Rules Require Real-World Identifiers
http://www.inconcept.com/JCM/May1998/sharp.html

"If an auditor checked to see if reimbursements were duplicated she/he
would look at only the non-key columns because the [autonumber] key
column is by definition unique. If duplicates are found, the auditor
would not know: if they were two legitimate purchases, if the same
purchase was entered twice by mistake, or if the employee tried to
commit fraud... Enforcing this auditing rule at the time of submission
eliminates a mistake that could be very embarrassing to an employee.
If an employee had several requests and was distracted during entering
them, a request could be entered twice. Explaining this to the boss or
a higher manager would not be a highlight of the employee's day."

Make a similar matrix for the proposed Issued_Returned table and I
think you'll find a lot of No's in the Allowed? column, indicating
missing constraints.

If the software isn't enforcing the rules then why not indeed "keep up
with this on paper"?

> This has absolutely nothing to do with dispensing. Our dispensing system is a
> completely separate program that I have nothing to do with.

Thanks for confirming :)

> No "life-critical system" here

That one should be in this group's FAQ <g>.

Jamie.

--

Reply to this Message