Date-range specific report with counted results of combo box selections

[Erin]

Hi there,
I'm a novice with Access, but am currently setting up a patient
database for an NGO in India that I'm volunteering with. The primary
reason for the database is to collect statistics quarterly. It is a
simple database with one table that collects patient data from simple
forms with some fields having combo boxes.

Ultimately I want to generate reports that will show a summary of
'counts' of various fields for a given period. For example:

In the table I have a combo box for where the person came from, with
several options including - "Pick up Y. Bazar", "Came Self" &
"Referred". I also have fields with tick boxes, e.g. HIV positive is
either ticked or not. On the report I would like it to look like the
following:

From 01/01/07 - 31/03/07

Came from: Pick up Y. Bazar (count)
Came Self (count)
Referred (count)

HIV positive: (count)

Firstly, what kind of queries to I need to make to do this, and how do
I make a report look like the above? I am familiar with how to make a
query defined by a date range and how to use criteria to limit to one
result from a combo box etc.

I have searched the group to try and work this out but have had no
luck, any pointers anyone could give would be appreciated - I'm
possibly getting a bit out of my depth here.

Cheers,
Erin.

 

[Larry Daugherty]

Hi Erin,

Welcome to the world of Access development. Early on in your
experiences in these newsgroups you'll probably find that each time
you posit an issue the responses seem to come back talking about
something else. Such will be the case here. It's all intended to
help you and those in like situations.

First, you've been sucked in by Microsoft regarding Lookup Fields in
table design. Get rid of them before you've tried to layer other
functionality on them. Remove them and let each table stand alone
rather than trying to store a relationship in the table. Don't
confuse Lookup Fields (a bad thing) with Lookup Tables, a very good
and useful thing. Visit www.mvps.org/access for edifying discussion.

Next, you've made a decision regarding your schema (just one
table....) that just doesn't make sense. The topics of analysis and
design aren't covered well in the Microsoft documentation so don't
restrict your attempts to learn about them to just the MS sources. A
lot of good information comes out in these newsgroups but TablesDesign
is particularly on target.

The more thoroughly you complete the early phases of an application,
the more successful and easy to maintain and to enhance that
application will be.. To simplify: there should be one table for each
entity type within your application. Every instance of a given entity
should be recorded in the single table provided within your
application for that entity type. One common mistake that people make
in designing their data is to create a new table for each instance of
what is really a data attribute. Don't create tables based on the
year, for example.. Just include a date field in your table and the
year is now something on which you can sort and differentiate
records..

Make ample use of Lookup Tables - those are simple tables that serve
to hold lists of data. For example you might have a table, tblDisease
in which every diagnosable disease within the scope of your
application is listed. You start with a huge list and then add
diseases as they are encountered for the first time. That will save
lots of typing with the chances for typographical errors. Just point
and click to select the diagnosed disease and get to what ever else
your schedule demands.

New developers who are serious about getting it "right" will do well
to lurk Microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted. Also, visit
www.mvps.org/access frequently You'll gain new insights by reading
there. Also, new stuff keeps appearing. Make intelligent efforts to
learn and the rewards will come. Access has a long, steep learning
curve. You aren't stupid. You're taking on the world of relational
database design and development and Access at the same time. It's a
load.


By the way, if your NGO or any of which you are aware are providing
free medical services to folks then you and they might want to decode
my email and get in touch with me. I have an Access based Medical
Transcriber productivity tool that I make available to free clinics at
no charge. People and organizations who charge the recipients for
their services don't get the freebies.

HTH

 

[John W. Vinson]

Hi there,
I'm a novice with Access, but am currently setting up a patient
database for an NGO in India that I'm volunteering with. The primary
reason for the database is to collect statistics quarterly. It is a
simple database with one table that collects patient data from simple
forms with some fields having combo boxes.

Ultimately I want to generate reports that will show a summary of
'counts' of various fields for a given period. For example:

In the table I have a combo box for where the person came from, with
several options including - "Pick up Y. Bazar", "Came Self" &
"Referred". I also have fields with tick boxes, e.g. HIV positive is
either ticked or not. On the report I would like it to look like the
following:


Came from: Pick up Y. Bazar (count)
Came Self (count)
Referred (count)

HIV positive: (count)

Firstly, what kind of queries to I need to make to do this, and how do
I make a report look like the above? I am familiar with how to make a
query defined by a date range and how to use criteria to limit to one
result from a combo box etc.

A Totals query will do this. Create a query based on your table; select the
"Came From" field and some field which will never be null - the table's
Primary Key is a good choice. Also select the date field (and any other field
that you want to use for criteria).

Change the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). A new row labeled Totals will appear in the query grid.

Leave the default Group By on the "Came From" field; change it to Count on the
primary key field; and change it to "Where" on the date field. Put

BETWEEN [Enter start date:] AND [Enter end date:]

on the Criteria line under the date field.

Base your report on this Query.

