Removing a duplicate

[hitchhiker via AccessMonster.com]

I have created a Query that grabs its data from a table and another query.
It looks like this....

SELECT POSHIST.STAFFID, qStaffData.FullName, POSHIST.PPOSITION, POSHIST.PHOME,
POSHIST.CURRENT
FROM POSHIST INNER JOIN qStaffData ON POSHIST.STAFFID = qStaffData.STAFFID
WHERE (((POSHIST.PHOME)<>0) AND ((POSHIST.CURRENT)=Yes));

My current issue is that the records show up multiple times if the person
associated with that ID has more than one position within the company or they
work at more than one location since that would be considered another
position. An example of the output looks something like this....

StaffID Fullname Pposition Phome Current
1 Smith, Bob AHC 5 -1
2 Brown, Sally AHC 6 -1
3 Jackson, Ted HC 5 -1
3 Jackson, Ted HC 6 -1
(different Phome but same StaffID, Fullname, and Poistion)
4 Anderson, Fred AHC 7 -1

What I would like to do is set the StaffID field to be unique within the
query so that Ted Jackson(3) would only show up once even though he is
employed at 2 locations. If this can not be achieved in the query then can
it be accomplished in a report?

Any help would be most appreciated.

 

[Jeff Boyce]

As long as your new query returns unique information from more than one
source, you don't have a way (an easy way) to return only unique persons.
In your example, as long as you include the Phone information, Ted Jackson
shows twice because Ted Jackson has two Phone numbers.

If you left out the data that would be repeated for other
positions/locations, you'd only get one instance each.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Which record do you want to keep or does it make any difference.

You could use an aggregate query and the First aggregate function to return
a more or less random record for each id.

SELECT POSHIST.STAFFID, First(qStaffData.FullName)
, First(POSHIST.PPOSITION), First(POSHIST.PHOME),
First(POSHIST.CURRENT)
FROM POSHIST INNER JOIN qStaffData ON POSHIST.STAFFID = qStaffData.STAFFID
WHERE (((POSHIST.PHOME)<>0) AND ((POSHIST.CURRENT)=Yes));
GROUP BY StaffId

If you have some date or other item that tells you which record to keep then
you could use a subquery or a nested query to get the results you want and
identify a specific record to show.

 

[hitchhiker via AccessMonster.com]

Which record do you want to keep or does it make any difference.

You could use an aggregate query and the First aggregate function to return
a more or less random record for each id.

SELECT POSHIST.STAFFID, First(qStaffData.FullName)
, First(POSHIST.PPOSITION), First(POSHIST.PHOME),
First(POSHIST.CURRENT)
FROM POSHIST INNER JOIN qStaffData ON POSHIST.STAFFID = qStaffData.STAFFID
WHERE (((POSHIST.PHOME)<>0) AND ((POSHIST.CURRENT)=Yes));
GROUP BY StaffId

If you have some date or other item that tells you which record to keep then
you could use a subquery or a nested query to get the results you want and
identify a specific record to show.

Either record will do the trick, I just need to narrow it down to one per
StaffID.

Thanks for the advice ( Both of you! ). I am gonna dig around the data some
more and see if I can't find a different way around it.

I am really amazed that this wouldn't be easier.....