Need to Count Active Records

[shep]

I have reviewed responses to several similar postings, particularly thoase by
Ofer (7/28/05), Gerald Stanley (7/15/05), and Ken Snell (6/12/05). However,
I have not been able to solve my need.

I have a table tblStatusHistoryV1 that stores patients change in status by
date. They are initially "Active" and may subsequently change to "Inactive",
back to "Active", or "Discharged".

Here is SQL for qryActivePatientsV2
SELECT tblStatusV1.Status, Count(tblStatusHistoryV1.ChartNumber) AS
CountOfChartNumber
FROM tblStatusV1, tblStatusHistoryV1 INNER JOIN qryPatientNameV1 ON
tblStatusHistoryV1.ID = qryPatientNameV1.ID
GROUP BY tblStatusV1.Status
HAVING (((tblStatusV1.Status)="Active"));

But this counts all records in tblStatusHistoryV1 that ever had a Status of
"Active". I need to count those records that have their latest status date =
"Active".

Thanks for any help

 

[tina]

i'm not good at writing SQL statements "freehand", so see if you can follow
these directions in the query's design grid:

Group by patient ID field, then Max the Date field, then Group by the
Status, and put a criteria on the grouped status of "Active". the aim here
is to pull the most recent status date and status for each patient, then
filter those results to show only the patients where the status is active.

btw, in the SQL statement you posted, tblStatusV1 doesn't appear to be
linked to tblStatusHistoryV1 (or linked to anything else), so check that out
as well.

hth

 

[shep]

Thanks Tina
I do not have a "Patient ID" field as such. tblStatusHistoryV1 has an ID
field that is the primary key, an autonumber assigned by ACCESS, as does
tblPatientV1 which is table qryPatientNameV1 is based on. The patient in
both tables is identified by both "ChartNumber" and "Patient".

here is new SQL:

SELECT tblStatusHistoryV1.ID, Max(tblStatusHistoryV1.StatusDate) AS
MaxOfStatusDate, tblStatusV1.Status, Count(tblStatusHistoryV1.ChartNumber) AS
CountOfChartNumber
FROM (tblStatusHistoryV1 INNER JOIN qryPatientNameV1 ON
tblStatusHistoryV1.ID = qryPatientNameV1.ID) INNER JOIN tblStatusV1 ON
tblStatusHistoryV1.Status = tblStatusV1.ID
GROUP BY tblStatusHistoryV1.ID, tblStatusV1.Status
HAVING (((tblStatusV1.Status)="Active"));

This pulls the first record that has "Active" status and that record only,
but that is not the latest status for that patient.

I have not entered the correct expression to Max the StatusDate field. I
don't know if that is only problem, but if you would help me get that part
right, we will tighten the noose.

Thanks

 

[tina]

well, if you have a table listing all your patients, then that table has a
primary key. that primary key functions as the patient ID *in the database*,
regardless of whether you call the primary key field ID, PatientID, or
anything else.

from your explanation, you're having the same type of linking problem you
had some weeks back, in not understanding the role of primary key/foreign
key relationships between tables. if tblStatusHistoryV1.ID is the autonumber
primary key field of tblStatusHistoryV1, and tblPatientV1.ID is the
autonumber primary key field of tblPatientV1, then there is no relationship
between the two fields and you *cannot* use them to link the two tables.

if tblStatusHistoryV1 tracks the status of patients in tblPatientV1, then
the primary key field of tblPatientV1 must be included in tblStatusHistoryV1
as a foreign key field. the names of the two fields do not have to be the
same, but the data type does (when the data type of the primary key field in
the parent table is Autonumber, then the data type of the *foreign* key
field in the child table must be LongInteger).

in your query, you need to link qryPatientNameV1.ID to the corresponding
*foreign* key field in tblStatusHistoryV1.

I have not entered the correct expression to Max the StatusDate field.

i don't understand what you mean there. if you're working in the QBE grid,
not the SQL pane, then when you add a field to the grid in a Totals query,
it automatically defaults to Group By. for the date field, change the Group
By to Max, from the droplist. at this point, i'd say no further
"expression-writing" will be needed.

shep, i really recommend that you read up on tables/relationships. you need
to get a solid understanding of the principles, or you're just going to
continue beating your head against a wall every time you need to link your
tables in a query, or form/subform, or report/subreport. take a look at the
links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Database Design 101 link.

hth

 

[shep]

I appreciate yor advise and will work to improve.

I have resolved the issue. I created three new queries and will post here ;
it may help someone else.

qryChartNumberLastStatusDateV2 lists the latestest status date for each
ChartNumber (Patient)
SELECT tblPatientV1.ChartNumber, Max(tblStatusHistoryV1.StatusDate) AS
LastStatusDate
FROM tblPatientV1 INNER JOIN tblStatusHistoryV1 ON tblPatientV1.ChartNumber
= tblStatusHistoryV1.ChartNumber
WHERE (((tblStatusHistoryV1.StatusDate)<=[forms]![frmReportDateV1]![Start
Date]))
GROUP BY tblPatientV1.ChartNumber;

qryChartNumberLastStatusIDV1 lists latest status ID for each ChartNumber
SELECT qryChartNumberLastStatusDateV2.ChartNumber,
qryChartNumberLastStatusDateV2.LastStatusDate, Max(tblStatusHistoryV1.ID) AS
LastStatusID
FROM tblStatusHistoryV1 INNER JOIN qryChartNumberLastStatusDateV2 ON
tblStatusHistoryV1.ChartNumber = qryChartNumberLastStatusDateV2.ChartNumber
GROUP BY qryChartNumberLastStatusDateV2.ChartNumber,
qryChartNumberLastStatusDateV2.LastStatusDate;

QryActivePatientV4 shows the count of active patients as of a selected date.
SELECT tblStatusV1.Status, Count(tblStatusHistoryV1.ChartNumber) AS
CountOfChartNumber
FROM ((tblStatusHistoryV1 INNER JOIN tblStatusV1 ON
tblStatusHistoryV1.Status = tblStatusV1.ID) INNER JOIN
qryChartNumberLastStatusIDV2 ON tblStatusHistoryV1.ID =
qryChartNumberLastStatusIDV2.LastStatusID) INNER JOIN qryPatientNameV1 ON
qryChartNumberLastStatusIDV2.ChartNumber = qryPatientNameV1.ChartNumber
GROUP BY tblStatusV1.Status
HAVING (((tblStatusV1.Status)="Active"));


Thanks again for your help, and I will study more about tables/relationships.