Filter records from different table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
Click to expand...

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

John Vinson said:
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?
Click to expand...

A Subquery will work: use a criterion on ID of

IN(SELECT Encounters.ID FROM Encounters WHERE NextVisitDate = [Enter
date:])


John W. Vinson[MVP]
Click to expand...
 
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?
Click to expand...

A Subquery will work: use a criterion on ID of

IN(SELECT Encounters.ID FROM Encounters WHERE NextVisitDate = [Enter
date:])


John W. Vinson[MVP]
Click to expand...

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?

John Vinson said:
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?
Click to expand...

A Subquery will work: use a criterion on ID of

IN(SELECT Encounters.ID FROM Encounters WHERE NextVisitDate = [Enter
date:])


John W. Vinson[MVP]
Click to expand...

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]
Click to expand...
 
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
Click to expand...
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?

John Vinson said:
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?
Click to expand...

A Subquery will work: use a criterion on ID of

IN(SELECT Encounters.ID FROM Encounters WHERE NextVisitDate = [Enter
date:])


John W. Vinson[MVP]
Click to expand...
 
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?
Click to expand...

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]
 
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?
Click to expand...

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:])));
 
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:])));
Click to expand...

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]
Click to expand...
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

John Vinson said:
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:])));
Click to expand...

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]
Click to expand...
 
Sorry for the delayed responce.
Removing
INNER JOIN Encounter ON Main.[Medical Record] = Encounter.[Medical
Record]
Click to expand...
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
Click to expand...

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]
 
Back
Top