Duplicates query problem

[Parasyke]

I have a complicated query that is giving me duplicate records for some
of the returned records. Underwriter name is what I don't want to
duplicate. Any clues? Thanks for any help!

SELECT qryUnderwriters.UWID, qryUnderwriters.UWName,
qryUnderwriters.JobTitle, qryUnderwriters.BUName,
qryUnderwriters.EmployHireDate, qryUnderwriters.ApptStartDate,
qryUnderwriters.TermDate, qryUnderwriters.EmployTypeCode,
qryUnderwriters.Active, qryDL_WatchFinal_2005.TotalLoans AS DL_Loans,
qryDL_WatchFinal_2005.TotalLoss AS DL_Loss, qryDL_WatchFinal_2005.Watch
AS DL_Watch, qryDL_WatchFinal_2005.Refer AS DL_Refer,
qryLR_WatchFinal_2005.TotalLoans AS LR_Loans,
qryLR_WatchFinal_2005.TotalPenalty AS LR_Penalty,
qryLR_WatchFinal_2005.PercCorrect AS LR_Correct,
qryLR_WatchFinal_2005.LR_Watch, qryLR_WatchFinal_2005.LR_Refer,
CalcFileReqs([CSAudits_2005],[CS2005_Perc],[CSAudits_12MoCum],[CS12Mo_Perc],[CS_SigFinds],[DLPriorLosses],[DLNewLosses])
AS AuditLevel, qry2005_FileReqInfo.CSAudits_2004,
qry2005_FileReqInfo.CS2004_Perc, qry2005_FileReqInfo.CSAudits_2005,
qry2005_FileReqInfo.CS2005_Perc, qry2005_FileReqInfo.CSAudits_12MoCum,
qry2005_FileReqInfo.CS12Mo_Perc, qry2005_FileReqInfo.CS_SigFinds,
qry2005_FileReqInfo.DLPriorLosses, qry2005_FileReqInfo.DLNewLosses
FROM ((qryUnderwriters LEFT JOIN qry2005_FileReqInfo ON
qryUnderwriters.UWID = qry2005_FileReqInfo.UWID) LEFT JOIN
qryDL_WatchFinal_2005 ON qryUnderwriters.UWID =
qryDL_WatchFinal_2005.UWID) LEFT JOIN qryLR_WatchFinal_2005 ON
qryUnderwriters.UWID = qryLR_WatchFinal_2005.NewUWID
WHERE (((qryDL_WatchFinal_2005.Watch)=True)) OR
(((qryDL_WatchFinal_2005.Refer)=True)) OR
(((qryLR_WatchFinal_2005.LR_Watch)=True)) OR
(((qryLR_WatchFinal_2005.LR_Refer)=True));

[Rich P]

If your dataset contains records that have groups of underwriters
(multiple records with the same underwriter) and you only want to
retrieve one record from each group of underwriters you can try a query
like this one:

SELECT * FROM sourceTable t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM sourceTable t2 WHERE t2.RecordId =
t1.RecordId) t1 WHERE t1.ID = t1.ID)

In this example, the RecordID field would be a field that separates each
Underwriter. In your case you could use the UnderwriteName's field as
your RecordID field. The "ID" field is the unique key field in the
table. The "ID" field uniquely identifies each row in the sourceTable.

the sourceTable can also be a query (queries are tables).

What I am doing here is aliasing the table name of sourceTable with the
aliases t2 and t1. Basically I am doing self joins here. This query
assumes you have a unique key field in the table.

If you don't have a unique key but you can uniquely identify a row based
on a combination of fields that would be non repeating you can modify
the above query as follows:

SELECT *
FROM sourceTable t1 WHERE EXISTS
(SELECT * FROM
(SELECT TOP 1 * FROM sourceTable t2
WHERE t2.Underwriter = t1.Underwriter) t3
WHERE t3.fld1 = t1.fld1
AND t3.fld2 = t1.fld2
AND t3.fld3 = t1.fld3
AND t3.fld4 = t1.fld4
)

