How to best implement my search FORM

[MathieuSDD]

Hi All,

I basically want to use my database of consultants/employees so that I can launch a quick search by country of experience, services provided as well as expertise. I have attached my database, and what I'm trying to achieve should be apparent from the two forms. I think the next logical step would be to create a list box that would show the name, and contact details of the consultants who fits the search I have selected. Can anybody help me out here?

Thanks alot.

Regards,

Mat

[ChipR]

Do you have a specific question?

[MathieuSDD]

Hi,

Yes. Please open 'FORM' or 'FORM1'. I think i have successfully implemented threre drop-down menus (expertise, country, services). From this form, I want to be able to select any work within the three drop-down boxes, and click on the 'refresh list' button, and have an additional box showing the results underneath. My question to you is: how do I do that? I don't know how to implement this box, and how to tell the computer to operationalise this search.

I hope this is now a bit clearer.

Many thanks for the help!

Mat

[ChipR]

Sorry I can't download your database here. It seems like a subform would serve you better than a list box for this function. You could use code to filter the subform records, or you could use the Master/Child Link fields.

[ajalwaysus]

Is this Access 2007?

-AJ

[NeoPa]

Mat. You need to post your question in the forum rather than simply posting a database and expecting people to download it then work out your question for you.

How many experienced forum workers do you expect will be incautious enough to download a database from someone they don't know? Especially a member with so little history?

[MathieuSDD]

Hi All...

I posted the database as I thought it would be easier to understand. I apologise if this is generally not done.

Regarding the first question: yes, this is access 2007, but I could save it as access 2003 if necessary.

For the rest, I'll rephrase: I have a form in which I have implemented different search options through three different drop-down menus (and in the future MORE). I want to be able to select elements within these three drop-down menus (or just one element in the three) and then click a button which would launch the search and provide me with the results I am looking for (i.e names, contact details etc). My understanding is that I need to create this through forms, which I have. My question is: can anybody tell me how I create a result box and tell the computer how to operationalise this search? Is the solution, as ChipR suggests, to use subforms instead of a result box? In this particular regard, ChipR, can you tell me more about the codes you mentioned.

I hope this helps. Thanks again for the help.

[mshmyob]

Hello MathieuSDD,

The first thing you should do is read the article on normalization and fix up your design. You have the right idea but I don't understand why you duplicate all the data in your tblExpertise. You do not need to save the fields Expertise Type and Service since those descriptive values are in the tables that you are linked to. You only need the foreign keys.

Could you also explain the relationship between Country and Consultant. I notice that country is linked to the Expertise table and not the consultant. Are you trying to say that a consultant can have experise in only certain countries for some reason?

Having said that I will make some minor changes and see what I can come up with for you.

cheers,

@MathieuSDD

[mshmyob]

Actually now that I look at it some more I also need to know the business rules between service and consultant.

Please give me all your business rules for all tables since it actually needs quite a bit of fix up.

cheers,

[MathieuSDD]

Hi.

Thanks alor for the help! Ok, so the idea is that we have a network of consultants with varied profiles. In order to assign jobs, I need to know which consultant has the right mix of qualities. By that, I mean that a consultant may have different country experience (potentially unlimited), has given expertise, and provides different services (hence tbl Country, tbl Expertise and tbl Services). So, what I want to do for now is be able to create a search that includes country X and Y and potentially Z as well as Expertise a, b, e, g and Services 1, 3, 4). I want to be able to tell quickly who is the consultant that matches all those requirements. In other words.

A consultant can have
- no/one/multiple country experience
- no/one/multiple expertise (the complete list is found in tblExpertisetype)
- no/one/multiple services (the complete list is found in tblServicestype)

So obviously, this also implies that I have previously INPUT all the details for consultant X. As in, I will have written down in a table (i presume) which skills/qualities the consultant has.

Does that help in any way?

Thanks VERY VERY much for the help!

[mshmyob]

Yep that helps.

Please clarify the following example:

Consultant has experience in country 1 and country 2
Consultant has expertise in "ABC", "DEF", "XYZ"

Does this mean that automatically the consultant has experience in both countries for all 3 fields of expertise or is each expertise for the consultant limited to certain countries?

cheers,


@MathieuSDD

[MathieuSDD]

Hi

In general, I would say that the three fields are quire independent. That is, it may very well be that consultant A has experience working in Venezuela providing service B, though at this stage I am not looking to go into that level of depth. Rather I want to be able to select from the list all consultants who satisfy the following criteria:

- Eventually ONLY country X
- ONLY expertise X
- ONLY expertise Y
- Country X, Y, Z .... WITHOUT any expertise or SERVICES
- Country X, Y, Z AND expertise 1,2,3,4 WITHOUT any SERVICES
- Country A, G, T AND expertise 1,6,7 AND service a, f, c

etc... so the there's a mix of requirements really, and I want to be able to mix search requirements quite easily.

To give you a more concrete answer to your question, the consultant DOES NOT automatically have experience in both (or whatever number of) countries for all 3 fields.

I hope that answers...

Thanks again and best regards

[NeoPa]

Mat,

You may find Example Filtering on a Form a useful thing to check up on. It talks mainly of successively more refined criteria, rather than your more wide-ranging option, but it could give some of the basics about how to approach the job as a whole.

The filter string you create will need to use more ORs than the ANDs used in the example, but I expect you have that logic already well enough understood. Have a look and see if it's some help.

[mshmyob]

I will look at it this weekend. I will be out today.

cheers,

@MathieuSDD

[mshmyob]

Could you explain exactly what the services table is for? Is there some kind of relation between service and expertise? Please explain in detail.

Also what is the difference between the Service table and Service Type table?

What are they for?

Could you please give me some business rules for all your tables please?

The reason you are having so much problems is because nothing is related. You just have a bunch of tables, you have lots of redundancy and you have no normalization.

Please read the article on normalization in my previous post and get back to me to answer my question.

The filtering like you want I understand and it is easily implemented but you need to get the design right to start with.


So far I have:

Each consultant may have many expertise.
Each expertise can be for may consultants.
Each consultant expertise is for a specific country (based on your last post)
Each consultant may have many expertise for many countries
Each country may have many consultants

Where does the Service and Service Type tables come into play?

cheers,