This probably isn't possible

[DandimLee]

Is there a way to have a report where the count of certain types of data is
displayed for each week in the previous month. This is for a medical
database, and I need to count the number of patients that had a particular
diagnosis three weeks ago, two weeks ago, and now. The problem I was having
with this is that there is a discharge checkmark and a discharge date. When
I tried to count the previous number of patients, it only returned those
patients that were not yet discharged (I had that filtered). And when I
don't filter according to discharges, it includes the discharged patients
from months ago. And when I tried to filter according to the discharge date,
the patients who are still inpatient are not included. Thanks for any help.

 

[Wayne-I-M]

Hi

To get last months data use DateAdd("m", -1, Date()) - I don't understand
your DB but could you not just disregard the discharge data and use a totals
query on the diagnosis types ??

 

[BruceM]

I expect that what you are describing is indeed possible, but it is a little
difficult to figure out just what you need to do. You can use a report's
Sorting and Grouping (View > Sorting and Grouping) to create a group header
or footer for a date field. In the Group On part of the dialog, you can
choose whether to group by day, week, year, etc.
If you want a sum by group, one way is to put a text box (txtRS) into the
report's detail section. Set its control source to =1, and its Running Sum
property to Over Group. Make it invisible. In the footer, add an unbound
text box with its control source set to =txtRS.
If you base the report on a query you should be able to limit the recordset
to just the records you need. For instance, you can limit the records to
just discharged patients from the past month (or whatever).
If this sounds like it's heading in the right direction, clarify your
requirements. We should be able to get this sorted out.

 

[DandimLee]

I'm sorry for being unclear. When I try to filter for the specific dates
that I need, like people who have been admitted within the last ten days with
the diagnosis, it's fine. I just restricted the date of admittance to the
last week, and the diagnosis to the selected diagnosis. The problem with
that is that it includes the patients discharged in that week, and it
excludes the people who were admitted prior to the cutoff date and have not
been discharged. If I exclude the people who were discharged, then they
won't show up even though they were discharged after the cutoff date. And if
I try to put a filter on the discharge date, the patients who haven't been
discharged are not included.
I think I need a way to filter by the discharge date where it not only
includes the people that have a discharge date after my selected date, but
also those that have nothing in that particular list box.

 

[BruceM]

Are you using a query? If so, post its SQL. To do that, click View > SQL
View. Copy what you see there, and paste it into a message.
You still need to clarify. First you say that the problem with a date range
and a diagnosis is that it includes people who have already been discharged,
and it excludes people who were admitted before the beginning of the date
range and who have not been discharged. Then you say that if you exclude
people who were discharged, they won't show up even if they were discharged
after the cutoff date. In the first place you say it is a problem that
discharged people show up on the list, then in the second place you say it
is a problem if they do not show up on the list.
Is the cutoff date the date they were admitted, the date they were
discharged, or something else? In what cases do you wish to include
discharged people on the list? What is "that particular list box".

 

[John W. Vinson]

I think I need a way to filter by the discharge date where it not only
includes the people that have a discharge date after my selected date, but
also those that have nothing in that particular list box.

Well, data isn't stored in listboxes so that's a tough one!

What is the structure of your table? What are the relevant fields? Could you
post the SQL view of the query you're using now?

John W. Vinson [MVP]

 

[DandimLee]

SELECT Master.[Date of Board], Master.PEB, DateDiff("d",[Date of
Board],Now()-21) AS Days
FROM Master
GROUP BY Master.[Date of Board], Master.PEB, Master.[SENT TO PEB],
Master.TERMINATED, Master.[No Longer Tracking], Master.BMIA, Master.Transfer,
Master.Other, Master.TBTR, Master.ADDENDUM, Master.SUS, Master.[DATE SENT TO
PEB]
HAVING (((Master.[Date of Board])<Now()-21) AND ((Master.PEB)=Yes) AND
((Master.[SENT TO PEB])=No) AND ((Master.TERMINATED)=No) AND ((Master.[No
Longer Tracking])=No) AND ((Master.BMIA)=No) AND ((Master.Transfer)=No) AND
((Master.Other)=No) AND ((Master.TBTR)=No) AND ((Master.ADDENDUM)=No) AND
((Master.SUS)=No));

