Design advice, please.

[Stephen Glynn]

I've recently, with a great deal of help from this group, been trying to
set up a system for an acupuncturist to keep records of what points he's
used in each session with his patients.

Doubtless I'll be back with several more questions as the project
progresses but I'm a bit puzzled about part of the design concept.

There are about 360 points he uses (not all in each session, obviously).
He wants to be able to click on check boxes in a form to record which
points he's used in each session.

There seem to me to be two ways of handling this.

One is to have a tblNeedlings with an autonumber NeedlingID primary key,
a NeedlingName, NeedlingDescription (both text fields) and a check box
for USED, bind each check box on the form to a record in the table,
then run an append query based on tblNeedlings with the criterion
"tblNeedlings.USED = True" to add the NeedlingID to a
tblTreatmentRecords followed by an update query to reset all the USED
fields in tblNeedlings to "False".

The other -- suggested by several people in this group -- is to use
unbound checkboxes, set their tag property to correspond with a
NeedlingID and run the append query from code based on the entries on
the form.

I've set it up both ways in alternative versions and I'm not completely
happy with either. The first way (basing it on a temporary entry in
the table) means, AIUI, only one person can use the system at a time --
not a problem in this case but it could be if his practice expands and
he takes on a partner. The second way means I've the same information
stored in both the table and the form and that I'm not actually using
the table for very much at present, which feels all wrong.

Anyone got any advice?

Steve

 

[rkc]

I've recently, with a great deal of help from this group, been trying to
set up a system for an acupuncturist to keep records of what points he's
used in each session with his patients.

Doubtless I'll be back with several more questions as the project
progresses but I'm a bit puzzled about part of the design concept.

There are about 360 points he uses (not all in each session, obviously).
He wants to be able to click on check boxes in a form to record which
points he's used in each session.

There seem to me to be two ways of handling this.

One is to have a tblNeedlings with an autonumber NeedlingID primary key,
a NeedlingName, NeedlingDescription (both text fields) and a check box
for USED, bind each check box on the form to a record in the table,
then run an append query based on tblNeedlings with the criterion
"tblNeedlings.USED = True" to add the NeedlingID to a
tblTreatmentRecords followed by an update query to reset all the USED
fields in tblNeedlings to "False".

The other -- suggested by several people in this group -- is to use
unbound checkboxes, set their tag property to correspond with a
NeedlingID and run the append query from code based on the entries on
the form.

I've set it up both ways in alternative versions and I'm not completely
happy with either. The first way (basing it on a temporary entry in
the table) means, AIUI, only one person can use the system at a time --
not a problem in this case but it could be if his practice expands and
he takes on a partner. The second way means I've the same information
stored in both the table and the form and that I'm not actually using
the table for very much at present, which feels all wrong.

Anyone got any advice?

Your problem with method one is solved by adding a currentUser field
to the temporary entry table and adding a Where currentUser = clause to
your update query.

 

[rkc]

Your problem with method one is solved by adding a currentUser field
to the temporary entry table and adding a Where currentUser = clause to
your update query.

Opps. Never mind. Mis-interpreted method one.

 

[John Vinson]

I've recently, with a great deal of help from this group, been trying to
set up a system for an acupuncturist to keep records of what points he's
used in each session with his patients.

I can see that there would be some 'point' to doing so... <g, d & r,
don't needle me for being an incurable punster!>

Doubtless I'll be back with several more questions as the project
progresses but I'm a bit puzzled about part of the design concept.

There are about 360 points he uses (not all in each session, obviously).
He wants to be able to click on check boxes in a form to record which
points he's used in each session.

Checkboxes might not be the best choice - 360 checkboxes on a form is
a WHOLE LOT. Would a listbox, or perhaps two cascading listboxes,
perhaps be better?

There seem to me to be two ways of handling this.

