Duplicate Fields..??

[bluenose76]

Hi,

I have a main table that holds names and qualifications.

multiple qualifications can equal the same trade so i have created a query that shows me all of the names that hold the one trade.

my problem is, if one name has three qualifications then it will show three rows with the same name and the three qualifications but all i actually want is to see the list of names.

I have linked a report and told it to hide duplicates on tne name field, but all this is doing is leaving a large gap between names becaue of the multiple qualifications.

can anyone help me with this?

All i want as the end result is a list of names.

Thank you in advance for your Help

Bev

 

[Guus2005]

if you leave out the fields you're not interested in, the group by clause works on the remaining fields giving you the information you want.

HTH

 

[bluenose76]

if you leave out the fields you're not interested in, the group by clause works on the remaining fields giving you the information you want.

HTH

Thank you for your reply, however I am lost with your advice.

I have simplified things by createing a query based upon a querywith only three fields:

Employee number
First name
Last name


Now even though I am not querying the other data, I am still receiving many instanced of the same name (this I know is because that there are more than one qualification associated with the name in the original query)

I have tried unsuccessfully to use the “group by clause” as your recommended but I cant figure out how it works,

Can you help please?

 

[neileg]

You need a select distinct query, not an aggregate query. Take out the grouping, go into query properties and set Unique Values to yes.

 

[Guus2005]

It doesn't matter if you use distinct or group by. Distinct queries are easier to write but it's more work in the QBE. The result will be the same. Execution time will be the same.

The fields you describe should give you a unique result in both cases.

SELECT DISTINCT [Employee number], [First name], [Last name] FROM TABLE1

or

SELECT [Employee number], [First name], [Last name] FROM TABLE1 GROUP BY [Employee number], [First name], [Last name]

so there must be something else you haven't mentioned yet.
Perhaps if you show us the query you are trying to execute?

NOTE: Don't use fieldnames with spaces.

HTH