Combine Records

[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

 

[KARL DEWEY]

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;

 

[Duane Hookom]

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"?

 

[vanderghast]

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

 

[Brian T]

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.

 

[Duane Hookom]

Your desired output doesn't match your specification of a separate row when
there is a break in location.