Motel Register - Displaying records between dates

[motelblues]

My brain is fried and I can't figure this out.

I need a Monthly register report of Clients staying at the motel.

I have an admission Date field in an admisions table and a discharge date
field in a discharge table.

How do I create a query to show all clients who have stayed at the motel in
any given month? ( as some clients stay for months at a time and others stay
for only a few days..)

Please help!!!

 

[vanderghast]

SELECT DISTINCT client
FROM table
WHERE dateIn<= #3/31/2009# AND dateOut >=#3/1/2009#



would give your the number of clients for month of March 2009, have they
stay just one night, of do they stay the whole year (dateIn = first of
January 2009, dateOut=31st of December 2009)



Vanderghast, Access MVP

 

[John Spencer MVP]

If you are doing just ONE month at a time the solution is different than if
you need to do a year's worth of months.

First of all we need more information about your table structure? You say you
have an admissions table and a discharge table. How do you relate the
records for a client between the two tables? Based on a ClientID field?

With the limited information you have provided, the query would look something
like the following for january 2009 where you enter the first day of the month
and the last day of the month as part of the criteria.

SELECT Count(Admissions.AdmitDate) as TheCount
FROM Admissions LEFT JOIN Discharge
ON Admissions.ClientID = Discharge.ClientID
WHERE Admissions.AdmitDate <= #2009-01-31#
AND Nz(DischargeDate,#2030-12-31#) >=#2009-01-01#

If you need to do this type of thing for multiple months in ONE query then the
solution is more complex

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
zero through maximum possible months stay.

SELECT REGISTER.Clients, REGISTER.Admission, REGISTER.Discharge,
DateAdd("m",[CountNUM],DateSerial(Year([Admission]),Month([Admission]),1)) AS
[Stay Month]
FROM CountNumber, REGISTER
WHERE
(((DateAdd("m",[CountNUM],DateSerial(Year([Admission]),Month([Admission]),1)))<=[Discharge]
And
(DateAdd("m",[CountNUM],DateSerial(Year([Admission]),Month([Admission]),1)))=[Enter report period (6/1/2009)]))
ORDER BY REGISTER.Clients,
DateAdd("m",[CountNUM],DateSerial(Year([Admission]),Month([Admission]),1));

 

[motelblues]

Maybe I should be a little more concise...

Basically, we have many return clients
so I have a clients table with ClientID as the primary key
Then I have an admissions Table with an AdmissionsID as the primary key and
the ClientID as a foreign key.
For when they check out, I have a Discharges table, with a DischargeID as
the primary key and the AdmissionsID as a foreign key.

Every month, my boss wants one report consisting of
the clients who arrived (including the Date of Admission)
the clients who departed (including the Date of Admission & Date of Discharge)
as well as the clients that were there the entire month
so that he can keep track of occupancy etc.

This is turning out to be more complicated than I thought it was going to be.

Thank you for any help you can give me!

 

[John Spencer MVP]

Ok this is going to make things a bit more complex.

SELECT tblAdmissions.*, tblDischarges.[Date Of Discharge]
FROM tblAdmissions LEFT JOIN tblDischarges
ON tblAdmissions.AdmissionID = tblDischarges.AdmissionID

Next query use that query to get the results
SELECT *
FROM aboveSavedQuery
WHERE [Date of Admission] >= #2009-01-31#
AND ([Date of Discharge] is NULL
OR [Date of Discharge] > #2009-01-01#)

If you need the Client information then add that into the above query

SELECT *
FROM aboveSavedQuery INNER JOIN ClientTable
ON aboveSavedQuery.ClientID = ClientTable.ClientID
WHERE [Date of Admission] >= #2009-01-31#
AND ([Date of Discharge] is NULL
OR [Date of Discharge] > #2009-01-01#)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[motelblues]

EVERYTHINGS RUNNING GREAT! THANK YOU!
To further complicate my little query I need to add more info..... such as
their referral source and status - even if there isn't on. What am I
missing?? (i'm so confused.)

SELECT Clients.CLFirstName, Clients.CLLastName, Clients.CLBirthdate,
Clients.PreviousService, CLREGstep1.AdmissDate, CLREGstep1.DisDate,
ServiceTypesDetails.ServiceType, ReferralsDetails.Referrals
FROM (((CLREGstep1
INNER JOIN (Clients
LEFT JOIN (ReferralsDetails
LEFT JOIN ServiceTypesDetails
ON (CLREFstep1.AdmissID = ServiceTypesDetails.AdmissID)
ON CLREGstep1.AdmissID = ReferralsDetails.AdmissID)
ON CLREGstep1.ClientID = Clients.ClientID)
WHERE (((CLREGstep1.AdmissDate)<=[End Date]) AND ((CLREGstep1.DisDate) Is
Null)) OR (((CLREGstep1.DisDate)>[Start Date] And (CLREGstep1.DisDate)<[End
Date]));

Ok this is going to make things a bit more complex.

SELECT tblAdmissions.*, tblDischarges.[Date Of Discharge]
FROM tblAdmissions LEFT JOIN tblDischarges
ON tblAdmissions.AdmissionID = tblDischarges.AdmissionID

Next query use that query to get the results
SELECT *
FROM aboveSavedQuery
WHERE [Date of Admission] >= #2009-01-31#
AND ([Date of Discharge] is NULL
OR [Date of Discharge] > #2009-01-01#)

If you need the Client information then add that into the above query

SELECT *
FROM aboveSavedQuery INNER JOIN ClientTable
ON aboveSavedQuery.ClientID = ClientTable.ClientID
WHERE [Date of Admission] >= #2009-01-31#
AND ([Date of Discharge] is NULL
OR [Date of Discharge] > #2009-01-01#)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Maybe I should be a little more concise...

Basically, we have many return clients
so I have a clients table with ClientID as the primary key
Then I have an admissions Table with an AdmissionsID as the primary key and
the ClientID as a foreign key.
For when they check out, I have a Discharges table, with a DischargeID as
the primary key and the AdmissionsID as a foreign key.

Every month, my boss wants one report consisting of
the clients who arrived (including the Date of Admission)
the clients who departed (including the Date of Admission & Date of Discharge)
as well as the clients that were there the entire month
so that he can keep track of occupancy etc.

This is turning out to be more complicated than I thought it was going to be.

Thank you for any help you can give me!

 

[John Spencer MVP]

Where is that information stored. If it is in the clients table then add the
fields to the select clause, if it is one of the other tables that are already
in the from clause - Add the table and field name to the select clause.

If in another table, then you will need to add the additional table to your query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

EVERYTHINGS RUNNING GREAT! THANK YOU!
To further complicate my little query I need to add more info..... such as
their referral source and status - even if there isn't on. What am I
missing?? (i'm so confused.)

SELECT Clients.CLFirstName, Clients.CLLastName, Clients.CLBirthdate,
Clients.PreviousService, CLREGstep1.AdmissDate, CLREGstep1.DisDate,
ServiceTypesDetails.ServiceType, ReferralsDetails.Referrals
FROM (((CLREGstep1
INNER JOIN (Clients
LEFT JOIN (ReferralsDetails
LEFT JOIN ServiceTypesDetails
ON (CLREFstep1.AdmissID = ServiceTypesDetails.AdmissID)
ON CLREGstep1.AdmissID = ReferralsDetails.AdmissID)
ON CLREGstep1.ClientID = Clients.ClientID)
WHERE (((CLREGstep1.AdmissDate)<=[End Date]) AND ((CLREGstep1.DisDate) Is
Null)) OR (((CLREGstep1.DisDate)>[Start Date] And (CLREGstep1.DisDate)<[End
Date]));

Ok this is going to make things a bit more complex.

SELECT tblAdmissions.*, tblDischarges.[Date Of Discharge]
FROM tblAdmissions LEFT JOIN tblDischarges
ON tblAdmissions.AdmissionID = tblDischarges.AdmissionID

Next query use that query to get the results
SELECT *
FROM aboveSavedQuery
WHERE [Date of Admission] >= #2009-01-31#
AND ([Date of Discharge] is NULL
OR [Date of Discharge] > #2009-01-01#)

If you need the Client information then add that into the above query

SELECT *
FROM aboveSavedQuery INNER JOIN ClientTable
ON aboveSavedQuery.ClientID = ClientTable.ClientID
WHERE [Date of Admission] >= #2009-01-31#
AND ([Date of Discharge] is NULL
OR [Date of Discharge] > #2009-01-01#)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Maybe I should be a little more concise...

Basically, we have many return clients
so I have a clients table with ClientID as the primary key
Then I have an admissions Table with an AdmissionsID as the primary key and
the ClientID as a foreign key.
For when they check out, I have a Discharges table, with a DischargeID as
the primary key and the AdmissionsID as a foreign key.

Every month, my boss wants one report consisting of
the clients who arrived (including the Date of Admission)
the clients who departed (including the Date of Admission & Date of Discharge)
as well as the clients that were there the entire month
so that he can keep track of occupancy etc.

This is turning out to be more complicated than I thought it was going to be.

Thank you for any help you can give me!




:

My brain is fried and I can't figure this out.

I need a Monthly register report of Clients staying at the motel.

I have an admission Date field in an admisions table and a discharge date
field in a discharge table.

How do I create a query to show all clients who have stayed at the motel in
any given month? ( as some clients stay for months at a time and others stay
for only a few days..)

Please help!!!