You may need a report with two or more subreports if you're displaying
unrelated sets of data (such as HIV positive) - I'm sure there will be overlap
between the "came from" patients and the HIV positive patients.

John W. Vinson [MVP]

 

[Erin]

Thanks to you both for your input, you have certainly opened my eyes
to the complexities of what I am doing!

Using look-up tables does seem like the better alternative to combo
boxes - should I only have one field in each look-up table with the
list of possibilities or should I have an autonumber associated with
each?

Now that I have looked into it, I am struggling with working out how
to change from having many fields in my original table (i.e. for each
diagnosis flag there is a yes/no field) to linking a table where
multiple diagnoses could be selected. I would want to have a list of
common diagnoses (the ones we want for statistical purposes) and an
"other" option that could also have a description. We may add a few
common diagnoses to the list in the future, but would not put every
new one in.

Larry - I have tried to send an email but it bounced back to me. As it
is (and I know this probably isn't the appropriate place to discuss
this), the NGO I'm with provides all medical services for free. Most
of the patients are picked up off the street and are often destitute.
I am interested in knowing more about your Access based tool, though I
am not sure what it is for? Would like to hear from you about it if
possible.

Cheers and many thanks again, Erin.

 

[John W. Vinson]

Thanks to you both for your input, you have certainly opened my eyes
to the complexities of what I am doing!

Using look-up tables does seem like the better alternative to combo
boxes - should I only have one field in each look-up table with the
list of possibilities or should I have an autonumber associated with
each?

NO. "Lookup fields" *ARE* combo boxes, not an alternative to combo boxes. See
http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup Field
misfeature.

If you're using table datasheets for data entry - *DON'T*. Tables are *not*
designed for this purpose. You can and should certainly use Combo Boxes
appropriately - on Forms, not in tables. If the value being looked up is
reasonably short and stable, then you don't need an Autonumber; it's perfectly
reasonable to have a one-field table with the list of valid values, and use
that combo as the rowsource of a combo box on a Form to store data into a Text
field in your table.

Now that I have looked into it, I am struggling with working out how
to change from having many fields in my original table (i.e. for each
diagnosis flag there is a yes/no field) to linking a table where
multiple diagnoses could be selected. I would want to have a list of
common diagnoses (the ones we want for statistical purposes) and an
"other" option that could also have a description. We may add a few
common diagnoses to the list in the future, but would not put every
new one in.

This is a very typical many to many relationship: each Patient can have one or
more diagnoses, and each diagnosis can apply to one or more patient. The
normalized way to handle this involves three tables:

Patients
PatientID <autonumber or other unique linking field>
Surname
Forename
<other biographical and identifying information as appropriate>

Diagnoses
DiagnosisCode <there are standard medical codes available, or use an
autonumber>
Diagnosis

PatientDiagnoses
PatientID <who has this diagnosis>
DiagnosisCode <what do they have>
<other fields about this patient's condition with regard to this diagnosis,
e.g. severity, date diagnosed, comments, ...>
<the Comments field could contain the "other diagnosis" if you choose to
have a DiagnosisCode for "Other">


Rather than forty *fields* for forty conditions, you would have as many
*records* as the patient needs. This makes it much easier to add or edit
conditions without changing your table structure, and you can very easily run
Totals queries joining the three tables to get statistical frequency.


OT but... your operation and your patients are in my prayers.

John W. Vinson [MVP]

 

[Erin]

Thanks again.

I have never thought to enter data directly into the data sheet, but
have rather set up forms for this (the people who will be entering
data are at the moment computer illiterate & I want it as simple as
possible!). I would love further clarification/advice on the following
if you could offer it:

it's perfectly
reasonable to have a one-field table with the list of valid values, and use
that combo as the rowsource of a combo box on a Form to store data into a Text
field in your table.

I'm confused about how to 'store data into a Text field in your
table' -- how do I get the resulting text from a combo box on a form
(that is linked to the one-field table) to end up as text in a table
and not a combo box?

Cheers yet again... Erin.

 

[John W. Vinson]

I'm confused about how to 'store data into a Text field in your
table' -- how do I get the resulting text from a combo box on a form
(that is linked to the one-field table) to end up as text in a table
and not a combo box?

Have a Text field in the table (STATE, say, in an address).

Have a Combo Box based on a query

SELECT [StateCode] FROM tblStates ORDER BY StateCode;

with values of StateCode like AK, AL, and so on.

On the Form set the Control Source of the combo to STATE.

If you're assuming that a combo box must store a numeric value... it needn't.
It will store whatever field is in its Bound Column into the Control Source
field that you specify in the combo's properties.

Also, it is NOT necessary to have a combo box in the Table in order to use a
combo box on a Form. That's one way to do it, sure, but you can very easily
use the toolbox combo box tool to put a combo on a form, independently of
whether it's a Lookup Field in a table or not.

John W. Vinson [MVP]