Duplicate query with NULL issue

[mbowler9]

Hello,

I am trying to find system names with duplicate entries in our database with a duplicate query. It works sort of. Right now it lists every duplicate instance of some systems and only one of the instances for other systems. It appears that if the (allmid_cpu.sa_suitsid) field is blank for the record, that instance will not show up. Can someone show me how to modify the query below to include these null values? I have tried just adding the following to the design view, but it still did not pick up the Nulls
Expr1: iif(IsNull([sa_suitsid]),"",[sa_suitsid])

SELECT DISTINCTROW allmid_cpu.sys_name, allmid_cpu.dns, allmid_cpu.status, allmid_cpu.in_svc, allmid_cpu.ito_managed, allmid_cpu.vendor, allmid_cpu.sys_script_update, allmid_cpu.vantive_update, allmid_cpu.update_date
FROM allmid_cpu
WHERE (((allmid_cpu.sys_name) In (SELECT [sys_name] FROM [allmid_cpu] As Tmp GROUP BY [sys_name] HAVING Count(*)>1 )) AND ((allmid_cpu.ito_managed)="Y"))
ORDER BY allmid_cpu.sys_name;



Thanks

 

[mbowler9]

Sorry to not get this in the first post. I just did some more research, and the sa_suitsid field does not have Nulls. It has either an id or "Unknown" in the table.

I am guessing that I need a reference to sa_suitsid under the "In" statement under sys_name.

 

[mbowler9]

Sorry again. I did figure it out. It happened to be my "ito" field set to "Y". Even though all of the selected systems should get by on this, some of the records were incorrect.

Thanks for looking.

 

[Golom]

Well done ...

Just in passing, Access documentation says

DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

So you don't need the DISTINCTROW in your SQL.