Query with 2 list box

  • Thread starter Thread starter Ulcom
  • Start date Start date
U

Ulcom

Hi.

I have a query including 3 tables. Tables are link with one field called
"N°"

First table "Table A" has 2 fields "N°" and "Name"
Second table "Table B" has also 2 fields "N°" and "Country" Note that many
Country can be allowed for the same N°
Third table "Table C" has also 2 fields "N°" and "Category" Like for table
B, many category can be allowed for the same N°

So the query can look like this
N° Country Category
1 1 Canada Fish
2 1 Canada Meat
3 1 Peru Vegetable
4 1 France Fish
5 1 France Pasta
6 2 Canada Meat
7 2 Peru Fish

On a form I have 2 list box (multiselection)
One that include Countries
Other include Categories

What i want to do is to check items in both list boxes and obtain the result
within the query or in another query

if i check Canada and France in the Country box and Fish and Pasta in the
Category box i should obtain records 1 and 4

If I only check France in the country box i sould obtain records 4 and 5

If i check Fish in the category box i should obtain records 1, 4 and 7

Anybody can help me with this ?
thanks
 
Ulcom said:
Hi.

I have a query including 3 tables. Tables are link with one field called
"N°"

First table "Table A" has 2 fields "N°" and "Name"
Second table "Table B" has also 2 fields "N°" and "Country" Note that many
Country can be allowed for the same N°
Third table "Table C" has also 2 fields "N°" and "Category" Like for table
B, many category can be allowed for the same N°

So the query can look like this
N° Country Category
1 1 Canada Fish
2 1 Canada Meat
3 1 Peru Vegetable
4 1 France Fish
5 1 France Pasta
6 2 Canada Meat
7 2 Peru Fish

On a form I have 2 list box (multiselection)
One that include Countries
Other include Categories

What i want to do is to check items in both list boxes and obtain the result
within the query or in another query

if i check Canada and France in the Country box and Fish and Pasta in the
Category box i should obtain records 1 and 4

If I only check France in the country box i sould obtain records 4 and 5

If i check Fish in the category box i should obtain records 1, 4 and 7

Anybody can help me with this ?
thanks
Click to expand...
I've been thinking about this for a couple of days. I threw together a mdb
but the way you have your table, I can't make a query to get the results you
want.

There is no relationship between TableB and TableC. The tables have a field
in common named the same, but how does the country Canada in TableB know
whether it is related to the record Fish or Meat in TableC?

You need a union table (many to many) or a table that has a structure like:

No, Country, Category

Sorry I couldn't be more help

SteveS
 
Back
Top