Combining Multiple Records in Queries

[Tony A.]

I have a table with employee names and hours worked on a project. However
during the course of the year, many employees have changed their rank (rank
is one of the fields), so when I run a query to show employee name and total
hours worked on a project, it is showing multiple records for an employee
name if they have had multiple ranks. For example, if John Smith was manager
and worked 100 hours on a project and was promoted to Senior Manager during
the same year and worked 175 hours on this same project, is there a way to
write a query that will provide me with John Smith as a Senior Manager (due
to new rank) with a total of 275 hours as one record for this project? Right
now, my query is bringing me two separate records for John Smith. One as a
manager with 100 hours and another record as a senior manager with 175 hours.
Basically, the overall query is looking for those who have worked a total of
200 hours or more, and John Smith is not being counted due to him being on
two separate records, each with less than 200 hours worked.

 

[Duane Hookom]

I would start by creating a table that describes/ranks the positions. You
could then add this to your record source and group by the position with the
highest position rank.