I want the name to appear only once in query result

[CSCS]

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.

 

[antonyx]

im not sure if this will work but ill suggest something based on my limited knowledge..

in your query, goto sql view and where is says SELECT name, change that to SELECT DISTINCT name

 

[neileg]

SELECT DISTINCT won't work unless all the fields in each record are identical. So if your record includes the area of strength you'll still get duplicates. You can uncheck the show box in this field, or turn the query into an aggregate query (a totals query) and use FIRST instead of SUM.

 

[CSCS]

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.

 

[neileg]

1. I don't understand why you think more than one record will be returned. If an employee has English and Maths, only the record with English will be returned.

2. I have already explained how to reduce the number of records to one.

 

[Pat Hartman]

You are selecting the area of strength to be returned by the query. That is why it will not aggregate. The compination of employee + strength is what is being returned. Uncheck the show box for strength. It isn't necessary to select a column just because you have criteria for it.

Select EmployeeID, Count(*) As CountOfStrengths
From YourTable
Where Strength = "a" or Strength = "b"
Group by EmployeeID;

I included a count field but if you don't want the count, then use the following:

Select Distinct EmployeeID
From YourTable
Where Strength = "a" or Strength = "b";

 

[CSCS]

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!

 

[neileg]

If you have a form/subform set up, you will get Mark's personal details in the main form and his areas of strength in the subform.