|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 6th, 2009, 04:21 AM
posted to microsoft.public.access.reports
|
|||
|
|||
Including records with null values in report
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? 
|
|
#2
December 6th, 2009, 05:23 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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 "omoluabi" wrote in message ... 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?
|
|
#3
December 6th, 2009, 02:05 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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" wrote: 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 "omoluabi" wrote in message ... 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? .
|
|
#4
December 6th, 2009, 05:17 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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" wrote in message ... 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" wrote: 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 "omoluabi" wrote in message ... 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? .
|
|
#5
December 6th, 2009, 09:00 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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" wrote: 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" wrote in message ... 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" wrote: 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 "omoluabi" wrote in message ... 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? . .
|
|
#6
December 6th, 2009, 09:35 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "omoluabi" wrote in message ... 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? . .
|
|
#7
December 6th, 2009, 10:19 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "omoluabi" wrote in message ... 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? . . .
|
|
#8
December 6th, 2009, 10:25 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "omoluabi" wrote in message ... 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? . . .
|
|
#9
December 6th, 2009, 11:14 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Including records with null values in report
Thanks Gina, I'll be sure t check it out.
"Gina Whipp" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "omoluabi" wrote in message ... 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
