Including records with null values in report


O

omoluabi

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.
Here I realized that if all the tables have data in them, the report works
fine. However, if one of the child tables contain null in it's primary key
field (i.e no data was entered in that table at all) the whole of that record
including data from the parent and child tables will not be displayed.
This is a problem as I need to be able to view everything that was entered.
So far I've changed the JOIN type of the parent-to-child relationships, yet
it still yields the same output. Somebody please help, what am I doing wrong?
 
Ad

Advertisements

G

Gina Whipp

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
 
O

omoluabi

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;
 
G

Gina Whipp

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

omoluabi said:
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;


Gina Whipp said:
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




.
 
O

omoluabi

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!




Gina Whipp said:
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

omoluabi said:
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;


Gina Whipp said:
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.
Here I realized that if all the tables have data in them, the report
works
fine. However, if one of the child tables contain null in it's primary
key
field (i.e no data was entered in that table at all) the whole of that
record
including data from the parent and child tables will not be displayed.
This is a problem as I need to be able to view everything that was
entered.
So far I've changed the JOIN type of the parent-to-child relationships,
yet
it still yields the same output. Somebody please help, what am I doing
wrong?


.


.
 
G

Gina Whipp

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

omoluabi said:
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!




Gina Whipp said:
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

omoluabi said:
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.
Here I realized that if all the tables have data in them, the report
works
fine. However, if one of the child tables contain null in it's
primary
key
field (i.e no data was entered in that table at all) the whole of
that
record
including data from the parent and child tables will not be
displayed.
This is a problem as I need to be able to view everything that was
entered.
So far I've changed the JOIN type of the parent-to-child
relationships,
yet
it still yields the same output. Somebody please help, what am I
doing
wrong?


.


.
 
Ad

Advertisements

O

omoluabi

Gina,
Thanks for sucessfully convincing me to use subreports..:).
What you explained there got me confused even more. I will use subreports
which I understand better and can always expand upon it whenever the need
arises.

Thanks again!

Gina Whipp said:
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

omoluabi said:
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!




Gina Whipp said:
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.
Here I realized that if all the tables have data in them, the report
works
fine. However, if one of the child tables contain null in it's
primary
key
field (i.e no data was entered in that table at all) the whole of
that
record
including data from the parent and child tables will not be
displayed.
This is a problem as I need to be able to view everything that was
entered.
So far I've changed the JOIN type of the parent-to-child
relationships,
yet
it still yields the same output. Somebody please help, what am I
doing
wrong?


.



.


.
 
G

Gina Whipp

omoluabi,

Good choice...

In your spare time have a look at... http://allenbrowne.com/casu-22.html
Specifically, Part 6 but it's really a great tutorail so starting at Part 1
won't hurt.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

omoluabi said:
Gina,
Thanks for sucessfully convincing me to use subreports..:).
What you explained there got me confused even more. I will use subreports
which I understand better and can always expand upon it whenever the need
arises.

Thanks again!

Gina Whipp said:
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

omoluabi said:
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.
Here I realized that if all the tables have data in them, the
report
works
fine. However, if one of the child tables contain null in it's
primary
key
field (i.e no data was entered in that table at all) the whole of
that
record
including data from the parent and child tables will not be
displayed.
This is a problem as I need to be able to view everything that
was
entered.
So far I've changed the JOIN type of the parent-to-child
relationships,
yet
it still yields the same output. Somebody please help, what am I
doing
wrong?


.



.


.
 
Ad

Advertisements

O

omoluabi

Thanks Gina, I'll be sure t check it out.

Gina Whipp said:
omoluabi,

Good choice...

In your spare time have a look at... http://allenbrowne.com/casu-22.html
Specifically, Part 6 but it's really a great tutorail so starting at Part 1
won't hurt.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

omoluabi said:
Gina,
Thanks for sucessfully convincing me to use subreports..:).
What you explained there got me confused even more. I will use subreports
which I understand better and can always expand upon it whenever the need
arises.

Thanks again!

Gina Whipp said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top