Problem to build a query in order to exclude some records

[gstylianou]

Hello to all friends of the forum,




I have a difficult problem (because of my own capabilities).


I have a table (tblFoods) which includes a number of food with their analyzes. I have a second table (tblExclusiveFoods) which is connected with a third table (tblCustomers) with one-to-many relation. Namely, o tblCustomers is the "One" and tblExclusiveFoods is the "Many".


The problem is the following:


Into table tblExclusiveFoods there is a field that acts like a ComboBox and gets data from the tblFoods such as drop-down-menu. So, using the combo box i can select the foodID from tblFoods and then showing into exFood field which is a part of the tblExclusiveFoods fields.


What i need is:


When i collect the foodID's from the tblFoods, i would like to to build a query which will exclude these foods from the tblFoods.


I would like to thank you in advanced


Sincerely,

[June7]

Do you really mean to exclude foods from the combobox RowSource?

The combobox RowSource must be a query that joins tblFoods with tblExclusiveFoods with a join type of "Include all records from tblFoods ...". Apply filter criteria under exFood field from tblExclusiveFoods: Is Null

Then need code to requery the combobox after each record is committed to table.

[gstylianou]

Good evening June7,

Maybe i wasn't clear. The issue is that

• I have a table (tblCustomers) which is associated with the tables "tblDiets" and the table "tblExclusiveFoods".
• Moreover, i have the "tblFoods" which is using as a "main foods store" for adding into a diet programs and is assosiated with the table "tblDiets" (tblDiets use the tblFoods in order to create a diet programs)
• Then, i have the related table named "tblExclusiveFoods" which is using as "additional customer information" which you can put notes on the foods that doesn't like to the customer. (using the tblFoods)

What i'm trying to do is:

In the form that I have made and is associated with the table "tbl Exclusive Foods" i'm getting data using the comboBox in order to select foods from "tblFoods" which is embedded in the query Row Source of the combo box. (giving FoodID and FoodName). So, when add any foods using the combo box and then when you try to create a diet program for the existing customer through the "frmdietplan", the selected foods i wanted to be hidden in the tblFoods (because are not in the customer's favorites)

I wish now to be more clear and sorry for my English..

[June7]

If I understand correctly - food that is not in tblExclusiveFoods for the customer should not be listed it in the combobox RowSource.

Still use the query I suggested as combobox RowSource. However, the query probably needs more filter criteria. I presume there is a field in tblExclusiveFoods for customer ID? Use filter criteria under that field that references the customer ID of current record on form.

Post your attempted query for analysis. Or provide db - follow instructions at bottom of my post.

[gstylianou]

Exactly June7.!!

Any foods which are included in the "tblExclusiveFoods" should not be listed using the combobox into "frmDiet" so that they can not be selected

[June7]

Did you try the suggested query with filter criteria?

[gstylianou]

Hello June7,

Yes, i tried your suggestion and thanks god....working.! You save me man another one time!

But, another one thing is:

In a design view of a query, which parameter i must use in order to give priority (going on top) on some records which are listed as "my favorite". (i refer to the relevant issue)

Thanks again

[June7]

Need a field with value that assigns a priority (favorite, acceptable) or a number. Then order by that field.

[gstylianou]

Where you mean June7 to put a new field? Into tblFavoriteFoods or somewhere else?

[June7]

I think in tblFavoriteFoods.

[gstylianou]

Good morning June7,

First all i would like to thank you for the support...really you save me another one time..

I have done some changes with the query and also i created a new field named "priority" and working fine...

Thank you very much another one time..