Applying filters to the mainform using the values in the subform

[haritbhasin]

Hi,



I have created a dtabase that contains information of the experts. i created 4 tables.
Experts:- contains the general information like name email id user id and all....user id is the primary key.
Created 3 tables for the expertise:-itype,solution and industry.....
itype have two level.Level1 and level2......so i concatenate the level 1 and level2 and use the ID field of the itype table as the primary key...
Solution table have 3 levels...i uses the same thing here also and ID as the primary key....
industry table have 2 levels and ID as the primary key....
rltnship table:----4 fields.....userid for expert table.....IDs from itype, solution and industry.

and have one tomany relationship...that means one expert can have more than one itpye solution or industry assigned to him.


Forms:-
expert list form:-contains the list of the experts with the information for the expert table.....
contains a field to open the expert details form....which contains a subform also where the experise are defined......


I want to apply 3 filters on the expert list form....where a user can filter according to itype solution and industry and then can get the expert list. where the experts having the expertise selected in the filters are displayed.......

[rpeare]

It sounds like you are using multi value fields, that's where your problem is. Multi value fields are a huge, huge, huge problem in terms of doing what you are trying to do. Get away from the practice of using them, they will cause you more problems and more headaches than if you abandon the practice now.

What you should have is a subtable that is a junction of the expert and their industry, and a junction subtable for the expert and the solution. so if an expert is suitable for 3 industries you would have 3 records in your subtable related using the PK of the experts table (by the way, you should not use userID as the primary key, because userID's can change, let's say a woman gets married, if you change her id to her married name all the child records will be orphaned).

[June7]

Subforms do not control main form (children do not dictate the parent), presuming the Master/Child links properties of the subform containers are set.

Why 3 tables for expertise - are these tables different in the fields used?

The filter you want to apply would require the main form RecordSource to have the fields that the criteria applies to. The 1 to many relationship of experts to each of the 3 expertise tables will make queries that apply criteria from the 3 tables awkward. You can build a query that will join the 3 expertise tables to expert table (each join type would be 'include all records from experts ...) but the result will appear to 'duplicate' data. Try it and see what happens. The main form will have multiple records for each expert.

Is UserID an autonumber field?

[haritbhasin]

Hi,

thank you for the instant reply....i am using the junction table only.....if a user have 3 different solutions....his id will appear 3 times in the junction table.but.....for example auser select 1 itype one solution and one industry his id will only be displayed only once.....but if the user will select second itype or solution or industry then his id will again be added to the junction table.....

[rpeare]

That depends on if solutions and industries are mutually exclusive. If for instance if a solution applies ONLY to a specific industry your junction table would be a combination of person, industry and solution, instead of two junction tables of person and industry and person and solution. I don't know what you're doing with your output but the number of records shouldn't be an issue if you are printing the data on a report or showing it on a form you can use forms/subforms and reports/subreports without creating multiple records in your main query.

[haritbhasin]

HI,

All the 3 expertise i.e itype,solution and industry all are independent of each other. so I want 3 combo box....one for itype one for industry and one for solution.....so if the user selects itype and solution then in the expert list page we should get the list of experts who have itype and solution as there expertise.......

[rpeare]

Then yes, you should have 3 subtables for your three areas of interest (solution, industry and type?) that are related to your personnel through the primary key of the personnel table. When you show this data on a form or report use subforms/subreports rather than trying to build it all into one query and you shouldn't ever have a problem with multiple lines for the same person.