sql - Selection of table values based on record -
i have 3 tables following structure:
table1: cat fields: cat0 cat1 record 1: record 2: b record 3: c record 4: d table2: catb fields: option record 1: x record 2: y table3: catd fields: option record 1: 1 record 2: 2
cat1 should lookup catb, if cat0 has b in record
cat1 should lookup catd, if cat0 has d in record
the output expected is:
table1: cat fields: cat0 cat1 record 1: null record 2: b x or y (combo box) record 3: c null record 4: d 1 or 2 (combo box)
i using lookup values in ms access.
the following code doesn't work in lookup of cat1:
select * cat inner join catb b on b.option = a.cat0 a.cat0 = "b" union select * cat inner join catd d on d.option = a.cat0 a.cat0 = "d";
how can achieved?
thanks.
i never set lookups in table, on forms.
correct union statement in query named options:
select "b" cat, option catb union select "d", option catd;
instead of having resort union, why don't have single table catb , catd options.
table: options fields: cat, option record 1: b, x record 2: b, y record 3: d, 1 record 4: d, 2
in either case, combobox can have conditional sql statement in rowsource.
select option options cat=[cat0];
if want stick union , use all-in-one statement rowsource:
select option (select "b" cat, option catb union select "d", option catd) options cat=[cat0];
and code in cat1 gotfocus event: me.cat1.requery
.
Comments
Post a Comment