Filtering Records in a Quary based on Duplicate fileds

  • Thread starter Thread starter Brendan
  • Start date Start date
B

Brendan

Here is my challange

Simple one table database. Two types of records can exist but only differ
by "Work Type", if so, only one type is needed to be reported on if the
following conditions match; "Region", "Dest", "HTLCode", "Contract Start
Date", and "Contract End Date"
and the "Work Type" differs by "CE" and "CO"
where the "CO" survives and the "CE" is not reported on

Help, please
 
What other fields are in the table. I might get you a method if I have more
information.
 
The table has about 15 more fields that cover other work types. We do have a
unique record number and several text fields for comments. This may help,
The CE work type is "Contract Extension", meaning agreed rates after the last
contract peroid. What happens is that the CE record is created to show we
have the next contract peroid covered and is replaced by a "CO" Contract for
the same period when the new contract is agreed too. What the underlining
question is; how many CE's do we have out there for a giventime frame (got
that quary covered) that do not have CO that cover the same. So,, if a CE
and a CO exist for the same peroid, the CO should be reflected, if a CE is
the only record for the same region, destination, hotel code, for the same
time frame, that shoudl be shown. And yes, this is the travel industry.

Thanks in advance
 
One query solution
Query One
SELECT Region, Dest, HTLCODE, [Contract Start Date], [Contract End Date],
Min([Work Type]

SELECT *
FROM [One Table] as A
WHERE A.[Work Type] =
(SELECT Min([Work Type]
FROM [One Table] as B
WHERE B.Region = A.Region AND
B.Dest = A.Dest AND
B.HTLCODE = A.HTLCODE AND
B.[Contract Start Date] = A.[Contract Start Date] AND
B.[Contract End Date] = A.[Contrract End Date])

A two query solution might be more efficient
First query saved as qOne
SELECT Region, Dest, HTLCODE
, [Contract Start Date], [Contract End Date]
, Min([Work Type]) as WType
FROM [One Table]
GROUP BY Region, Dest, HTLCODE
, [Contract Start Date], [Contract End Date]

Second query
SELECT A.*
FROM [One Table] as A INNER JOIN qOne as Q
ON A.Region = A.Region AND
A.Dest = Q.Dest AND
A.HTLCODE = Q.HTLCODE AND
A.[Contract Start Date] = Q.[Contract Start Date] AND
A.[Contract End Date] = Q.[Contrract End Date] AND
A.[Work Type] = Q.[WType]
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Whoops - left in some extraneous SQL
The two lines right after QueryOne should be deleted from my response.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
John Spencer said:
One query solution
Query One
SELECT Region, Dest, HTLCODE, [Contract Start Date], [Contract End Date],
Min([Work Type]

SELECT *
FROM [One Table] as A
WHERE A.[Work Type] =
(SELECT Min([Work Type]
FROM [One Table] as B
WHERE B.Region = A.Region AND
B.Dest = A.Dest AND
B.HTLCODE = A.HTLCODE AND
B.[Contract Start Date] = A.[Contract Start Date] AND
B.[Contract End Date] = A.[Contrract End Date])
Click to expand...
SNIP
 
Back
Top