Hi,

I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)

if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.

I tried "group by" but it gave me an error. I think I'm doing it wrong.

Please Help!

CS.

If you are going to use this in a report, then you can let the report control's Hide Duplicates property take of repetitions of the same value, provided the data is in employee name order. Or you could develop a group by report where the EmployeeName is in a group header.

The query would still fetch multiple records for each employee if he had multiple skills, but the report would not display the name more than once.

If you have two tables, tblEmployee and tblStrengths then this should not be a problem. Do you have 2 tables?

Jack

I think some clarification is needed here. If an employee has multiple strengths, you are going to get the employee name listed fror each strength when using a QUERY. There is no way around that.if you want to suppress the name for multiple strieghts, that would be done in a report by using grouping.

When you say do you mean that you want ONE row of data for each employee, with his/her strengths strung together as a separate string in a subsequent result field? As in:
If yes, see the 02/19/06 05:06 PM item at this post. It describes a user-written recursive dlookup function (I called it RLOOKUP so that I could remember its name!) that may solve your problem.

And if you want your string of strengths in a particular order, be sure to use the order by parm of the function.

Note that your table structure needs to be correct for this process to work...

???

Jinky

Ok here is the situation.

I have a Search form, in which the user will choose an AreaOfStrength (for example English).

and I have a SearchResult Form, in which the result of the search will appear. (it contains only Employee Names who have the AreaOfStrength I choose in the SearchForm, the AreaOfStrength field is not shown in the form)
Behind the SearchResult form, there is a query (SearchQuery).

in the query EmployeeTable and SrengthTable are linked.
The query will take the AreaOfStrength typed in Search form and will return the employee names who have this AreaOfStrength.

if an employee (for example Mark)who has English, hasanother AreaOfStrength, his name will appear twice in the SearchQuery result.

What I want is in the SearchResult form, the name of Mark appear only once. I don't care if he has 2 AreaOfStrength, I just want to know how this AreaOfStrength.

your help is very appreciated!!

CS.

What you are doing should work, it seems to me. If you are filling in the Criteria: cell correctly, then only those names who have English, say, should appear. You should not be getting anything but those names. It is possible that the fact that Mark has English is in the table twice. I would also double-check that the tables are joined correctly.

Try this:

Open the query in Design View, double-click the gray area to the right of the Field List boxes, the query properties should appear.

The one you want is Unique Records. Set it to Yes.

This will result in a recordset that cannot be updated, but I think it will take care of your problem.

Edited by: fkegley on Thu Mar 2 11:23:59 EST 2006.

That solved the problem in the case of me searching on AreaOFStrength.

But in case I was searching by name, i.e. I typed in the name Mark in the SearchForm to get all the employees whose names are Mark. and Mark has English and Math in his AreaOfStrength. I would be getting 2 records for the same guy, each one with a different AreaOfStrength

Help!