complex combobox relationships

[polisci grad]

I have two combo boxes which mutually limit eachother's choices. The
rowsource sql is:

SELECT DISTINCT industries.description, data.industry, data.theme,
themes.description
FROM themes INNER JOIN (industries INNER JOIN data ON industries.industry =
data.industry) ON themes.theme = data.theme
WHERE (((themes.description)=[forms]![form1]![cbotheme])) OR
((([forms]![form1]![cbotheme]) Is Null));

The other is just the obverse. The data table contains separate fields with
numbers for theme and industry (eg. 1=safety, 2=trade, etc) or (1=road,
2=sea). the industries and themes tables merely explain the numbers and are
related to the data table in a one-to-many.

Now the problem is that once industry.description becomes linked, through
data, with theme.description, the distinct clause returns all industries with
distinct themes...therefore what was once a discreet list now has duplicate
values (for example, three listings for road industry since data exists for
road in the safety, trade, and labour themes).

Is there a way around this?? I would like to present only the names of the
various industries/themes in the respective combo box, but still limit the
chocies in the others. DISTINCTROW makes it even worse, displaying all
records of available data. Whats more, I eventually hope to add a third
combo box (region) that will exist in the combobox relationships...all of
which determine a listbox of available variables....perhaps i need separate
queries to base my combo queries of off, i dont know...

??????

 

[Michel Walsh]

Hi,


If there is 3 "rows", like

industry1, theme1
industry1, theme2
industry1, theme3


and you wish to see just one row, what "theme" will we pick? If it does not
matter, why picking the "theme"?

SELECT DISTINCT industries.description
FROM themes INNER JOIN ...


would then do.

If you still need at least ONE theme, then

SELECT industries.description, LAST(data.industry), LAST(data.theme),
LAST(themes.description)
FROM ....
WHERE ...
GROUP BY industries.description


could do.


Hoping it may help,
Vanderghast, Access MVP