List box: combining columns from multiple tables where records not always cohesive... (1 Viewer)

[killyridols]

Hello,
I have a form with a combobox (combo0) where a sample id can be selected from the drop down menu.

In a list box I have the following code:

SELECT DIVERS_pt2.sample_id, DIVERS_pt2.[1/(maxi/summe)]
AS 1/(max species count/total count)], SHANN_pt2.proz
FROM DIVERS_pt2, SHANN_pt2
WHERE (((DIVERS_pt2.sample_id)=[forms]![NANDATSAMPLEID].
[combo0])
AND ((SHANN_pt2.sample_id)=[forms]![NANDATSAMPLEID].[combo0]));

I want to show the two types of species diversity ("1/max species count/total count", AND "proz"), however, for some sample ID's I have a value for the "1/max species/total count" but not for the "proz" one. Right now if I select a sample that does not have a Shannon Diversity value ("proz") no record shows up in my listbox, even though a value still exists for "1/max species/total count"

Does anybody have any suggestions on how I could alter my code so that a record will always show up as long as one of the two diversity values exist??

Thank you very much!

 

[WayneRyan]

K,

The easiest way is to use a Union query, BUT you have to have both
sides return the same number/type of data:

SELECT 'DIVERS_pt2',       <-- Note, just added to show where the data came from
       sample_id, 
       [1/(maxi/summe)]
FROM DIVERS_pt2
WHERE DIVERS_pt2.sample_id = [forms]![NANDATSAMPLEID].[combo0] UNION

SELECT 'SHANN_pt2',        <-- Note, just added to show where the data came from
       Sample_id,
       proz
FROM   SHANN_pt2
WHERE  sample_id = [forms]![NANDATSAMPLEID].[combo0];

hth,
Wayne