Trouble with Query

[kira]

I'm trying to create a query based on a single table with 7 fields. The ID field may be the only unique field in each record. The two fields queried are Name and Action which both may appear in multiple records.

First I ran the find duplicates wizard on the name field and came up with:

Id Name Action
456 Anne a
823 Anne a
322 Anne a
45 Anne a
998 Bill a
298 Bill b
235 Bill c


From this I now need to retrieve records containing unique actions for every name:

Id Name Action
823 Anne a
998 Bill a
298 Bill b
235 Bill c

It doesn't matter which id is returned as long as for each name the action isn't duplicated.

Creating subqueries and nested queries have so far only produced errors, no output, or the same output as the first query.

Any ideas would be appreciated.

Thanks

[PEB]

In fact do you need reely from the column Id in the second query? What is your need about this field?

In fact it seem that the recordset returns you the first or the last value that responds of the group But the information isn't grouped on it! It's sure! If not all records for Ann will be displayed!

Try a Group By on ID and see what will happen!
:)

[kira]

Thanks - PEB

I tried the Group By on Id and it returned the same results as first query.

To be honest I'm uncertain whether I need a second query or just need to add another statement to the first query code:

SELECT Summary.ID, Summary.EmployID, Summary.EmployName, 
Summary.ActionCode, Summary.Location, Summary.EffDate, Summary.Unit
FROM Summary
WHERE (((Summary.EmployName) In (SELECT [EmployName] FROM 
[Summary] As Tmp GROUP BY [EmployName] HAVING Count(*)>1 )))
ORDER BY Summary.EmployName;

The records are based on actions in an employee file. A second database is about to be created and the purpose of the query is to identify employees with more than one action and list one incidence of each action associated with those employees. This information will tell us whether the associated employee file needs to be left in the original db, or moved or copied to the second.

Kira

[PEB]

You've done it good! :)

In fact if you have done a Group BY directly on the first query you couldn't obtain the same results that you obtain now...

Your query seems to be excelent! :)

Have a nice day and don't be uncertain ;)

Vladimir

[kira]

Hi Vladimir,

You're right. Adding a Group By on ID to the first query did produce slightly different results. It eliminated the Id column. What I meant when I said it produced the same results is it returned all the same records and didn't solve the problem with the ActionCode column.

The first query (the code in my last post) has been fine from the start. It returns all duplicate EmployNames. What I need to figure out is for each of those duplicate names how to remove any duplicate ActionCodes. For example, if there are 3 records with EmployName 'Anne' and each has an ActionCode 'a' I need to add something to the first query or have a second query that only returns one record for 'Anne' with ActionCode 'a'.

Many thanks for taking the time to reply :)

Kira

[PEB]

Kira,

so your code may be like:

SELECT DISTINCT Summary.EmployName, 
Summary.ActionCode
FROM Summary
ORDER BY Summary.EmployName;
 

Hope that is you want :)

[PEB]

This is equal to:

SELECT DISTINCT Summary.EmployName, 
Summary.ActionCode
FROM Summary
GROUP BY Summary.EmployName, 
Summary.ActionCode
ORDER BY Summary.EmployName;
 

[kira]

Thanks Vladimir, that's the missing piece :)

Kira