Group on Month with multiple dates for same record

[Guest]

I have been struggling with my problem for a while now, and feel that the
answer may be quite simple; except that I can't figure out what it is. I am
running a report for Admissions vs. Discharges for my facility. The time
frame can span over one month or over several months, in which case, one
resident may have discharged from the facility and returned (admitted) again
at a later date. If I run the report based on month-to-month, all of the
information appears to be accurate. It is when I run multiple months that at
least one of the residents are dropped if they have more than one
admit/discharge/admit in the specified report date range.

My two tables are TblResidents and another one for TblAdmissionsDischarges.

How can I have the report recognize all of the dates and show the resident
for each month that is relevant for the report date range?

Secondly, I need to figure out how I can summarize the total number of
admissions and total number of discharges for each month. I had a
Count([Admissions]) field added to the group footer, but it doesn't count
only the admissions, it counts all names listed for that particular month --
discharges and all. Any help is greatly appreciated.

 

[Duane Hookom]

You have asked many questions. You haven't told us anything about your
tables. We don't even know if you have any date fields.

 

[Guest]

Obviously, I would have date fields -- I have two of them (admisison date and
discharge date). My primary key is ResidentID in TblResidents and is linked
to TblAdmissionsDischarges, where there can be one ResidentID from
TblResidents to many records in the TblAdmissionsDischarges.

In the report, I have grouped all of the months together by using the
DateSerial expression where all the dates are converted to the 1st day of the
1st year, with any one of the 12 months.

I hope this helps to answer some of your questions, again, any help that you
can give is greatly appreciated! Thank you.

You have asked many questions. You haven't told us anything about your
tables. We don't even know if you have any date fields.

--
Duane Hookom
MS Access MVP

I have been struggling with my problem for a while now, and feel that the
answer may be quite simple; except that I can't figure out what it is. I
am
running a report for Admissions vs. Discharges for my facility. The time
frame can span over one month or over several months, in which case, one
resident may have discharged from the facility and returned (admitted)
again
at a later date. If I run the report based on month-to-month, all of the
information appears to be accurate. It is when I run multiple months that
at
least one of the residents are dropped if they have more than one
admit/discharge/admit in the specified report date range.

My two tables are TblResidents and another one for
TblAdmissionsDischarges.

How can I have the report recognize all of the dates and show the resident
for each month that is relevant for the report date range?

Secondly, I need to figure out how I can summarize the total number of
admissions and total number of discharges for each month. I had a
Count([Admissions]) field added to the group footer, but it doesn't count
only the admissions, it counts all names listed for that particular
month --
discharges and all. Any help is greatly appreciated.

 

[Duane Hookom]

I don't understand what you mean by:
How can I have the report recognize all of the dates and show the resident
for each month that is relevant for the report date range?

Counting the number of residents discharged during July of 2006 would be:

=Sum(Abs( Format([DischargeDate],"yyyymm") ="200607"))

--
Duane Hookom
MS Access MVP

Obviously, I would have date fields -- I have two of them (admisison date
and
discharge date). My primary key is ResidentID in TblResidents and is
linked
to TblAdmissionsDischarges, where there can be one ResidentID from
TblResidents to many records in the TblAdmissionsDischarges.

In the report, I have grouped all of the months together by using the
DateSerial expression where all the dates are converted to the 1st day of
the
1st year, with any one of the 12 months.

I hope this helps to answer some of your questions, again, any help that
you
can give is greatly appreciated! Thank you.

You have asked many questions. You haven't told us anything about your
tables. We don't even know if you have any date fields.

--
Duane Hookom
MS Access MVP

I have been struggling with my problem for a while now, and feel that
the
answer may be quite simple; except that I can't figure out what it is.
I
am
running a report for Admissions vs. Discharges for my facility. The
time
frame can span over one month or over several months, in which case,
one
resident may have discharged from the facility and returned (admitted)
again
at a later date. If I run the report based on month-to-month, all of
the
information appears to be accurate. It is when I run multiple months
that
at
least one of the residents are dropped if they have more than one
admit/discharge/admit in the specified report date range.

My two tables are TblResidents and another one for
TblAdmissionsDischarges.

How can I have the report recognize all of the dates and show the
resident
for each month that is relevant for the report date range?

