Record Category, Filtering multiple choice

Your second method of creating a separate "Category" table was almost the way to go.  You just needed one more table that is needed to link the Task table to the Category table.  Say for instance that you had a unique field in the Task table called TaskID and a unique field in the Category table called CatID.  Your 3rd table "tbl_TaskToCategory" should have 2 columns, a TaskID and a CategoryID.  This table, along with the other 2 tables, is what you'd use in your query or RecordSource.

<aside>
No points wanted please
;-)

What is describes above is a classic "Many-To-Many" relationship...

The many to many table is needed because one Task can have many categories, and also one Category can have many tasks.

If you want to dig deeper you can research more online.
For example: setting both foreign keys as primary keys so that you cannot have the same combination of task-categorey more than once.

JeffCoachman

Well, thanks for above both. A bit confused with many to many, as did not do it before. Before giving the glorious victory to Iroq..:
1. "Task" table- existing (1000s of records)
2. "Category" table - done. (31 records=nos of categories); 3 fields: id_task, id_category, tick box marker for further filtering.

3."Task-Category" table

Sorry for interruption: s-pen in the train has its disadvantages. Anyway:
3."Task-category" table. As understood this table must be related to tasks as many-to-many. (There is a bit of confusion here. Task is one to many to categories. I,m assigning categories via combo box field in subform to the tasks form. It means for each task there can be up to 31 record.
4. Next step is query, which will return up to 32 records for each task.
5. form to filter query to be more user friendly (based on category table= tick box + title.
6.limiting the query based on tick boxes choice in item 5?(tick box true).
I have an impression that query will return back up to 31 results instead of 1 (or instead of and).
Where is mistake?
Rgds,
Of confusion here.).

Your Task-Category table needs to have the fields id_task and id_category.  These are used as foreign keys.  You don't need the id_task field in the Category table.  As for the tick box in that table, that depends on how you are implementing this.  For instance, if I use a multi-select listbox in the form to show the Categories, there is no need for the tickbox; however, a tickbox can be useful in a subform of Categories.

It's hard to be specific without seeing how you're planning to do this.  If you have a screenshot of your form, it may help.

Looks like we cross-posted.  I just now saw the rest of your post.  It would really be better if you can upload a copy of your database (remove any private info first).  I'm retiring for the night but the other experts here would be able to help.

Thanks. Will create "mock-up" in the evening.

Gd evening,

Here we are. I,ve created trial db, which is more or less simulating the structure I have in the main db. All is starting from form frm01sbf01_TasksPersonalList. Categories assigned on the Individual page(command button within each record- another form).
The target is to filter frm06(based on qry02) with the pop-up form frm05. And to get the task once it is corresponding multiple categories instead of task returned N times if it corresp to N categories.

Can provide any additional info on request

Thanks in advance for consideration and best regards

Did I attached the db?
TaskCategorydb.mdb

How is form6 supposed to be sorted?  Since you're filtering by categories, will they be grouped by Category first?  So you prefer this to be a form rather than a report?  

I don't understand why you have a subform (frm03sbf02_TaskCategory) that holds another subform (sbf02frm03_TaskCategory).  Wouldn't it make more sense if the subform to select the categories be the same as frm05_Category (the one with the checkboxes)?

Also, you don't need ID_TaskCategory in your table tbl4_TaskCategory and the primary key should be Task_ID and Category_ID together.  That way you avoid duplicates in your table.  Right now for instance, you have Task 7 with Category 2 is on there twice.  

Here's what your relationship should look like for Many-To-Many:
As an aside, you have a really confusing way of naming your forms.  I understand that you're wanting to know which form a subform is tied to but if you weigh the benefit of that against the benefit of dealing with less confusing objects, I think it's not worth it.  Also, what happens when you have subforms that are shared with more than one form (something that you could actually do here with frm05).  Numbering the forms doesn't make too much sense either (or did you just do that for my benefit?).  Some names you could consider are frmCategory or sfrm_Task_Category or fsub_Task_Category.

Thanks for support. Will take a look IN few hours.
The main idea behind all this category staff:
-There is a huge task index form which can be filtered in many ways (by date; fleet, person, equipment code etc).
- With expected further increase of the number of tasks (more users joining the application) existing criteria are not enough+ demand of more wide evaluation of the activities= type of events= categories
- Expected result: categories are assigned by the users on the task individual page.
- Index FORM is capable to be filtered by applicable categories (average number of categories assigned for one case is 2-3; almost never-1);
- displaying of the categories on t he index form is absolutely not necessary. Just the task matching multiple criteria;

Currently we are doing it by filtering the text field, where the categories are typed in manually= a lot of mistakes.

Apologies for a long text. Reverting with rest of comments and results later 2day

Gd day,
Basically all clear, except one point. If the task is associated with three categories, query is returning the this task three times instead of one as wanted.
May be I'm not smart enough but I don't understand.

Thanks in advance

Something like this perhaps:
In this version I reused your category form.  It as a subform for your 2nd form as well.
Also, the changes you make to the categories are only saved if you click on the Save button (I wasn't sure what the original intent of this button is for).
TaskCategorydb-rev.mdb

Gd evening,
The way of assigning of the categories is great. I think I can even handle later a kind of event to avoid the command button launching the update query.
But the result is still not reached: if the task is associated with 3 categories, the query is still returning 3 records instead of one matching  all categories.

I'm also trying now the approach based on the fact that the number of categories assigned to one task is never more than 5. Means , reserving 5 fields in task table => assigning the categories by combo box, for example on the individual page=> filtering the query based on the category form selection (smth like And OR). Also without success up to now.

regards,

, the query is still returning 3 records instead of one matching  all categories.

Which query are you referring to?

Gd day,

I am referring to the query qry2 and form frm06(based on query2). Means if the task is assigned with 3 categories (for example), qry 2 is returning 3 lines reflecting same case for each assigned category => frm 06 is reflecting same.
Target to have task reflected on time even if it is corresponding to all categories.

Thanks in advance

This all depends on your desired output form or report.  In your case, you have a form that shows the records being returned and it includes the Tasks and the Categories for each one.  This works well for a report where you want to see what categories each task is assigned to.  Did you want a form that only shows the tasks associated with the selected categories?  If so, you cannot have the categories displayed in the query results. How exactly do you want the filtered form to look like and how would the user be interacting with this form?

Ron

I dont need to see categories displayed in the query results. I need to see the task name on the form. And this task must be displayed only once despite the number of assigned categories.(similar staff is working perfectly in Ourtlook. Fortunately, not everyone is a sales-man. For Technical department ToDo in Outlook is not enough).
The categories list distilled with the time is currently consisting of 31 category. As I said in above waste correspondence, currently, each task has a text field, where user is printing the category manually. Not choosing, but printing. Later, on the task index form, user is filtering records by text in the text field using the following syntax: *category1* OR * category2* etc.
If the task is assigned even with 5 categories, it is visible on the filtered form only once.
Such method works fine except two points: printing mistakes when entering categories in the text field and a lot of space the field is occupying on the screen.
As soon as the list of tasks is filtered by categories, user can continue filtering by other attributes (dates, objects, equipment code, fleet, event codes, egngineer in charge, location etc).
As soon as you have found what you need, there is a command button to jump to the individual page to read the details, print reports, edit attributes , history etc.

Reporting of the form filtered by categories is not required.
All this catgeories staff is only to make the search more friendly + a little bit of statistics.
Assigning of the Categories with the tick is preferred method (people like it here).

God bless you Irog! The thing is working exactly how we need! Rushing to repeat the structure in the real db. YOu have saved a lot of time for very busy gents.
(I did not understand what table 1 is doing. Consider as not necessary, a kind of remainings...)
Best regards and nice weekend,

Regarding the email you just sent me:
You need to set the UniqueValues property in the query's property sheet to Yes.  Or if you go in the the SQL view of the query, you can add the DISTINCT keyword after SELECT so that it will look something like this:

Yes. This is it. Fully operational now. Many thanks.