Here I am separating each group of underwriters and using 4 fields to
uniquely identify each row in each group of underwriters (I am making
this up of course). You could get away with 2 fields if you know you
could uniquely identify each row in each group of underwriters with just
2 fields.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Parasyke]

I see the SELECT TOP logic, kinda, but in my query how can I set an
ALIAS for the specific query (or table) and in this case I'm assuming I
need to produce two different ALIASES for my origination query?
Rich P wrote:

If your dataset contains records that have groups of underwriters
(multiple records with the same underwriter) and you only want to
retrieve one record from each group of underwriters you can try a query
like this one:

SELECT * FROM sourceTable t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM sourceTable t2 WHERE t2.RecordId =
t1.RecordId) t1 WHERE t1.ID = t1.ID)

In this example, the RecordID field would be a field that separates each
Underwriter. In your case you could use the UnderwriteName's field as
your RecordID field. The "ID" field is the unique key field in the
table. The "ID" field uniquely identifies each row in the sourceTable.

the sourceTable can also be a query (queries are tables).

What I am doing here is aliasing the table name of sourceTable with the
aliases t2 and t1. Basically I am doing self joins here. This query
assumes you have a unique key field in the table.

If you don't have a unique key but you can uniquely identify a row based
on a combination of fields that would be non repeating you can modify
the above query as follows:

SELECT *
FROM sourceTable t1 WHERE EXISTS
(SELECT * FROM
(SELECT TOP 1 * FROM sourceTable t2
WHERE t2.Underwriter = t1.Underwriter) t3
WHERE t3.fld1 = t1.fld1
AND t3.fld2 = t1.fld2
AND t3.fld3 = t1.fld3
AND t3.fld4 = t1.fld4
)

Here I am separating each group of underwriters and using 4 fields to
uniquely identify each row in each group of underwriters (I am making
this up of course). You could get away with 2 fields if you know you
could uniquely identify each row in each group of underwriters with just
2 fields.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

If you have a query that contains all of your query code you can select
that into a table that would serve as a temp table:

select * into tempTbl from yourFinalQuery

Now you can query tempTbl using a group by on your underwriter field to
see all the groups of underwriters. Pick one underwriter for testing.

Select * from temptbl where underwriter = 'Harry Smith'

Say you have 10 different underwriters and each underwriter has 10 rows
of data per underwriter (the duplicate rows). The goal is to retrieve
just one row per underwriter. You can use the Top operator for this

Select Top 1 * From tempTbl where Underwriter = 'Harry Smith'

This is just to show you what it is that my query is doing for you.

Now you can adapt my query trick - what this does is the same thing as
the line of code above for 'Harry Smith' except for all the underwriters
in one shot:

SELECT *
FROM tempTbl t1 WHERE EXISTS
(SELECT * FROM
(SELECT TOP 1 * FROM tempTbl t2
WHERE t2.Underwriter = t1.Underwriter) t3
WHERE t3.fld1 = t1.fld1
AND t3.fld2 = t1.fld2
AND t3.fld3 = t1.fld3
AND t3.fld4 = t1.fld4
)
Note: you don't have to use 4 fields in the last where clause. Try
using only 2 fields

SELECT *
FROM tempTbl t1 WHERE EXISTS
(SELECT * FROM
(SELECT TOP 1 * FROM tempTbl t2
WHERE t2.Underwriter = t1.Underwriter) t3
WHERE t3.fld1 = t1.fld1
AND t3.fld2 = t1.fld2
)
You might even experiment with leaving the last where clause out
altogether (if all 10 rows are exact rows)

SELECT *
FROM tempTbl t1 WHERE EXISTS
(SELECT * FROM
(SELECT TOP 1 * FROM tempTbl t2
WHERE t2.Underwriter = t1.Underwriter) t3)
But don't forget that last parenthesis. It might not work without the
last where clause. You will have to experiment.

Rich

*** Sent via Developersdex http://www.developersdex.com ***