Filter records from different table

[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

 

[John Vinson]

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]

 

[Guest]

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

 

[John Vinson]

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]

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]

 

[Guest]

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

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]

 

[Guest]

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

 

[John Vinson]

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]

 

[John Vinson]

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]

 

[Guest]

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:])));

 

[John Vinson]

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:])));

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]

 

[Guest]

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,
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:])));

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]

 

[John Vinson]

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

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]