Duplicate Query based on two duplicates

[BMaerhofer]

I need to build a duplicate query based on two different criterias. It needs
to have one record for "D1" and one for "HSIF" to be considered a duplicate.
It also is based on other fields. Please see sample below:

::::Record 1::::
area: 123456 (duplicate criteria)
location: test (duplicate criteria)
date: 10/17 (duplicate criteria)
block: D1... (If HSIF record, then duplicate)

::::Record 2::::
area: 123456 (duplicate criteria)
location: test (duplicate criteria)
date: 10/17 (duplicate criteria)
block: HSIF... (If D1 record, then duplicate)

I need it to consider this a duplicate based from area, location, date, and
block (even though that they do not match on block). To be a true duplicate I
need one record to have D1 and one to have HSIF. It seems that when I pick
the duplicate query, it will only allow for each field to match and I cannot
figureo out how to edit to have the query determine if there is one record
for each type of bock. Please help.

Thank you so much!

 

[John Spencer]

Try using a calculated field in place of the block field. And then run
the duplicates query against this instead of against the block value.

IIF([Block] in ("HSIF","D1"),"HSIF",[Block])

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[BMaerhofer]

John,
Thanks for your help, but I cannot get this to work. Still the same amount
of records. I still have the records base area, location, and date being
pulled in which they are not duplicates based on having a "D1" and "HSIF"
block. Any other suggestions?

Thanks!
--
BWM

Try using a calculated field in place of the block field. And then run
the duplicates query against this instead of against the block value.

IIF([Block] in ("HSIF","D1"),"HSIF",[Block])

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I need to build a duplicate query based on two different criterias. It needs
to have one record for "D1" and one for "HSIF" to be considered a duplicate.
It also is based on other fields. Please see sample below:

::::Record 1::::
area: 123456 (duplicate criteria)
location: test (duplicate criteria)
date: 10/17 (duplicate criteria)
block: D1... (If HSIF record, then duplicate)

::::Record 2::::
area: 123456 (duplicate criteria)
location: test (duplicate criteria)
date: 10/17 (duplicate criteria)
block: HSIF... (If D1 record, then duplicate)

I need it to consider this a duplicate based from area, location, date, and
block (even though that they do not match on block). To be a true duplicate I
need one record to have D1 and one to have HSIF. It seems that when I pick
the duplicate query, it will only allow for each field to match and I cannot
figureo out how to edit to have the query determine if there is one record
for each type of bock. Please help.

Thank you so much!

 

[John Spencer]

First, post the SQL of the query you are using. (Open query in design
view, select View: SQL from the menu. Copy and paste)

You might try modifying the IIF clause one more time -
IIF([Block] in ("HSIF","D1"),"HSIF",Null)

Or you might add criteria to the query you are using to return only
records where BLOCK is equal to HSIF or D1. Also, I suggest you type
this since I can tell if D1 is D One or D L.

I think what you are saying is you only want to see records where Block
is equal to HSIF or D1 and there are duplicates in Area, Location, and
Date. The simplest thing may be to filter the records that are returned
by filtering the results to show just those where block is HSIF or D1.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[BMaerhofer]

What I need is to find duplicates with the Area, Location, and Date... Once
it finds thoses I need it to have one D "one" record and one HSIF record to
be considered a duplicate.

See Example:
<--- (Duplicate Records to remove)
Area Location Date Code
12 115 1/1 D1 <---
12 115 1/1 HSIF <---
13 155 1/1 D1 **
13 155 1/1 ABC **

** Since Area 13 doesnt have both D1 andHSIF records then dont consider as a
duplicate.

I tried adding your coding *** IIF([Block] in ("HSIF","D1"),"HSIF",Null) ***
I get no records.

Thank you!



--
BWM

First, post the SQL of the query you are using. (Open query in design
view, select View: SQL from the menu. Copy and paste)

You might try modifying the IIF clause one more time -
IIF([Block] in ("HSIF","D1"),"HSIF",Null)

Or you might add criteria to the query you are using to return only
records where BLOCK is equal to HSIF or D1. Also, I suggest you type
this since I can tell if D1 is D One or D L.

I think what you are saying is you only want to see records where Block
is equal to HSIF or D1 and there are duplicates in Area, Location, and
Date. The simplest thing may be to filter the records that are returned
by filtering the results to show just those where block is HSIF or D1.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,
Thanks for your help, but I cannot get this to work. Still the same amount
of records. I still have the records base area, location, and date being
pulled in which they are not duplicates based on having a "D1" and "HSIF"
block. Any other suggestions?

Thanks!

 

[John Spencer]

Last time I'll ask. Please post the SQL of the query you are using.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[BMaerhofer]

Sorry about that. Here it is just as a duplicate query:


SELECT test.area, test.location, test.date, test.code
FROM test
WHERE (((test.area) In (SELECT [area] FROM [test] As Tmp GROUP BY
[area],[location],[date] HAVING Count(*)>1 And [location] =
[test].[location] And [date] = [test].[date])))
ORDER BY test.area, test.location, test.date;

Thanks!
--
BWM

Last time I'll ask. Please post the SQL of the query you are using.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

What I need is to find duplicates with the Area, Location, and Date... Once
it finds thoses I need it to have one D "one" record and one HSIF record to
be considered a duplicate.

See Example:
<--- (Duplicate Records to remove)
Area Location Date Code
12 115 1/1 D1 <---
12 115 1/1 HSIF <---
13 155 1/1 D1 **
13 155 1/1 ABC **

** Since Area 13 doesnt have both D1 andHSIF records then dont consider as a
duplicate.

I tried adding your coding *** IIF([Block] in ("HSIF","D1"),"HSIF",Null) ***
I get no records.

Thank you!

 

[John Spencer]

A slight modification to your query. The one you posted should have
returned duplicate records based on Area, Location, and Date. It should
not have returned no records, just a lot more records than you want.

Try the following:

SELECT Test.area, Test.location, Test.date, Test.code
FROM TEST
WHERE test.area In
(SELECT [area]
FROM [test] As Tmp
GROUP BY [area],[location],[date], Code
HAVING Count(*)>1 And [location] = [test].[location]
And [date] = [test].[date]
AND Code = Test.Code)
ORDER BY test.area, test.location, test.date;

Above code assumes that Test.Code is the expression
IIF([Block] in ("HSIF","D1"),"HSIF",Null)
in a query named Test

Query test would look like:

SELECT Area, Location, [Date], Block
,IIF([Block] in ("HSIF","D1"),"HSIF",Null) as Code
FROM YourTableName


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================