Report with multiple date parameters

[Guest]

I have structured a table to include several yes/no questions, followed by dates. (ie, [diagnosis1]Yes/No; [diagnosis1date]dd/mm/yy.
I have structured another (linked) table that includes client information (client number, phone numbers, client initials, etc.)

I need to create a simple report that will provide the following:
Between (startdate) And (enddate)
[SumOfdiagnosis1]
list of client identifier with [diagnosis1]
[SumOfdiagnosis1]
list of client identifier with [diagnosis2]

I need to create another report that will provide the following:
Between [startdate] And [enddate]
list of clients with either [diagnosis1] or [diagnosis2] and contact information

I have written a query that COUNTS [diagnosis1] and [diagnosis2]

I have created a report request form that includes [startdate] and [enddate]

HOWEVER, if I include criteria in my query that specifies Between . . . And for both [diagnosis1date] and [diagnosis2date], I receive an error message (date is entered in wrong format or is too complex).

I can make this work with only ONE diagnosis date, but not with TWO.

Help!

 

[John Vinson]

I have structured a table to include several yes/no questions, followed by dates. (ie, [diagnosis1]Yes/No; [diagnosis1date]dd/mm/yy.
I have structured another (linked) table that includes client information (client number, phone numbers, client initials, etc.)

Then you have an improperly normalized table. WHENEVER you have fields
with names like Diagnosis2 or Diagnosis3, you should reconsider your
table design! You're storing a one-to-many relationship in each
record.

A better design would be to have *two* tables - the second table would
have a Primary Key field, a ClientID field to link to the main table,
a Diagnosis field and a DiagnosisDate field.

The difficulties you're having would be all resolved by making this
structural change.

 

[John Vinson]

The client ID field is the patient number. This needs to be entered ONLY ONCE. If I set up the tables as you describe (I've done that), place an auto-numbered field in each new table as the primary key, include a (differently named) field for the patient number, and link that to the main table -- one-to-one relationship -- it now asks me to provide the client ID before opening the entry form OR any reports.

What's wrong?

I suspect that you're trying to open the "entry form" in a different
manner than I'd expected. Typically one would have a main Form for the
patient info and Subforms for the related tables, using PatientID as
the master/child link field.

The relationship should probably be one to many. Could you describe
the two tables and how you're trying to link them? Does the PatientID
in the new diagnoses table have a unique Index? (It shouldn't).

 

[John Vinson]

I've set up 4 tables now: the patient data (patient ID as primary key, patient initials and contact information); the diagnosis data (in two separate tables, with non-matching field names for diagnosisID(primary keys), patient ID(linked to patient data table), diagnosis yes/no, and date of diagnosis; and the contact history table (which includes the patient ID linked to the patient data, a contactID as the primary key, and the contact history -- calls, dates).

What do you mean by "non-matching field names"? Is the new table
structure working for you?

 

[John Vinson]

The new table structure is not working for me. I've been spending time re-connecting everything (thank goodness there's no data in this database yet!).

Ok... let's see if we can't clarify things a bit.

Here's where I am today:
Table 1: [PatientID] contains three fields: [PatientID#](primary key), [firstinitial] and [lastinitial]

I take it PatientID# is an Autonumber, and that you don't keep patient
names? Do you just refer to patients as SB or JV? If you have eight
people with the same initials how do you distinguish them? I'd
recommend that you NOT use # in fieldnames - it's a date delimiter and
Access can get confused; I'd just call this field PatientID.

Table 2: [Contactinfo] contains [phone][mobilephone][lastappointmentdate][lastupdate](an automatic update field that indicates when the record was last updated) and [PatientID#] (lookup to [PatientID])[/QUOTE]

Suggestion:

*GET RID OF ALL LOOKUPS*. You *don't need them*. See
http://www.mvps.org/access/lookupfields.htm for a critique. You can
use a lookup *table*, using a Form with tools such as subforms and
combo boxes to display data from other tables - but all the Lookup
Field in your table accomplishes is to conceal the actual contents of
your table. Keep the PatientID field but in table datasheet view it
should *just show a number*.
[QUOTE]
Table 3: [Contacthistory] contains [Patient id#] [contactID] (primary key) [called](yes/no field), [called date], [2nd call] (y/n), [2ndcalldate], [contactmade] (y/n) . . . and several more fields like this that let us know whether and when the patient was contacted.[/QUOTE]

I'd suggest instead having a ContactID (autonumber primary key),
PatientID Long Integer as a link to Patients; a CalledDate; a
CallNumber (1 for first call, 2 for second, etc. - this can be
automated); and a contactmade field. If you call a patient four times
this table *would contain four records*. "Fields are expensive,
records are cheap" - rather than having two calls in one record, have
each call stored in *its own record*.
[QUOTE]
Table 4: [diagnosis1] contains [patient#], [diag1#] (primary key), [diag1] (yes/no field), and [diag1date]

Table 5: [diagnosis2] contains [client#], diag2#] (primary key), [diag2] (yes/no) and [diag2date][/QUOTE]

And here you've missed the track completely. You have a *MANY TO MANY*
relationship between patients and diagnoses. One patient can have one,
two, three, or (poor soul!) a dozen different diagnoses; each
condition may be suffered by multiple patients. Whenever you have a
many to many relationship you need *three* tables.

You need a table of Diagnoses with fields DiagNo (Primary Key); this
might be an autonumber but perhaps better would be an
industry-standard diagnosis code (such as ICD 9 codes), if you have a
source of these. Let's call these fields DiagCode and Diagnosis (such
as "Hypertension" or "Shingles" or whatever).

The Patients table is the other side of the many to many.

The third table would be PatientDiagnosis. It would have (at least)
two fields: PatientID (link to Patients) and DiagCode (link to
Diagnoses). If a patient has three conditions this table would have
three records. The table would also have fields for DiagDate, and
perhaps a comment field for medical notes, severity, etc. The yes/no
field is not needed - if a patient has Shingles, there's a record in
this table linked to that patient and to the code for Shingles; if the
patient doesn't have that condition, you simply don't insert that
record.

[QUOTE]
The relationships are one-to-one, the joins are "Include all records from [PatientID] and only those from [tablename] where fields are equal.[/QUOTE]

One to one relationships are VERY RARE. If you're not familiar with
the term Subclassing, then you probably are making a mistake using one
to ones.
[QUOTE]
At this point, nothing works.[/QUOTE]

We'll get there! Keep plugging... and do find some references to
"normalization" and database design. A good place to start is the
Microsoft knowlege base article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;283878