Secondly, I need to figure out how I can summarize the total number of
admissions and total number of discharges for each month. I had a
Count([Admissions]) field added to the group footer, but it doesn't
count
only the admissions, it counts all names listed for that particular
month --
discharges and all. Any help is greatly appreciated.

 

[Guest]

Ok. Let me see if I can try to answer some questions that haven't been asked
yet.

Some of the fields in the report that are based on the two tables
(TblAdmissionsDischarges and TblResidents) include:
ResidentLastName
ResidentFirstName
AdmitDate
DCDate

I am grouping all of my admissions in the report query as: AdmitMonthGroup:
DateSerial(1,Month([AdmitDate]),1)

I am grouping all of my discharges in the report query as: DCMonthGroup:
IIf([DCDate] Is Null,"",(DateSerial(1,Month([DCDate]),1)))

Then, I am attempting to group all of these two in to one month group in
the report query as: MonthGroup: IIf([AdmitDate]>=Forms![Report Date
Range]![Beginning Rpt
Date],DateSerial(1,Month([AdmitDate]),1),(DateSerial(1,Month([DCDate]),1)))

Then, if all of these things work, I am trying to show a "1" in the new
field [Admissions] that I added to the report query as Admissions:
IIf([AdmitDate]>=Forms![Report Date Range]![Beginning Rpt Date],1,""). A new
field for Discharges in the report query would be the same way: Discharges:
IIf(DatePart("m",[DCDate])>DatePart("m",[MonthGroup]),"",IIf([DCDate] Is
Null,"",IIf(DatePart("m",[DCDate])<=DatePart("m",[MonthGroup]),1,"")))

On the report itself, I have a grouping level based on the [MonthGroup]
field that I added in the report query.

The problem that I am running in to with the report as it exists is:

Assuming the date range will be 01/01/06 - 6/30/06; if a resident were to
admit to the facility on 2/15/06 and discharge on 3/5/06 and then readmit on
4/12/06, the resident will not register under the month of the discharge --
only in the two months of the admissions.

I hope that helps to answer a little bit about what I am trying to do.
Clearly I am no expert at knowing what I am doing, which probably makes what
I'm doing much more difficult. But, if you can try to figure out the logic
behind what I am trying to do and come up with something different that
works, ok.

I don't understand what you mean by:
How can I have the report recognize all of the dates and show the resident
for each month that is relevant for the report date range?

Counting the number of residents discharged during July of 2006 would be:

=Sum(Abs( Format([DischargeDate],"yyyymm") ="200607"))

--
Duane Hookom
MS Access MVP

Obviously, I would have date fields -- I have two of them (admisison date
and
discharge date). My primary key is ResidentID in TblResidents and is
linked
to TblAdmissionsDischarges, where there can be one ResidentID from
TblResidents to many records in the TblAdmissionsDischarges.

In the report, I have grouped all of the months together by using the
DateSerial expression where all the dates are converted to the 1st day of
the
1st year, with any one of the 12 months.

I hope this helps to answer some of your questions, again, any help that
you
can give is greatly appreciated! Thank you.

You have asked many questions. You haven't told us anything about your
tables. We don't even know if you have any date fields.

--
Duane Hookom
MS Access MVP

I have been struggling with my problem for a while now, and feel that
the
answer may be quite simple; except that I can't figure out what it is.
I
am
running a report for Admissions vs. Discharges for my facility. The
time
frame can span over one month or over several months, in which case,
one
resident may have discharged from the facility and returned (admitted)
again
at a later date. If I run the report based on month-to-month, all of
the
information appears to be accurate. It is when I run multiple months
that
at
least one of the residents are dropped if they have more than one
admit/discharge/admit in the specified report date range.

My two tables are TblResidents and another one for
TblAdmissionsDischarges.

How can I have the report recognize all of the dates and show the
resident
for each month that is relevant for the report date range?

Secondly, I need to figure out how I can summarize the total number of
admissions and total number of discharges for each month. I had a
Count([Admissions]) field added to the group footer, but it doesn't
count
only the admissions, it counts all names listed for that particular
month --
discharges and all. Any help is greatly appreciated.

 

[Duane Hookom]

You might want to consider creating a union query like:

SELECT ResidentID , "D" as AD, DCDate as ADDate
FROM tblAdmissionsDischarges
WHERE DCDate Is Not Null
UNION ALL
SELECT ResidentID , "A", AdmitDate
FROM tblAdmissionsDischarges
WHERE AdmitDate Is Not Null;