One is to have a tblNeedlings with an autonumber NeedlingID primary key,
a NeedlingName, NeedlingDescription (both text fields) and a check box
for USED, bind each check box on the form to a record in the table,
then run an append query based on tblNeedlings with the criterion
"tblNeedlings.USED = True" to add the NeedlingID to a
tblTreatmentRecords followed by an update query to reset all the USED
fields in tblNeedlings to "False".

That's not going to work: you can't "bind a check box to a record".
The table design is good, but the USED field isn't needed - the table
should instead have fields for the SessionID and the NeedleID. You'ld
simply add a record to the table for each point used. If a patient
needed twelve points in a given session, there would be twelve records
for just those points. You could use a continuous Subform with a combo
box allowing the selection of a different needle for each record.

The second way means I've the same information
stored in both the table and the form and that I'm not actually using
the table for very much at present, which feels all wrong.

Clarification: you have NOTHING stored in the form. Forms cannot store
data - or at least only transiently, like a scratchpad.

 

[Stephen Glynn]

I can see that there would be some 'point' to doing so... <g, d & r,
don't needle me for being an incurable punster!>



Checkboxes might not be the best choice - 360 checkboxes on a form is
a WHOLE LOT. Would a listbox, or perhaps two cascading listboxes,
perhaps be better?




That's not going to work: you can't "bind a check box to a record".
The table design is good, but the USED field isn't needed - the table
should instead have fields for the SessionID and the NeedleID. You'ld
simply add a record to the table for each point used. If a patient
needed twelve points in a given session, there would be twelve records
for just those points. You could use a continuous Subform with a combo
box allowing the selection of a different needle for each record.

Can we expand on this? I've got a tblSessions, which has a SessionID,
PatientID, TherapistID (even though there's only one of him at the
moment) and SessionDate. I've also got a tblNeedlingSessions which
stores the relevant SessionID and NeedlingID.

The guy's asked for yes/no boxes (and I've got the form set up using tab
pages -- a real pain that was) but I can see it would make more sense to
have use a multiple selection combo box. Rather than having all 362
points in one box I think I'd rather have a separate box for each group
of points (acupuncture apparently groups points into Lung Points, Heart
Points and so on) which I could achieve by populating the combo boxes
from queries with the criteria LIKE "LU*" or LIKE "HT*" to return Lung
points LU1, LU2 ... or Heart points HT1, HT2....HTn, and so forth.

What would the code look like to append the selected points from each
combo box to the NeedlingID field in tblNeedlingSessions?

Clarification: you have NOTHING stored in the form. Forms cannot store
data - or at least only transiently, like a scratchpad.

Sorry. I expressed that badly. I've got, at present, all my
NeedlingIDs as tags for the checkboxes and an append query that appends
the tag property for each checkbox on the form to
tblNeedlingSessions.NeedlingID if that box is checked.

Steve

 

[John Vinson]

Rather than having all 362
points in one box I think I'd rather have a separate box for each group
of points (acupuncture apparently groups points into Lung Points, Heart
Points and so on) which I could achieve by populating the combo boxes
from queries with the criteria LIKE "LU*" or LIKE "HT*" to return Lung
points LU1, LU2 ... or Heart points HT1, HT2....HTn, and so forth.

What would the code look like to append the selected points from each
combo box to the NeedlingID field in tblNeedlingSessions?

I'm not quite certain what you're appending *from* so I can't be sure.

Without knowing clearly what views your client would find acceptable,
I'm not certain what to advise; what I had in mind for the linked
controls would be a Combo Box (or a Listbox, maybe better) with the
categories (Lung Points, Heart Points, ...). In its AfterUpdate event
you would simply requery a Points combo box, which would be based on a
query referencing the first combo box; instead of

LIKE "LU*"

you could use

LIKE [Forms]![YourForm]![cboTypes] & "*"

or have your points table include a field for the type and simply use
= with no wildcard rather than Like.

With the unbound checkboxes... I can't see any good way other than a
fairly painful VBA routine enumerating the form controls. You might
make this a bit simpler by matching the control names to the desired
fieldnames.

 

