Find minimum value from different tables

[cardgage]

Good afternoon,

I am working on a project which involves looking at a particular variable for an individual that is collected many different times during each of three different phases. The data is currently stored in 4 separate tables:

• tblIndividuals
• tblPhase1Data
• tblPhase2Data
• tblPhase3Data

The variable being collected may be collected once, many times, or not at all during each of these three phases for each individual. I am trying to find the minimum value of this variable across all three phases for each individual. However, I am having a problem where, if any of the three phases has no data, that individual is being omitted from the final report. Here is what is happening (pretend these are the data in the three separate "Phase" tables listed above):

tblPhase1Data:			tblPhase2Data:			tblPhase3Data:
IndividualID	VarValuePh1		IndividualID	VarValuePh2		IndividualID	VarValuePh3
1	4		2	11		1	10
1	7		2	16		1	19
2	10		3	5		2	7
2	15		3	9		2	10
2	9		3	14		2	13
3	8

What I would like the query to return is what I have listed below on the left. However, what I am actually getting back is listed below on the right:

What I Want:			What I Get:
IndividualID	MinVarValue		IndividualID	MinVarValue
1	4		2	7
2	7
3	5

As you can see, the query is omitting the IndividualIDs where data is missing from any of the three "Phase" tables. As I have it, I have a query set up which has "IndividualID" as Group By and "VarValuePh1", "VarValuePh2" and "VarValuePh3" as Min; this is the query that is omitting IndividualIDs as suggested above. I then have a second query with a nested IIf statement which returns the lowest of the three values (which is working properly for the data that is included). Is there a way that I can force Access to include all IndividualIDs to show up in this query to show the data how I would like it?

Thank you in advance for any help!
cardgage

[ranman256]

the data should have been stored in 1 table:

phase	IndividualID	VarValuePh1
phase1	1	4
phase1	1	7
phase1	2	10
phase1	2	15
phase1	2	9
phase1	3	8
phase2	2	11
phase2	2	16
phase2	3	5
phase2	3	9
phase2	3	14
phase3	1	10
phase3	1	19
phase3	2	7
phase3	2	10
phase3	2	13

but now you need to make a UNION query with 3 queries in it, qnAllData

select * from tPhase1
union
select * from tPhase2
union
select * from tPhase3

then make a query and get MIN from that...
select [individ],MIN(minVar) from qnAllData

[cardgage]

Thank you ranman, this worked like a charm!