I want to filter Master.[DATE SENT TO PEB] for both a date like I did in
Master.[Date of Board] and to include those entries which don't have a DATE
SENT TO PEB. After I get this query to display all of the Master.[PEB] cases
which meet the criteria of having been generated prior to Now()-21
(Master.[Date of Board], and have not been discharged prior to
Now()-21(Master.[DATE SENT TO PEB], including those that haven't been
discharged Now(). The above SQL statement displays only those that haven't
been discharged yet, vice having those that haven't been discharged and those
that weren't discharged prior to the date 3 weeks ago.

 

[BruceM]

If you want to include records with no entry in [DATE SENT TO PEB], in query
design view, in the OR row, add "Is Null" (without the quotes). For the
rest of the fields (Master.PEB, etc.), duplicate in the Or row the
information from the Criteria row as needed.
A difficulty I am having understanding what is going on here is that you
want to filter according to whether the patient has been discharged, but I
don't know which field shows that information.
For the rest, you may be over-complicating it. From what I can tell, you
need to show all patients who have not been discharged. If that information
is a Yes/No field, you simply do not provide any criteria for that field.

 

[DandimLee]

If I put "is Null" in the criteria, can I also put in a < or > criteria for
the date? This is so that the resulting count includes the patients who were
discharged (Master.[Sent to PEB]) after the date I specify in [DATE SENT TO
PEB]. I'm trying to count the number of active patients within a certain
time frame (3 weeks ago, 2 weeks ago, etc.). Some of those patients are
going to be active through the entire month, while some patients will be
admitted in the middle of month, or be discharged within the month, which is
why I need to have the [DATE SENT TO PEB] be null or be greater than the
selected date.

If you want to include records with no entry in [DATE SENT TO PEB], in query
design view, in the OR row, add "Is Null" (without the quotes). For the
rest of the fields (Master.PEB, etc.), duplicate in the Or row the
information from the Criteria row as needed.
A difficulty I am having understanding what is going on here is that you
want to filter according to whether the patient has been discharged, but I
don't know which field shows that information.
For the rest, you may be over-complicating it. From what I can tell, you
need to show all patients who have not been discharged. If that information
is a Yes/No field, you simply do not provide any criteria for that field.

SELECT Master.[Date of Board], Master.PEB, DateDiff("d",[Date of
Board],Now()-21) AS Days
FROM Master
GROUP BY Master.[Date of Board], Master.PEB, Master.[SENT TO PEB],
Master.TERMINATED, Master.[No Longer Tracking], Master.BMIA,
Master.Transfer,
Master.Other, Master.TBTR, Master.ADDENDUM, Master.SUS, Master.[DATE SENT
TO
PEB]
HAVING (((Master.[Date of Board])<Now()-21) AND ((Master.PEB)=Yes) AND
((Master.[SENT TO PEB])=No) AND ((Master.TERMINATED)=No) AND ((Master.[No
Longer Tracking])=No) AND ((Master.BMIA)=No) AND ((Master.Transfer)=No)
AND
((Master.Other)=No) AND ((Master.TBTR)=No) AND ((Master.ADDENDUM)=No) AND
((Master.SUS)=No));

I want to filter Master.[DATE SENT TO PEB] for both a date like I did in
Master.[Date of Board] and to include those entries which don't have a
DATE
SENT TO PEB. After I get this query to display all of the Master.[PEB]
cases
which meet the criteria of having been generated prior to Now()-21
(Master.[Date of Board], and have not been discharged prior to
Now()-21(Master.[DATE SENT TO PEB], including those that haven't been
discharged Now(). The above SQL statement displays only those that
haven't
been discharged yet, vice having those that haven't been discharged and
those
that weren't discharged prior to the date 3 weeks ago.

 

[BruceM]

If you are trying to find patients who were discharged after a date you
specify for [DATE SENT TO PEB], open the query in design view. In the
Criteria row for that field, put:

[Enter a date]

If another field has a criteria, add that. For instance, let's say the
criteria for [DateOfBoard] is:
<DateAdd("d",-15,Date)
If you also want to see records for which [DATE SENT TO PEB] is null, in the
Or row in query design view, directly below the Criteria row, put:
Is Null
If you want the same criteria for DateOfBoard, add it again to the Or row
for that field, directly below where you entered the DateAdd expression in
the criteria row.
The logic here is:
Find all records in which [DATE SENT TO PEB] is after a specified date and
[DateOfBoard] is earlier than fifteen days ago, or in which [DATE SENT TO
PEB] is null and [DateOfBoard] is earlier than fifteen days ago.
You can shorten this process by entering in the criteria row for [DATE SENT
TO PEB]:

[Enter a date] Or Is Null

In [DateOfBoard], use the DateAdd expression as stated above.
I used [DateOfBoard] just to illustrate having another field with a
criteria. There is no requirement to add any other criteria, or you can add
criteria for several fields.
BTW, when asking a question you would do well to remember that we cannot see
your database, nor do we know your jargon. If you are asking about the
discharge date field, it would help to call it [DischargeDate] rather than
leaving it for others to sort out that [DATE SENT TO PEB] is the discharge
date. Sorting that out took time away from trying to address the actual
question.
On another point, there does not seem to be a need for [SENT TO PEB]. If
there is no date, the patient was not discharged.

If I put "is Null" in the criteria, can I also put in a < or > criteria
for
the date? This is so that the resulting count includes the patients who
were
discharged (Master.[Sent to PEB]) after the date I specify in [DATE SENT
TO
PEB]. I'm trying to count the number of active patients within a certain
time frame (3 weeks ago, 2 weeks ago, etc.). Some of those patients are
going to be active through the entire month, while some patients will be
admitted in the middle of month, or be discharged within the month, which
is
why I need to have the [DATE SENT TO PEB] be null or be greater than the
selected date.

If you want to include records with no entry in [DATE SENT TO PEB], in
query
design view, in the OR row, add "Is Null" (without the quotes). For the
rest of the fields (Master.PEB, etc.), duplicate in the Or row the
information from the Criteria row as needed.
A difficulty I am having understanding what is going on here is that you
want to filter according to whether the patient has been discharged, but
I
don't know which field shows that information.
For the rest, you may be over-complicating it. From what I can tell, you
need to show all patients who have not been discharged. If that
information
is a Yes/No field, you simply do not provide any criteria for that field.

SELECT Master.[Date of Board], Master.PEB, DateDiff("d",[Date of
Board],Now()-21) AS Days
FROM Master
GROUP BY Master.[Date of Board], Master.PEB, Master.[SENT TO PEB],
Master.TERMINATED, Master.[No Longer Tracking], Master.BMIA,
Master.Transfer,
Master.Other, Master.TBTR, Master.ADDENDUM, Master.SUS, Master.[DATE
SENT
TO
PEB]
HAVING (((Master.[Date of Board])<Now()-21) AND ((Master.PEB)=Yes) AND
((Master.[SENT TO PEB])=No) AND ((Master.TERMINATED)=No) AND
((Master.[No
Longer Tracking])=No) AND ((Master.BMIA)=No) AND ((Master.Transfer)=No)
AND
((Master.Other)=No) AND ((Master.TBTR)=No) AND ((Master.ADDENDUM)=No)
AND
((Master.SUS)=No));

I want to filter Master.[DATE SENT TO PEB] for both a date like I did
in
Master.[Date of Board] and to include those entries which don't have a
DATE
SENT TO PEB. After I get this query to display all of the Master.[PEB]
cases
which meet the criteria of having been generated prior to Now()-21
(Master.[Date of Board], and have not been discharged prior to
Now()-21(Master.[DATE SENT TO PEB], including those that haven't been
discharged Now(). The above SQL statement displays only those that
haven't
been discharged yet, vice having those that haven't been discharged and
those
that weren't discharged prior to the date 3 weeks ago.

 

[DandimLee]

Thank you for your help. It's working like I want it to now.

If you are trying to find patients who were discharged after a date you
specify for [DATE SENT TO PEB], open the query in design view. In the
Criteria row for that field, put:

[Enter a date]

If another field has a criteria, add that. For instance, let's say the
criteria for [DateOfBoard] is:
<DateAdd("d",-15,Date)
If you also want to see records for which [DATE SENT TO PEB] is null, in the
Or row in query design view, directly below the Criteria row, put:
Is Null
If you want the same criteria for DateOfBoard, add it again to the Or row
for that field, directly below where you entered the DateAdd expression in
the criteria row.
The logic here is:
Find all records in which [DATE SENT TO PEB] is after a specified date and
[DateOfBoard] is earlier than fifteen days ago, or in which [DATE SENT TO
PEB] is null and [DateOfBoard] is earlier than fifteen days ago.
You can shorten this process by entering in the criteria row for [DATE SENT
TO PEB]:

[Enter a date] Or Is Null

In [DateOfBoard], use the DateAdd expression as stated above.
I used [DateOfBoard] just to illustrate having another field with a
criteria. There is no requirement to add any other criteria, or you can add
criteria for several fields.
BTW, when asking a question you would do well to remember that we cannot see
your database, nor do we know your jargon. If you are asking about the
discharge date field, it would help to call it [DischargeDate] rather than
leaving it for others to sort out that [DATE SENT TO PEB] is the discharge
date. Sorting that out took time away from trying to address the actual
question.
On another point, there does not seem to be a need for [SENT TO PEB]. If
there is no date, the patient was not discharged.

If I put "is Null" in the criteria, can I also put in a < or > criteria
for
the date? This is so that the resulting count includes the patients who
were
discharged (Master.[Sent to PEB]) after the date I specify in [DATE SENT
TO
PEB]. I'm trying to count the number of active patients within a certain
time frame (3 weeks ago, 2 weeks ago, etc.). Some of those patients are
going to be active through the entire month, while some patients will be
admitted in the middle of month, or be discharged within the month, which
is
why I need to have the [DATE SENT TO PEB] be null or be greater than the
selected date.

If you want to include records with no entry in [DATE SENT TO PEB], in
query
design view, in the OR row, add "Is Null" (without the quotes). For the
rest of the fields (Master.PEB, etc.), duplicate in the Or row the
information from the Criteria row as needed.
A difficulty I am having understanding what is going on here is that you
want to filter according to whether the patient has been discharged, but
I
don't know which field shows that information.
For the rest, you may be over-complicating it. From what I can tell, you
need to show all patients who have not been discharged. If that
information
is a Yes/No field, you simply do not provide any criteria for that field.

SELECT Master.[Date of Board], Master.PEB, DateDiff("d",[Date of
Board],Now()-21) AS Days
FROM Master
GROUP BY Master.[Date of Board], Master.PEB, Master.[SENT TO PEB],
Master.TERMINATED, Master.[No Longer Tracking], Master.BMIA,
Master.Transfer,
Master.Other, Master.TBTR, Master.ADDENDUM, Master.SUS, Master.[DATE
SENT
TO
PEB]
HAVING (((Master.[Date of Board])<Now()-21) AND ((Master.PEB)=Yes) AND
((Master.[SENT TO PEB])=No) AND ((Master.TERMINATED)=No) AND
((Master.[No
Longer Tracking])=No) AND ((Master.BMIA)=No) AND ((Master.Transfer)=No)
AND
((Master.Other)=No) AND ((Master.TBTR)=No) AND ((Master.ADDENDUM)=No)
AND
((Master.SUS)=No));

I want to filter Master.[DATE SENT TO PEB] for both a date like I did
in
Master.[Date of Board] and to include those entries which don't have a
DATE
SENT TO PEB. After I get this query to display all of the Master.[PEB]
cases
which meet the criteria of having been generated prior to Now()-21
(Master.[Date of Board], and have not been discharged prior to
Now()-21(Master.[DATE SENT TO PEB], including those that haven't been
discharged Now(). The above SQL statement displays only those that
haven't
been discharged yet, vice having those that haven't been discharged and
those
that weren't discharged prior to the date 3 weeks ago.

 

[BruceM]

Glad to hear it. Good luck with the rest of the project.

Thank you for your help. It's working like I want it to now.

If you are trying to find patients who were discharged after a date you
specify for [DATE SENT TO PEB], open the query in design view. In the
Criteria row for that field, put:

[Enter a date]

If another field has a criteria, add that. For instance, let's say the
criteria for [DateOfBoard] is:
<DateAdd("d",-15,Date)
If you also want to see records for which [DATE SENT TO PEB] is null, in
the
Or row in query design view, directly below the Criteria row, put:
Is Null
If you want the same criteria for DateOfBoard, add it again to the Or row
for that field, directly below where you entered the DateAdd expression
in
the criteria row.
The logic here is:
Find all records in which [DATE SENT TO PEB] is after a specified date
and
[DateOfBoard] is earlier than fifteen days ago, or in which [DATE SENT TO
PEB] is null and [DateOfBoard] is earlier than fifteen days ago.
You can shorten this process by entering in the criteria row for [DATE
SENT
TO PEB]:

[Enter a date] Or Is Null

In [DateOfBoard], use the DateAdd expression as stated above.
I used [DateOfBoard] just to illustrate having another field with a
criteria. There is no requirement to add any other criteria, or you can
add
criteria for several fields.
BTW, when asking a question you would do well to remember that we cannot
see
your database, nor do we know your jargon. If you are asking about the
discharge date field, it would help to call it [DischargeDate] rather
than
leaving it for others to sort out that [DATE SENT TO PEB] is the
discharge
date. Sorting that out took time away from trying to address the actual
question.
On another point, there does not seem to be a need for [SENT TO PEB]. If
there is no date, the patient was not discharged.

If I put "is Null" in the criteria, can I also put in a < or > criteria
for
the date? This is so that the resulting count includes the patients
who
were
discharged (Master.[Sent to PEB]) after the date I specify in [DATE
SENT
TO
PEB]. I'm trying to count the number of active patients within a
certain
time frame (3 weeks ago, 2 weeks ago, etc.). Some of those patients
are
going to be active through the entire month, while some patients will
be
admitted in the middle of month, or be discharged within the month,
which
is
why I need to have the [DATE SENT TO PEB] be null or be greater than
the
selected date.

:

If you want to include records with no entry in [DATE SENT TO PEB], in
query
design view, in the OR row, add "Is Null" (without the quotes). For
the
rest of the fields (Master.PEB, etc.), duplicate in the Or row the
information from the Criteria row as needed.
A difficulty I am having understanding what is going on here is that
you
want to filter according to whether the patient has been discharged,
but
I
don't know which field shows that information.
For the rest, you may be over-complicating it. From what I can tell,
you
need to show all patients who have not been discharged. If that
information
is a Yes/No field, you simply do not provide any criteria for that
field.

SELECT Master.[Date of Board], Master.PEB, DateDiff("d",[Date of
Board],Now()-21) AS Days
FROM Master
GROUP BY Master.[Date of Board], Master.PEB, Master.[SENT TO PEB],
Master.TERMINATED, Master.[No Longer Tracking], Master.BMIA,
Master.Transfer,
Master.Other, Master.TBTR, Master.ADDENDUM, Master.SUS, Master.[DATE
SENT
TO
PEB]
HAVING (((Master.[Date of Board])<Now()-21) AND ((Master.PEB)=Yes)
AND
((Master.[SENT TO PEB])=No) AND ((Master.TERMINATED)=No) AND
((Master.[No
Longer Tracking])=No) AND ((Master.BMIA)=No) AND
((Master.Transfer)=No)
AND
((Master.Other)=No) AND ((Master.TBTR)=No) AND
((Master.ADDENDUM)=No)
AND
((Master.SUS)=No));

I want to filter Master.[DATE SENT TO PEB] for both a date like I
did
in
Master.[Date of Board] and to include those entries which don't have
a
DATE
SENT TO PEB. After I get this query to display all of the
Master.[PEB]
cases
which meet the criteria of having been generated prior to Now()-21
(Master.[Date of Board], and have not been discharged prior to
Now()-21(Master.[DATE SENT TO PEB], including those that haven't
been
discharged Now(). The above SQL statement displays only those that
haven't
been discharged yet, vice having those that haven't been discharged
and
those
that weren't discharged prior to the date 3 weeks ago.