Combine Records

  • Thread starter Thread starter Brian T
  • Start date Start date
B

Brian T

I have a table that shows periods of service for personnel. Personnel may
have several entries that may or may not have a break in service.

Example
Personnel Start Date End Date
--------------------------------------------
Doe, John 1/1/2007 1/1/2008
Doe, John 1/1/2008 Present

I would like to combine these into a single row.

Thanks
 
Try this --
SELECT Personnel, Min(CVDate([Start Date])) AS [Service Start], IFF([End
Date] = "Present", "Present", Max(CVDate([End Date]))) AS [Service End]
FROM YourTable
GROUP BY Personnel;
 
And... that single row should look like what?

What if there is another record with a break in service?

What are your specs other than "combine these into a single row"?
 
I am a little bit in a hurry, but I would make a UNION ALL over the two
dates field, make that field u_date, same personnel ;then make another query
to GROUP over the personnel, and u_date, and keeping only those groups
having COUNT =1. In your example, that would leave John Doe 1/1/2007 and
John Doe Present. The number of records should be a multiple of 2, for each
personnel. Ranking these dates, by personnel, would tell you which date is
an effective start of sequence (rank = 1, 3, 5 ... ) and which ones are end
of continuous sequence (rank=2, 4, 6, .... )


Hoping it makes sense,
Vanderghast, Access MVP
 
The table/query looks like this:

Personnel Location Start Date End Date
-----------------------------------------------------
Doe, John VT 1/1/2004 1/1/2005
Doe, John VT 6/1/2005 1/1/2007
Doe, John NY 1/1/2007 Present

Personnel Location Start Date End Date
-----------------------------------------------------
Doe, John VT 1/1/2004 1/1/2005
Doe, John VT,NY 6/1/2005 Present

If there is a break in service by either location or date, I would like them
in separate row.
 
Your desired output doesn't match your specification of a separate row when
there is a break in location.
 
Back
Top