You could then create a crosstab query based on this union query. Set the
ADDate as the Row Heading, AD as the Column Heading, and Count of ResidentID
as the Value. This will give you a count of resident admits and discharges
by date.

You can filter the crosstab by beginning and ending dates. You can also
group by Year and Month of the ADDate.

Are you also looking for the resident population for each month?


--
Duane Hookom
MS Access MVP

Ok. Let me see if I can try to answer some questions that haven't been
asked
yet.

Some of the fields in the report that are based on the two tables
(TblAdmissionsDischarges and TblResidents) include:
ResidentLastName
ResidentFirstName
AdmitDate
DCDate

I am grouping all of my admissions in the report query as:
AdmitMonthGroup:
DateSerial(1,Month([AdmitDate]),1)

I am grouping all of my discharges in the report query as: DCMonthGroup:
IIf([DCDate] Is Null,"",(DateSerial(1,Month([DCDate]),1)))

Then, I am attempting to group all of these two in to one month group in
the report query as: MonthGroup: IIf([AdmitDate]>=Forms![Report Date
Range]![Beginning Rpt
Date],DateSerial(1,Month([AdmitDate]),1),(DateSerial(1,Month([DCDate]),1)))

Then, if all of these things work, I am trying to show a "1" in the new
field [Admissions] that I added to the report query as Admissions:
IIf([AdmitDate]>=Forms![Report Date Range]![Beginning Rpt Date],1,""). A
new
field for Discharges in the report query would be the same way:
Discharges:
IIf(DatePart("m",[DCDate])>DatePart("m",[MonthGroup]),"",IIf([DCDate] Is
Null,"",IIf(DatePart("m",[DCDate])<=DatePart("m",[MonthGroup]),1,"")))

On the report itself, I have a grouping level based on the [MonthGroup]
field that I added in the report query.

The problem that I am running in to with the report as it exists is:

Assuming the date range will be 01/01/06 - 6/30/06; if a resident were to
admit to the facility on 2/15/06 and discharge on 3/5/06 and then readmit
on
4/12/06, the resident will not register under the month of the
discharge --
only in the two months of the admissions.

I hope that helps to answer a little bit about what I am trying to do.
Clearly I am no expert at knowing what I am doing, which probably makes
what
I'm doing much more difficult. But, if you can try to figure out the
logic
behind what I am trying to do and come up with something different that
works, ok.

I don't understand what you mean by:
How can I have the report recognize all of the dates and show the
resident
for each month that is relevant for the report date range?

Counting the number of residents discharged during July of 2006 would be:

=Sum(Abs( Format([DischargeDate],"yyyymm") ="200607"))

--
Duane Hookom
MS Access MVP

Obviously, I would have date fields -- I have two of them (admisison
date
and
discharge date). My primary key is ResidentID in TblResidents and is
linked
to TblAdmissionsDischarges, where there can be one ResidentID from
TblResidents to many records in the TblAdmissionsDischarges.

In the report, I have grouped all of the months together by using the
DateSerial expression where all the dates are converted to the 1st day
of
the
1st year, with any one of the 12 months.

I hope this helps to answer some of your questions, again, any help
that
you
can give is greatly appreciated! Thank you.

:

You have asked many questions. You haven't told us anything about your
tables. We don't even know if you have any date fields.

--
Duane Hookom
MS Access MVP

I have been struggling with my problem for a while now, and feel that
the
answer may be quite simple; except that I can't figure out what it
is.
I
am
running a report for Admissions vs. Discharges for my facility. The
time
frame can span over one month or over several months, in which case,
one
resident may have discharged from the facility and returned
(admitted)
again
at a later date. If I run the report based on month-to-month, all
of
the
information appears to be accurate. It is when I run multiple
months
that
at
least one of the residents are dropped if they have more than one
admit/discharge/admit in the specified report date range.

My two tables are TblResidents and another one for
TblAdmissionsDischarges.

How can I have the report recognize all of the dates and show the
resident
for each month that is relevant for the report date range?

Secondly, I need to figure out how I can summarize the total number
of
admissions and total number of discharges for each month. I had a
Count([Admissions]) field added to the group footer, but it doesn't
count
only the admissions, it counts all names listed for that particular
month --
discharges and all. Any help is greatly appreciated.