Two Tables with Different Dates

[Guest]

Hi there,

I have a problem in that I want to create a query with two tables that have
exactly the same fields in them. The fields in each table are based upon an
Admit Date. One table is called the Encounter table and the other is the
Encounter History table. None of the dates in the tables are duplicates.
There is a third table which is called Patient Registry. I need to be able
to show all Admit Dates and their pertaining information for each Patient in
my query. That would be a total of Admit Dates from the Encounter and the
Encounter History tables per patient. My problem is all the fields in each
Encounter table have exactly the same names, yet I need to show info from
both. I hope someone can help. Thanks.

Janet

 

[Lynn Trapp]

You might want to look at using a Union query.

Select *
From Encounters
UNION
Select *
From EncounterHistory;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Lynn,

Thanks, but I also have to include several fields from a third table,
Patient Registry, which has the field which everything will be sorted by in
the final query, and that is the Patient Number. Any idea how to include
this? Thanks for your help.

Janet

 

[Guest]

Use table aliases. For example,

Select T1.FieldName, T2.FieldName
from Table1 T1, Table2 T2 etc.

Jet will resolve using the aliases for the tables that have the same field
names.

Good Luck!

 

[Lynn Trapp]

Do the Union query and, then, Join that query with the Patient table. I'm
assuming one of the fields in the encounter tables is a Patient ID or
something.

Select PatientRegistry.field1,
PatientRegistry.field2,
YourUnionQuery.Field3,
YourUnionQuery.Field4
From PatientRegistry Join YourUnionQuery ON YourUnionQuery.PatientID =
PatientRegistry.PatientID;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Thanks Lynn. That worked.

Janet

Do the Union query and, then, Join that query with the Patient table. I'm
assuming one of the fields in the encounter tables is a Patient ID or
something.

Select PatientRegistry.field1,
PatientRegistry.field2,
YourUnionQuery.Field3,
YourUnionQuery.Field4
From PatientRegistry Join YourUnionQuery ON YourUnionQuery.PatientID =
PatientRegistry.PatientID;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Lynn Trapp]

That's great. I'm glad it worked.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html