Retrieve Info from two tables

[Fernb2000]

Hello;

I have two tables

Table 1 Rx
-------
RxNumber, RxId(no duplicates), RxDate, Name, Notes, Type, DOB

Table 2 Email
-------
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type


I would like the result to be

Result Table
------------
Rx Id, RxDate, Name, Notes(or Subject), Type

Table 1 can be searched by date, or Name or DOB I want the result of
this search to go throug table 2
and return to me all emails that match thre previous results RxIds

For example Table 1
RxNumber, RxId, RxDate, Name, Notes, Type, DOB
1 99 1/1/2005 Person1 Note1 1 1/1/95
2 98 3/1/2005 Person2 Note2 1 1/1/96
3 97 5/1/2004 Person3 Note3 1 1/1/97
4 96 1/1/2004 Person4 Note4 1 1/1/98
5 95 6/1/2005 Person5 Note5 1 1/1/99

For Example Table 2
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type
1 100 S1 M1 T1 F1 2
2 98 S2 M2 T2 F2 2
3 101 S3 M3 T3 F3 2
4 95 S4 M4 T4 F4 2
5 98 S5 M5 T5 F5 2
6 95 S6 M6 T6 F6 2
7 96 S7 M7 T7 F7 2
8 98 S8 M8 T8 F8 2
9 100 S9 M9 T9 F9 2
10 100 S10 M10 T10 F10 2

If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005
I would like the resulting table to be
RxId RxDate Name Note/Subject Type
99 1/1/2005 Person1 Note1 1
98 3/1/2005 Person1 Note2 1
98 3/1/2005 Person2 S2 2
98 3/1/2005 Person2 S5 2
98 3/1/2005 Person2 S8 2
95 6/1/2005 Person5 Note5 1
95 6/1/2005 Person2 S4 2
95 6/1/2005 Person2 S6 2

I have tried a combination of inner joins and Union with no luck any
suggestions

Thanks

Fernb200

 

[MGFoster]

Hello;

I have two tables

Table 1 Rx
-------
RxNumber, RxId(no duplicates), RxDate, Name, Notes, Type, DOB

Table 2 Email
-------
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type


I would like the result to be

Result Table
------------
Rx Id, RxDate, Name, Notes(or Subject), Type

Table 1 can be searched by date, or Name or DOB I want the result of
this search to go throug table 2
and return to me all emails that match thre previous results RxIds

For example Table 1
RxNumber, RxId, RxDate, Name, Notes, Type, DOB
1 99 1/1/2005 Person1 Note1 1 1/1/95
2 98 3/1/2005 Person2 Note2 1 1/1/96
3 97 5/1/2004 Person3 Note3 1 1/1/97
4 96 1/1/2004 Person4 Note4 1 1/1/98
5 95 6/1/2005 Person5 Note5 1 1/1/99

For Example Table 2
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type
1 100 S1 M1 T1 F1 2
2 98 S2 M2 T2 F2 2
3 101 S3 M3 T3 F3 2
4 95 S4 M4 T4 F4 2
5 98 S5 M5 T5 F5 2
6 95 S6 M6 T6 F6 2
7 96 S7 M7 T7 F7 2
8 98 S8 M8 T8 F8 2
9 100 S9 M9 T9 F9 2
10 100 S10 M10 T10 F10 2

If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005
I would like the resulting table to be
RxId RxDate Name Note/Subject Type
99 1/1/2005 Person1 Note1 1
98 3/1/2005 Person1 Note2 1
98 3/1/2005 Person2 S2 2
98 3/1/2005 Person2 S5 2
98 3/1/2005 Person2 S8 2
95 6/1/2005 Person5 Note5 1
95 6/1/2005 Person2 S4 2
95 6/1/2005 Person2 S6 2

I have tried a combination of inner joins and Union with no luck any
suggestions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try (untested):

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT R.RxId, R.RxDate, R.Name, E.Subject & " " & R.Note As SubNote,
R.Type
FROM Rx As R LEFT JOIN Email As E ON R.RxId = E.RxId
WHERE R.RxDate BETWEEN [Start Date?] And [End Date?]

You didn't specify the criteria for showing the Subject/Note so I just
concatenated the values of both columns.

Your example results should show RxIds for 95-99, but it doesn't. Is
that an oversight or is there another criteria specification you haven't
mentioned?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQveDTYechKqOuFEgEQKaLACfbBooTH3Zw5D1pWEG7Utw5Ws4MhIAoNif
Rg7Ols131abP8l8hRBpBYYq6
=ZxoW
-----END PGP SIGNATURE-----

 

[Fernb2000]

Hello;

The Notes/Subject column should only show either the Note of the Rx or
the Subject of the email. In other words what the resulting table
should show is the all the rxs from table 1 from an original query and
then ist should also show all emails related to the rxs that were just
selected on the original query..

thanks

Fernb2000