[lbrinkman]

One problem is that an Access table cannot have more than 256 fields; so if
you want check boxes for 360, you will need to have two (2) tables. It can
be done this way: just have 2 tables in a one-to-one relationship and two
forms (at least) - one for each table.

I assume that you have 4 tables right now: Patients; Sessions; Therapists (a
look-up table); and Needlings (a look-up table). I would add another table:
tblSessionNeedlings to hold the needling sites actually used per session.
Normally, I would have a Sessions form (frmSessions) with a SUBFORM (in
spreadsheet mode) in which the user would select the needling sites from a
combox box (within the spreadsheet subform). However, again we run into the
256 field/table limit. What I would do is have TWO combo boxes based on the
two Needling tables (tblNeedling1 and tblNeedling2) on the frmSessions:, one
combo box based on tblNeedlings1 and the other combo box based on
tblNeedlings2. (The table tblNeedlings1 would hold 180 needling sites; the
table tblNeedlings2 would hold the other 180 sites. The same would be true
of the 2 combo boxes. Then using the AfterUpdate event for both combo boxes,
when a needling site is
selected from from either combo box, it would be added to the subform (in
spreadsheet format) based on tblPatientNeedlings.
---Phil Szlyk

 

[Stephen Glynn]

Rather than having all 362
points in one box I think I'd rather have a separate box for each group
of points (acupuncture apparently groups points into Lung Points, Heart
Points and so on) which I could achieve by populating the combo boxes

from queries with the criteria LIKE "LU*" or LIKE "HT*" to return Lung

points LU1, LU2 ... or Heart points HT1, HT2....HTn, and so forth.

What would the code look like to append the selected points from each
combo box to the NeedlingID field in tblNeedlingSessions?

I'm not quite certain what you're appending *from* so I can't be sure.

Without knowing clearly what views your client would find acceptable,
I'm not certain what to advise; what I had in mind for the linked
controls would be a Combo Box (or a Listbox, maybe better) with the
categories (Lung Points, Heart Points, ...). In its AfterUpdate event
you would simply requery a Points combo box, which would be based on a
query referencing the first combo box; instead of

LIKE "LU*"

you could use

LIKE [Forms]![YourForm]![cboTypes] & "*"

or have your points table include a field for the type and simply use
= with no wildcard rather than Like.

With the unbound checkboxes... I can't see any good way other than a
fairly painful VBA routine enumerating the form controls. You might
make this a bit simpler by matching the control names to the desired
fieldnames.

Sorry. I'm not making myself very clear and I know I ought to
understand how this works but for some reason I can't get my head round it.

The client wants to record the fact that in a particular session he's
used about 10 or 15 of the 362 available points on his patient. He
and I are still playing around with the easiest ways for him to do this.
One option, which simulates what he's doing at the moment with paper
and pen, is to have a series of schematics of (e.g.) the right and left
hands and have check boxes at about where on these hands the various
points are to be found. Alternatively, he might select from a list
box or boxes points Lung 1, Heart 2, and so on.

One way or the other he wants, at the end of the session, to append to
tblNeedlingSessions from the checked boxes/selected items in the list
boxes on the form the information that the following points have been
used in this session.

I suppose what I'm trying to do is reproduce an ordering system with
tblOrders, tblOrderDetailsExtended and tblProducts only with either a
check box for each individual product or a series of list boxes for each
individual product category. What's slightly complicating the matter
is that having a separate "category" table seems very artificial in that
the categories are inherent in the NeedlingName field (e.g. LU1 is a
Lung point, HT1 is a heart point).

The answer, I know, is staring me in the face but I can't see it.

Steve

 

[Stephen Glynn]

Sorry. I've not made myself clear. The structure you suggest is
pretty much what I've got at the moment. There's a tbleNeedlings, which
has three fields (autonumber ID, NeedlingName and Needling Description)
and 362 rows, one for each point)

