 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / October 2005Filter records from different table
Hi, This forum is wonderfull. I got all my questions anwered, but I got another one. I have two linked tables by ID MAIN and ENCOUNTER I have created a form using MAIN table and used subform function to list date from ENCOUNTER table for that ID. I have multiple encounters for each ID (patients). I export ahead of time the list of patients that would be coming on particular day to both MAIN and ENCOUNTER tables. Duplicate values are filtered out of MAIN table, so there are no duplicate records for one patients. Patients have multiple visits, so ENCOUNTER contains multiple entries for the same ID with different date Now, I would like to filter my MAIN table to show only patients that are scheduled to arrive on particular date. I do not want to limit myself to today [Date()]. The date of the next visit is entered in ENCOUNTER table, but not in MAIN table. Is it possible to accomplish? Thanks >Now, I would like to filter my MAIN table to show only patients that are >scheduled to arrive on particular date. I do not want to limit myself to >today [Date()]. The date of the next visit is entered in ENCOUNTER table, >but not in MAIN table. >Is it possible to accomplish? A Subquery will work: use a criterion on ID of IN(SELECT Encounters.ID FROM Encounters WHERE NextVisitDate = [Enter date:]) John W. Vinson[MVP] Where do I enter it? that sounds great > >Now, I would like to filter my MAIN table to show only patients that are > >scheduled to arrive on particular date. I do not want to limit myself to [quoted text clipped - 8 lines] > > John W. Vinson[MVP] >>Now, I would like to filter my MAIN table to show only patients that are >>scheduled to arrive on particular date. I do not want to limit myself to [quoted text clipped - 8 lines] > > John W. Vinson[MVP] On the Criteria line under your Encounters ID field. You'll need to change the fieldnames to those in your table, of course. John W. Vinson[MVP] that presumes that I am working through query. I initially made a mistake and based by form and subform on tables. Is it possible to change, without recreating whole form? > >>Now, I would like to filter my MAIN table to show only patients that are > >>scheduled to arrive on particular date. I do not want to limit myself to [quoted text clipped - 13 lines] > > John W. Vinson[MVP] >that presumes that I am working through query. I initially made a mistake and >based by form and subform on tables. Is it possible to change, without >recreating whole form? Yes; open the Form in design view, select its Recordsource property, click the ... icon and accept Access' offer to open the table as a query. John W. Vinson[MVP] Well, It seems that I figured it out. I have created new query combining both tables (Main and Encounter) and then I placed the expression IN(SELECT Encounter.[Medical Record] FROM Encounter WHERE Date = [Enter > date:]) in criteria Then I recreated the stucture of my subform. So far it wourks great. The only small glitch is that if patient had several visits, it pulls record for that patient several times. I am not sure why that happens. Any idea? > >Now, I would like to filter my MAIN table to show only patients that are > >scheduled to arrive on particular date. I do not want to limit myself to [quoted text clipped - 8 lines] > > John W. Vinson[MVP] >Then I recreated the stucture of my subform. So far it wourks great. >The only small glitch is that if patient had several visits, it pulls record >for that patient several times. I am not sure why that happens. Any idea? Please open the Query in SQL view and post it here; I'm not seeing why you should get dups! John W. Vinson[MVP] SELECT Main.[Patient Name], Main.[Medical Record], Main.DOB, Main.Age, Main.[Initial Date], Main.Diagnosis, Main.[Refering MD], Encounter.Date, Encounter.Type FROM Main INNER JOIN Encounter ON Main.[Medical Record] = Encounter.[Medical Record] WHERE (((Main.[Medical Record]) In (SELECT Encounter.[Medical Record] FROM Encounter WHERE Date = [Enter date:]))); > >Then I recreated the stucture of my subform. So far it wourks great. > >The only small glitch is that if patient had several visits, it pulls record [quoted text clipped - 4 lines] > > John W. Vinson[MVP] >SELECT Main.[Patient Name], Main.[Medical Record], Main.DOB, Main.Age, >Main.[Initial Date], Main.Diagnosis, Main.[Refering MD], Encounter.Date, [quoted text clipped - 3 lines] >WHERE (((Main.[Medical Record]) In (SELECT Encounter.[Medical Record] FROM >Encounter WHERE Date = [Enter date:]))); If you're using the join, you WILL get one record for each encounter - and you're specifically asking to see each encounter date and encounter type. If you just want to see Patient info on this query (while displaying encounter data on a subform, perhaps), simply use the Encounter table only in the subform: SELECT Main.[Patient Name], Main.[Medical Record], Main.DOB, Main.Age, Main.[Initial Date], Main.Diagnosis, Main.[Refering MD] FROM Main WHERE (((Main.[Medical Record]) In (SELECT Encounter.[Medical Record] FROM Encounter WHERE [Date] = [Enter date:]))); Note that Date is a reserved word (for the built in Date() function) and is best avoided as a fieldname. I'd suggest calling it EncounterDate. John W. Vinson[MVP] Sorry for the delayed responce. Removing INNER JOIN Encounter ON Main.[Medical Record] = Encounter.[Medical >Record] converted linked encounter records to Expr1: Encounter.Date, etc Now when I try to open form it is asking me for parameter on every field in the quiery. I do not think that it will work > >SELECT Main.[Patient Name], Main.[Medical Record], Main.DOB, Main.Age, > >Main.[Initial Date], Main.Diagnosis, Main.[Refering MD], Encounter.Date, [quoted text clipped - 23 lines] > > John W. Vinson[MVP] >Sorry for the delayed responce. >Removing [quoted text clipped - 4 lines] >Now when I try to open form it is asking me for parameter on every field in >the quiery. I do not think that it will work Well, that ISN'T what I suggested. Base the mainform on the patient table. Use controls on the mainform to display patient information. Base the subform on the encounter table. Use controls on the subform to display encounter information. If this isn't working as you expect, please post the SQL view of the mainform's Recordsource, and that of the Subform's recordsource as well. John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.