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]));
John Spencer MVP said:
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!!!