B
BURL ives
I have a query from a many to many relationship that pulls the
following fields:
last name
first name
title
procedure number
revision number
assigned date (date the revision of the procedure occurred)
The data looks something like:
doe, john, director, QLT1, 0, 5/5/06
doe, john, director, QLT1, 1, 7/5/06
doe, john, director, QLT1, 2, 5/5/07
doe, john, director, QLT2, 0, 5/5/06
doe, john, director, QLT3, 0, 7/5/06
doe, john, director, QLT3, 1, 5/5/07
smith, tom, tech, QLT1, 0, 5/5/06
smith, tom, tech, QLT1, 1, 7/5/06
smith, tom, tech, QLT1, 2, 5/5/07
smith, tom, tech, QLT2, 0, 5/5/06
smith, tom, tech, QLT3, 0, 7/5/06
smith, tom, tech, QLT3, 1, 5/5/07
My question is:
For a given person and for a given procedure number, how do you select
the most current assigned date? I'd like the output of the query to
look like:
doe, john, director, QLT1, 2, 5/5/07
doe, john, director, QLT2, 0, 5/5/06
doe, john, director, QLT3, 1, 5/5/07
smith, tom, tech, QLT1, 0, 5/5/06
smith, tom, tech, QLT1, 2, 5/5/07
smith, tom, tech, QLT2, 0, 5/5/06
smith, tom, tech, QLT3, 1, 5/5/07
THANK YOU IN ADVANCE
following fields:
last name
first name
title
procedure number
revision number
assigned date (date the revision of the procedure occurred)
The data looks something like:
doe, john, director, QLT1, 0, 5/5/06
doe, john, director, QLT1, 1, 7/5/06
doe, john, director, QLT1, 2, 5/5/07
doe, john, director, QLT2, 0, 5/5/06
doe, john, director, QLT3, 0, 7/5/06
doe, john, director, QLT3, 1, 5/5/07
smith, tom, tech, QLT1, 0, 5/5/06
smith, tom, tech, QLT1, 1, 7/5/06
smith, tom, tech, QLT1, 2, 5/5/07
smith, tom, tech, QLT2, 0, 5/5/06
smith, tom, tech, QLT3, 0, 7/5/06
smith, tom, tech, QLT3, 1, 5/5/07
My question is:
For a given person and for a given procedure number, how do you select
the most current assigned date? I'd like the output of the query to
look like:
doe, john, director, QLT1, 2, 5/5/07
doe, john, director, QLT2, 0, 5/5/06
doe, john, director, QLT3, 1, 5/5/07
smith, tom, tech, QLT1, 0, 5/5/06
smith, tom, tech, QLT1, 2, 5/5/07
smith, tom, tech, QLT2, 0, 5/5/06
smith, tom, tech, QLT3, 1, 5/5/07
THANK YOU IN ADVANCE