What I'm trying to do is use a form to record which of the available 362
points is used in any session and then to store this information in a
tblNeedlingSessions. This contains, at present, three fields:
SessionID (which links to tblSessions which is where I'm storing the
patientID and therapistID along with session time and date), NeedlingID
and a memo field for notes on the particular session.

At present I've got a form containing (among other things)362 check
boxes, each with a a tag property corresponding to a particular
needlingID. As he uses a particular needle point on a patient the
therapist clicks the appropriate box and, when he finishes the session
he clicks a command button that runs some code to look at each check box
on the form and, if it's been checked, appends its tag property to
tblNeedlingSessions along with the NeedlingID and any notes on the session.

This is working OK, at least in my prototype.

However, what's got me worried is that by doing it this way I'm not
actually using tblNeedlings for anything since, if a box is checked, my
code looks not at anything in tblNeedlings but at the tag property of
the individual control. I could now delete tblNeedlings and the
application would work perfectly well without it.

This feels all wrong.

Steve

 

[rkc]

Sorry. I've not made myself clear. The structure you suggest is
pretty much what I've got at the moment. There's a tbleNeedlings, which
has three fields (autonumber ID, NeedlingName and Needling Description)
and 362 rows, one for each point)

What I'm trying to do is use a form to record which of the available 362
points is used in any session and then to store this information in a
tblNeedlingSessions. This contains, at present, three fields:
SessionID (which links to tblSessions which is where I'm storing the
patientID and therapistID along with session time and date), NeedlingID
and a memo field for notes on the particular session.

At present I've got a form containing (among other things)362 check
boxes, each with a a tag property corresponding to a particular
needlingID. As he uses a particular needle point on a patient the
therapist clicks the appropriate box and, when he finishes the session
he clicks a command button that runs some code to look at each check box
on the form and, if it's been checked, appends its tag property to
tblNeedlingSessions along with the NeedlingID and any notes on the session.

This is working OK, at least in my prototype.

However, what's got me worried is that by doing it this way I'm not
actually using tblNeedlings for anything since, if a box is checked, my
code looks not at anything in tblNeedlings but at the tag property of
the individual control. I could now delete tblNeedlings and the
application would work perfectly well without it.

This feels all wrong.

What would you use for reports if you eliminated tblNeedlings?
Where would the needling name and description come from?

 

[Stephen Glynn]

What would you use for reports if you eliminated tblNeedlings?
Where would the needling name and description come from?

Well, yes. Obviously I wasn't planning to ditch tblNeedlings since, as
you say, I'll need it for the reports.

What's worrying me, though, is that I've got the same data stored in two
different places -- in tblNeedlings and also in the tag properties of
362 check-boxes on a data entry form and need it to be in both places to
get the application to work.

I know that the two are synchronised so it shouldn't be a problem but
this runs contrary to what I know of normalisation, so I'm worried I've
made a glaring error somewhere that I'll come to regret later on
(usually when I realise I haven't normalised properly is when, at an
advanced stage, something doesn't work and I have to take the whole
database to bits and put it back together again properly).

Steve

 

[rkc]

Well, yes. Obviously I wasn't planning to ditch tblNeedlings since, as
you say, I'll need it for the reports.

What's worrying me, though, is that I've got the same data stored in two
different places -- in tblNeedlings and also in the tag properties of
362 check-boxes on a data entry form and need it to be in both places to
get the application to work.

I know that the two are synchronised so it shouldn't be a problem but
this runs contrary to what I know of normalisation, so I'm worried I've
made a glaring error somewhere that I'll come to regret later on
(usually when I realise I haven't normalised properly is when, at an
advanced stage, something doesn't work and I have to take the whole
database to bits and put it back together again properly).

You don't have a normalization problem.
Your need to duplicate the needling names in the tag property is a function
of the non-standard GUI requested by the client. As people have pointed
out and I think you realise yourself your table structure would be the
same if you used the standard form/subform gui instead.