omoluabi,
First part...
Then why is tblMedUpdates and tblMedErrors not in one table? However, in
your present state...
tblClients LEFT JOIN tblClientReviewer LEFT JOIN balance of the tables.
Use
the ClientID from tblClients to insure you are capturing every Client.
Note, the balance of the tables must be joined to tblClientReviewer or
tblClients because attempting to use a LEFT JOIN on the balance of tables
if
they have no record in that table you still won't see any results. Which
is
why I think you should bite the bullet and use subreports.
Last part...
Yes, changing the JOINS changes your results...
tblClients LEFT JOIN tblClientReviewer = All Clients whether they have a
Reviewer or not.
tblClients INNER JOIN tblClientReviewer = All Clients that have a
Reviewer.
tblClients RIGHT JOIN tblClientReviewer = Only Clients that have a
Reviewer
and even those Reviewers who have no Clients.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina,
You're right about the Parent table setup. tblClients and tblReviewer
are
the Parents of tblClientReviewer. Forgive me for leaving that fact out.
tblClientReviewer in turn has its own children which are the 4 child
tables
(tblMedUpdates,tblIncidents,tblRecs&Out and tblMedErrors).
What I'm trying to do is record Monthly reports for the clients of an
healthcare small business. The reports come in at the start of each
month
and
a record has to be filed for each client (the service recipient).
The Reviewer is the employee of the small business who filed the report
on
a
specific Client.
My table is set up in such a way that for a new Review/Record to be
created,
the user has to supply the Client's First Name and Last name and also
the
Reviewer's First name and Last name. The primary keys of these two
tables
(tblClient and tblReviewers) are foreign keys on the tblClientReviewer
table.
This contain a few other details such as the date of the review. The
primary
key of the tblClientReviewer is then used as foreign keys on the rest
of
the
tables (Child tables) which has the details of that month's report.
I hope that explains it.
Regarding the JOIN, does the type of JOIN i specified in the
relationship
alter the results of my query? If all else fails then I would have to
go
the
subreports way. Thanks for your kind help!
:
omoluabi,
Not sure I understand why tblClientReviewer would be the Parent table
as
opposed to tblClients, ie...
tblClients
cClientID (PK)
tblReviewer
rReviewerID (PK)
tblClientReviewer
crClientID (FK)
crReviewer (FK)
Perhaps explaining what it is you are trying to accomplish and your
table
structure would help me better understand the below query. For
instance
are
you trying to track Clients reactions to medications or the Reviewers
actions? I digress...
To answer your question, you are using INNER JOINS which say unless
this
data is avaialable in ALL tables do NOT show it to me. You might try
a
LEFT
JOIN from your parent table (tblClientReviewer) and work your way from
there. HOWEVER, looking at what you want to accomplish and the way
your
tables are presently set up, I am going to recommend subreports.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Thanks for the response. I wasn't aware of the existence subreports
in
access
until yesterday. Even though that seems to solve my problem, I
wouldn't
want
to go through the lengthy process of reformatting the report design.
Do
you
know of a way that I can easily port my existing report format into
a
subreport? By format I mean the placement of the report's controls.
Going by the query method here is the whole of the query. Few things
to
note:
tblClientReviewer is the main parent table, all other tables apart
from
tblClient and tblReviewer depends on it. I hope it's not too
lengthy.
Thanks!
SELECT tblClientReviewer.ReviewID, tblClientReviewer.Name_Of_ISC,
tblClientReviewer.ISC_Agency, tblClientReviewer.Months,
tblClientReviewer.DateOfReview, tblClientReviewer.ISCEffectiveDate,
tblClients.FirstName AS tblClients_FirstName, tblClients.LastName AS
tblClients_LastName, tblReviewer.FirstName AS tblReviewer_FirstName,
tblReviewer.LastName AS tblReviewer_LastName,
tblMedUpdates.Schdl_4_Appt,
tblMedUpdates.New_Prescriptn, tblMedUpdates.Phy_Discntd_Med,
tblMedUpdates.Admitted_Hospt, tblMedUpdates.DischHospt,
tblMedUpdates.Changes_Trtmnt, tblMedUpdates.Changes_Med,
tblMedUpdates.Met_Needs, tblMedUpdates.Referrd_to_Specialist,
tblMedUpdates.Declined_treatmnt, tblMedUpdates.Awaiting_Results,
tblMedUpdates.Appntmnt_Reschdl, tblMedUpdates.Not_Coperative,
tblMedUpdates.DidNotMedNeeds, tblMedUpdates.Apmnt_Date,
tblMedUpdates.NewPrecriptn, tblMedUpdates.DiscontinuedMeds,
tblMedUpdates.ChangedMeds, tblMedUpdates.LabTestCompltd,
tblMedUpdates.LabTestDetails, tblMedUpdates.AdmtdListHospitals,
tblMedUpdates.Ref2SpecMet, tblMedUpdates.AdmitdListHospOthers,
tblMedUpdates.Ref2SpecOMet, tblMedUpdates.Ref2SpecUnmet,
tblMedUpdates.Ref2SpecUnmetO, tblMedUpdates.DischHospDate,
tblMedUpdates.AptReschlDate, tblMedUpdates.NoAppPlan,
tblMedUpdates.ConsvrtNoConsent, tblMedUpdates.AwaitConConsent,
tblMedUpdates.AwaitRegOConsent, tblMedUpdates.ConsvrtDNRespond,
tblMedUpdates.ChangeTreatPlan, tblMedUpdates.RefusedMedApp,
[tblRecs&Outs].Outcome, [tblRecs&Outs].Recomendation,
[tblRecs&Outs].[Action
Steps], [tblRecs&Outs].MetAction, [tblRecs&Outs].UnmetAction,
[tblRecs&Outs].Responsible_Party, [tblRecs&Outs].Hand_O_Hand,
[tblRecs&Outs].Verbal_Prompt, [tblRecs&Outs].Gestures,
[tblRecs&Outs].Physical_App, [tblRecs&Outs].Partial_Phy_App,
[tblRecs&Outs].Completed_Step_Indpd, [tblRecs&Outs].Not_Intersted,
[tblRecs&Outs].Not_Phy_Capable, [tblRecs&Outs].Refused_to_Part,
[tblRecs&Outs].Lack_Of_funds, [tblRecs&Outs].Chose_Alternate,
[tblRecs&Outs].No_Approved_Plan,
[tblRecs&Outs].Awaiting_Approved_Cost,
[tblRecs&Outs].Starcare_Not_Responsible,
[tblRecs&Outs].NotMetOthers,
[tblRecs&Outs].ISCNotified, [tblRecs&Outs].ISCNotUpdated,
[tblRecs&Outs].PManager, [tblRecs&Outs].SRImproved,
[tblRecs&Outs].SRMinimal,
[tblRecs&Outs].SRAddSupport, [tblRecs&Outs].RecomContS,
[tblRecs&Outs].RecomDiscS, [tblRecs&Outs].ResPStarcare,
[tblRecs&Outs].ResPISC, [tblRecs&Outs].ResPOTPT,
[tblRecs&Outs].ResPSLP,
[tblRecs&Outs].ResPFamily, [tblRecs&Outs].ResPSR,
[tblRecs&Outs].ResPBA,
tblIncidents.IncidentYes, tblIncidents.IncidentNo,
tblIncidents.[Behavioral/Psychiatric], tblIncidents.Prop_Destrc,
tblIncidents.Med_Incdnt, tblIncidents.Elopement,
tblIncidents.Sex_Aggrssn,
tblIncidents.Alleged_Abuse, tblIncidents.Alleged_Neglect,
tblIncidents.Exploitation, tblIncidents.Serious_Injury,
tblIncidents.Unknown,
tblIncidents.Other_incidents, tblIncidents.RBPFreq,
tblIncidents.RMIFreq,
tblIncidents.EFreq, tblIncidents.SAFreq, tblIncidents.OFreq,
tblIncidents.AAFreq, tblIncidents.ANFreq, tblIncidents.AEFreq,
tblIncidents.SInFreq, tblIncidents.UnFreq, tblIncidents.PDFreq,
tblIncidents.Comments, tblMedicationErrors.ErrorYes,
tblMedicationErrors.YesPhyContacted,
tblMedicationErrors.YesRegNurse,
tblMedicationErrors.YesMedErrorComp, tblMedicationErrors.YesMedVar,
tblMedicationErrors.YesCompltn_date, tblMedicationErrors.id,
tblMedicationErrors.ErrorNo
FROM ((((tblReviewer INNER JOIN (tblClients INNER JOIN
tblClientReviewer
ON
tblClients.[Client_ID] = tblClientReviewer.[Client_ID]) ON
tblReviewer.[ReviewerID] = tblClientReviewer.[ReviewerID]) INNER
JOIN
tblMedUpdates ON tblClientReviewer.[ReviewID] =
tblMedUpdates.[ReviewID])
INNER JOIN tblIncidents ON tblClientReviewer.ReviewID =
tblIncidents.ReviewID) INNER JOIN tblMedicationErrors ON
tblClientReviewer.ReviewID = tblMedicationErrors.Review_ID) INNER
JOIN
[tblRecs&Outs] ON tblClientReviewer.ReviewID =
[tblRecs&Outs].ReviewID;
:
omoluabi,
Yep, that will happen, especially if you don't use the proper joins
and
in
the right places. However, instead of creating a query with all
the
tables
why not use subreports the same way you used subforms. If you want
to
use
your query you will need to copy/paste the SQL of your query here.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
I'm buliding an access database for a small business.
I have a parent table which is related to 4 child tables. The
four
child
tables are subforms in my form design.
I created a query which pulls data from all 5 tables (parent and
children
included) using the Q wizard and used this for my report design.
.